Convert nvarchar to datetime

Question

Convert nvarchar to datetime

Dave Skrivanek

Newbie

Points: 4
More actions
March 19, 2008 at 9:15 am

#188326

I have a vendor table that contains dates in an nvarchar2 column in the format mm/dd/yyyy hh:mi:ss Z
(i.e. 03/18/2008 13:12:29 EDT). I would like to cast/convert this to a datetime so that I can use the date functions, or more specifically delete any data older than 3 months. I seem to be struggling wtih the cast syntax. Any help would be appreciated.
Dave

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Dave

If you use CONVERT instead of CAST, then you can take advantage of the date style options. These are explained in Books Online.

John

Dave Skrivanek Newbie Points: 4 More actions · Answer 2

Thanks John. I did see that in BOL, but I am tripping on the syntax. There is not a clear example for converting character data to a datetime, although there are plenty of examples of converting the other way around. I'm simply looking for an example that I can build my query from.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Dave

Is the time zone important in your calculations? If not, you could just knock it off the end. This worked for me:

select cast ('03/18/2008 13:12:29' as datetime)

John

Dave Skrivanek Newbie Points: 4 More actions · Answer 4

I don't think the time zone is important because we do not need to be that precise in deleting data older than months. I've tried both cast and convert (i.e. ,convert(datetime, [timeOccurred]) as timeOccured), but both return a "conversion failed" message. I also tried giving the date format for cast (mm/dd/yyyy hh:mi:ss Z), but that returned an error as well.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

I have a feeling you may need to split off the EDT from the rest, and somehow convert that (if need be) to a +/- hours from GMT. The format allowing for the timezone adjuster didn't even exist for 2000 AFAIK. Even so - when it does get introduced - it's expressed as a +/- adjustment in hours to GMT.

Once that's removed - then simply CAST(datevalue as datetime) will do the trick

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Dave Skrivanek Newbie Points: 4 More actions · Answer 6

Thanks Matt, that does seem to be the solution. This works:

,convert(datetime, substring([timeOccurred],1,19)) as timeOccured

I appreciate your help.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

If you prefer the convert (since it gives you more control over the format) - try:

select convert(datetime,'03/18/2008 13:12:29 ',101)

Looks to be the format you're wanting

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?