Converting/Casting strings into Datetime datatype

Hello,

I have a varchar column that inludes dates in the following fomat: 03032007? When I try to cast this to datetime, I keep getting "Arithmetic overflow error converting expression to data type datetime." error. Maybe someone has some ideas how to handle this?

Thanks!

If you had only stored your date values in the ISO format of YYYYMMDD, they would easily cast or convert to datetime. -Or even left in one of the standard date delimiters, such as [ / - ].

However, you (or some unnamed 'other' person) made up a oddball format, and now you will have to 'handle' it to create a 'real' date value everytime you need to use it.

(This assumes your format is MMDDYYYY.)

SELECT cast( stuff( stuff( '03032007', 3, 0, '/' ), 6, 0, '/' ) AS datetime )

-
2007-03-03 00:00:00.000

|||

You know that MS stores sqlagent datetime in to two int columns with the following format, right? (Take a look at the schema for msdb: sysalerts,sysjobhistory, sysjobschedules, sysjobservers, and sysjobsteps.)

date: YYYYMMDD

time: HHMMSS

So, it's not that weird to see the public employs such schema.

|||

But I also notice that MS stores SQL Agent datetime in ISO format (YYYYMMDD).

That little 'standard' makes a lot of difference in cast/convert.

That is behind my even mentioning using a standard ISO format in my response...

Converting strings to dates

Hi,

I have a varchar column with mixed date formats, some are dd-mm-yyyy and others are mmm dd yyyy hh:mmAP, these latter having been generated automatically by SQL when I changed the column datatype from datetime to varchar.

I would like to run an update script to pattern match the latter and change to the former.

I've been trying something along the lines of

update dwsubmit set authoreddate = (select day(authoreddate))+'-'+(select month(authoreddate))+'-'+(select year(authoreddate)) where id = 841

to try and get the format conversion correct, but this is obviously not correct. I have tried a few combinations, but have had no joy. Any ideas?

Thanks, Matt.I understand ur pain man...but u r doin it wrongly. Try this:

update dwsubmit
set authoreddate = (select datepart(authoreddate,dd))+'-'+(select datepart(authoreddate,mm))+'-'+(select datepart(authoreddate,yy))
where id = 841

op it works for u...av fun!|||sorry i mixed it up

update dwsubmit
set authoreddate = (select datepart(dd,authoreddate))+'-'+(select datepart(mm,authoreddate))+'-'+(select datepart(yy,authoreddate))
where id = 841

it should be ds way...sorry about dat...|||Thanks for that - I tried something similar, but SQL is actually adding the values up to come up with 2022 (04 + 12 + 2006). I have no idea what it is doing with the '-' characters. I then tried casting each part to char, but it ignores that too!|||i op u tried d 2nd version not d first...ol d same

try ds...

update dwsubmit
set authoreddate = select datepart(dd,authoreddate) + '-' + select datepart(mm,authoreddate) + '-' + select datepart(yy,authoreddate)
where id = 841|||Using what you gave me, I got to the following:

update dwsubmit
set authoreddate = cast((select datepart(dd,authoreddate)) as char(2))+'-'+cast((select datepart(mm,authoreddate)) as char(2))+'-'+cast((select datepart(yy,authoreddate)) as char(4))
where id = 841

Which gives me what I want unless there is only a single digit for the month or day, in which case I get something like 4 -12-2006 which should actually be 04-12-2006.

I'm sure the convert function is a better way of doing this, but I can't figure it out!

Thanks,
Matt.