I am trying to run a script to get customer's arrival, departure, date reservation made from a table but these column fields have numeric(6,0) datatype. It is MMDDYY. Here is 2 sample of these fields value:- 103110, 110110 And, I can't get it to convert to datetime. Any ideas please. I keep getting this error:-
`Msg 241, Level 16, State 1, Line 1Conversion failed when converting datetime from character string.`
You can use the American date standard (mm/dd/yy), by specifying style 1 on the convert function, after adding the appropriate '/' characters.
---- Edit : Oleg makes a good point about the date only being 5 chars if the month is less than 10, so here is a revised solution
Using ISO 8601 format should make it work regardless of regional settings, and on any DBMS that speaks ISO SQL.
Format is YYYY-MM-DDThh:mm:ss[.nnn] In your case '2010-10-31T00:00:00'
I'm pretty sure that would work in any ISO sql dialect.
answered Nov 05 '10 at 06:05 AM
answered Nov 08 '10 at 06:02 AM
Please try this -
DECLARE @IntDate INT = 103110
DECLARE @VarDate VARCHAR(10)
SELECT @VarDate = CONVERT(varchar, STUFF(STUFF(@IntDate,3,0,'/'),6,0,'/'))
SELECT CONVERT(datetime,@VarDate,1) --US Format
answered Nov 05 '10 at 09:47 AM