T-SQL: Extract Substring

Question

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

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

@YuriyGalanter it is irrelevant, anyway i edited the question to avoid confucion. — E-Bat
– E-Bat, Commented Apr 11, 2014 at 21:30
Converting dates stored as text to a database date type has been widely explored. For example, stackoverflow.com/questions/18317650/…. — user565869
– user565869, Commented Apr 11, 2014 at 21:31
@JonofAllTrades, not the same thing, look to my examples, i have to dig in the string to get only the day. — E-Bat
– E-Bat, Commented Apr 11, 2014 at 21:34
Yep. So cast it to a date and use DAY(). If you really want to use SUBSTRING() for some reason, please specify. — user565869
– user565869, Commented Apr 11, 2014 at 21:36

jpw · Accepted Answer · 2014-04-11 22:10:01Z

2

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

edited Apr 11, 2014 at 22:10

answered Apr 11, 2014 at 21:44

jpw

45k6 gold badges71 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

David Griffiths Over a year ago

You can avoid the last cast: select day(string) as the_day from @t

jpw Over a year ago

@DavidGriffiths Good point, didn't think that day() operated on anything else than date[time] variables, but thanks to you I now know it can take string literals too. Nice to learn something new :)

E-Bat Over a year ago

I get: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

jpw Over a year ago

@Elio.Batista I'm guessing the date got interpreted as dmy. How the date is interpreted is affected by your language setting. You might have to force it by using set dateformat mdyfirst.

E-Bat Over a year ago

Yes, that is why i wanted to avoid casting to date types, just read the single value from day part.

|

Collectives™ on Stack Overflow

T-SQL: Extract Substring

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related