question

sharon avatar image
sharon asked

Converting numeric to datetime

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 1 Conversion failed when converting datetime from character string.`
t-sqldatetimecast-convert
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks so much for all the wonderful replies. It works for the select part of the query but not on the where clause. For example, select stuff(stuff( case when len(DATE_RES_MADE) < 6 then '0' + convert(varchar(6) ,DATE_RESERVATION_MADE) else convert(varchar(6) ,DATE_RESERVATION_MADE) end, 3, 0, '/'), 6, 0, '/') from RES where DATE_RES_MADE > DATEADD(dd, DATEDIFF(dd, 0, GETDATE())-1, 0);
0 Likes 0 ·
@sharon This is simply because your original DATE_RES_MADE is still numeric(6,0), and if you need the predicate to work as date comparison, you can do something like this:
select
    records.DATE_RES_MADE from
    (
        select stuff(stuff(
            case 
                when len(DATE_RES_MADE) < 6 then '0' + 
                    convert(varchar(6) ,resdate)
                else convert(varchar(6) ,resdate)
            end, 3, 0, '/'), 6, 0, '/') as DATE_RES_MADE 
            from RES
    ) records
        where records.DATE_RES_MADE > 
            dateadd(dd, datediff(dd, 0, getdate()) -1, 0);
Otherwise, you will have to copy the select part into the where clause in place of your DATE_RES_MADE.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
You can use the American date standard (mm/dd/yy), by specifying style 1 on the convert function, after adding the appropriate '/' characters. declare @dates table (resdate numeric(6,0)) insert into @dates select 103110 union select 110110 select resdate, convert(datetime, substring(cast(resdate as varchar),1,2) + '/' + substring(cast(resdate as varchar),3,2) + '/' + substring(cast(resdate as varchar),5,2), 1) from @dates this gives resdate --------------------------------------- ----------------------- 103110 2010-10-31 00:00:00.000 110110 2010-11-01 00:00:00.000 (2 row(s) affected) ---- **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 declare @dates table (resdate numeric(6,0)) insert into @dates select 103110 union select 110110 union select 090110 select resdate, convert(datetime, substring(right('000000'+cast(resdate as varchar),6),1,2) + '/' + substring(right('000000'+cast(resdate as varchar),6),3,2) + '/' + substring(right('000000'+cast(resdate as varchar),6),5,2), 1) from @dates gives resdate --------------------------------------- ----------------------- 90110 2010-09-01 00:00:00.000 103110 2010-10-31 00:00:00.000 110110 2010-11-01 00:00:00.000 (3 row(s) affected)
7 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

the moral of the story is: if you are going to use a number to represent a date, such as an int key for a time dimension, always use YYYYMMDD. It is an ISO standard and you get the added benefit of natural ordering of dates
6 Likes 6 ·
I think I am late to the train, but it seems that the key piece of information is disregarded here. That key is that the data is stored as **decimal(6, 0)** or how it reads in question **numeric(6,0)**. This does mean that should the month part be earlier than October, the total number of characters would be 5 not 6, which will then break the statement in the answer:
Msg 242, Level 16, State 3, Line 7 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Because of it, Matt's way appears safer because it is resilient to the problem with 5 digits instead of 6. Additionally, if to modify it slightly to append '20' or '19' depending on the year to the left then his convert results in the YYYYMMDD, which is independent of any local settings and does not need a format parameter: select convert(datetime, '20' + convert(varchar, floor([resdate] / 100) + ((resdate % 100) * 10000))) from @dates; Unfortunately, the statement above is not resilient to the problem of the date where year part is less than 10, such as **103105** This means that the answer still requires further tweaking :)
5 Likes 5 ·
+1 - but I went for `CONVERT(datetime, CONVERT(varchar, FLOOR([resdate] / 100) + ((resdate % 100) * 10000)))`
0 Likes 0 ·
Yep there are sooooooooo many ways
0 Likes 0 ·
@Oleg - good spot... '20' + Right('000000' + ... , 6) ... or something similar?
0 Likes 0 ·
Show more comments
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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' In code: DECLARE @di int SET @di=103110 DECLARE @ds varchar(6) SET @ds=cast(@di as varchar(6)) DECLARE @dd datetime SET @dd='20' + SUBSTRING(@ds,5,2) + '-' + SUBSTRING(@ds,1,2) + '-' + SUBSTRING(@ds,3,2) + 'T00:00:00' I'm pretty sure that would work in any ISO sql dialect.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Magnus Ahlkvist Yes, it will work in any dialect. There is another ISO format independent of local settings, it is YYYYMMDD. It will not work, however, when the month part is earlier than October, because the original data is stored as numeric(6,0) meaning that it will only have 5 not 6 digits for those values.
1 Like 1 ·
Good point. Good fix too from Kev.
0 Likes 0 ·
Leo avatar image
Leo answered
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
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Leo Same problem, this does not work if the month is earlier than October. Try DECLARE @IntDate INT = 53110 to see. Kev updated his answer to cure the problem, check it out :)
0 Likes 0 ·
@Oleg - yes, that is right. Sorry I missed that. I hope that will work

DECLARE @IntDate INT = 103110
DECLARE @VarDate VARCHAR(10)
DECLARE @IntDate_Conv VARCHAR(6)

IF LEN(@IntDate) < 6
BEGIN
SELECT @IntDate_Conv = '0' + Convert(VARCHAR,@IntDate)
END
ELSE
BEGIN
SELECT @IntDate_Conv = Convert(VARCHAR,@IntDate)
END


SELECT @VarDate = STUFF(STUFF(@IntDate_Conv,3,0,'/'),6,0,'/')
SELECT CONVERT(datetime,@VarDate,1)
0 Likes 0 ·
@Leo It works. In case if you need to compact all the logic into a single statement (because the original question was asking about values in the table), you will have to opt for:
select stuff(stuff(
    case 
        when len(resdate) < 6 then '0' + 
            convert(varchar(6) ,resdate)
        else convert(varchar(6) ,resdate)
    end, 3, 0, '/'), 6, 0, '/') 
    from @dates;
Kev was exactly right when he said:
Yep there are sooooooooo many ways
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
Or ... DECLARE @n_date NUMERIC(6, 0) DECLARE @d_date DATE SET @n_date = 053109 SET @d_date = '20' + RIGHT(@n_date, 2) + '-' + REVERSE(SUBSTRING(REVERSE(@n_date), 5, 2)) + '-' + SUBSTRING(RIGHT(@n_date, 4), 1, 2) SELECT @d_date AS [Date type date] :)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.