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.`
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);
@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.
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)
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
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 :)
+1 - but I went for `CONVERT(datetime, CONVERT(varchar, FLOOR([resdate] / 100) + ((resdate % 100) * 10000)))`
@Oleg - good spot... '20' + Right('000000' + ... , 6) ... or something similar?
@Oleg : thats what you get for doing these things at midnight!! @Matt : aha - same thoughts!
@Matt Whitfield Your way to flip the last 2 characters to the front is very good, but just does not do it for year < 10, so it needs a little tweaking. I have a hard time for some reason to come up with something cheap enough. The best I have so far is
select
convert(datetime,
case
when right(cast(resdate as varchar), 2) > '49' then '19'
else '20'
end +
right(cast(resdate as varchar), 2) +
left(replace(str(cast(resdate as varchar), 6), ' ', '0'), 4))
from @dates; It handles both problems (5 digits which breaks original answer and year less than 10 which breaks yours). It does not look cheap though, and so I don't like it though it works.
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.
@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.
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
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
@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)
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)
@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
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] :)
