CODEWITHSUNDEEP
t-sql
E-Bat
E-Bat

Reputation: 4892

T-SQL: Extract Substring

I have to read dates from a varchar column with the following possible patterns ( month/day/year ):

1/1/2005 
1/13/2005 
10/9/2005 
10/13/2005

What is the correct way to read the day part of those dates with T-SQL?

Upvotes: 0

Views: 179

Answers (1)

jpw
jpw

Reputation: 44921

Casting to date and using day() would be the correct approach imo:

declare @t table (string varchar(10))
insert @t values ('1/1/2005'),('1/13/2005'),('10/9/2005'),('10/13/2005')
select day(cast(string as date)) as the_day from @t

Would give:

the_day
-----------
1
13
9
13

An alternative if you want to avoid casting would be to use thesubstringandcharindexfunctions:

select 
    substring(
      string, 
      charindex('/', string)+1, 
      charindex('/', string, charindex('/', string)+1)-charindex('/', string)-1
    ) 
from @t

Upvotes: 2

Related Questions