June 8, 2004 at 11:54 am
Hey I was wondering if and how it is possible to convert a date that is in the format mmddyy (varchar) into a datetime. When I try cast and convert they spit at me (syntax error converting date time from character string). isdate() doesn't like it(returns a 0), how can I format it so I can convert it?
mcb
June 8, 2004 at 12:31 pm
If your strings are always 6 chars and the same format you could use
declare @date varchar(10), @day char(2), @mnth char(2), @yr char(2)
set @date = '101004'
select @mnth = substring(@date, 1, 2)
select @day = substring(@date, 3, 2)
select @yr = substring(@date, 5, 2)
set @date = @yr + @mnth + @day
select cast(@date as datetime)
June 8, 2004 at 1:13 pm
Thanks, with a bit of fiddling I got it to work on the query. Strange how it wants the string in Year then Month then Day....that was what was holding me up. Thanks a million!
mcb
June 8, 2004 at 1:18 pm
If you look up CAST AND CONVERT in BOL they have a list of the possible date formats. The date format that you run defines the order of the entries when converting. I tried using set dateformat to do a direct CAST however it did not work (don't know why).
June 9, 2004 at 6:49 am
DECLARE @datestr char(6)
SET @datestr = '060904'
SET DATEFORMAT mdy
SELECT CAST(STUFF(STUFF(@datestr,5,0,'/'),3,0,'/') as datetime)
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply