Converting numeric to datetime

Question

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.

Answer 1

0

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)

answered Nov 04 '10 at 05:22 PM

Kev Riley ♦♦
46k ● 38 ● 43 ● 69

+1 - but I went for CONVERT(datetime, CONVERT(varchar, FLOOR([resdate] / 100) + ((resdate % 100) * 10000)))

Nov 05 '10 at 01:59 AM Matt Whitfield ♦♦

Yep there are sooooooooo many ways

Nov 05 '10 at 02:00 AM Kev Riley ♦♦

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 :)

Nov 05 '10 at 08:51 AM Oleg

@Oleg - good spot... '20' + Right('000000' + ... , 6) ... or something similar?

Nov 05 '10 at 09:04 AM Matt Whitfield ♦♦

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

Nov 05 '10 at 06:37 PM Scot Hauder

(comments are locked)

Answer 2

0

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.

answered Nov 05 '10 at 06:05 AM

Magnus Ahlkvist
13.7k ● 13 ● 17 ● 30

@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.

Nov 05 '10 at 08:03 AM Oleg

Good point. Good fix too from Kev.

Nov 05 '10 at 03:07 PM Magnus Ahlkvist

(comments are locked)

Answer 3

0

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]

:)

answered Nov 08 '10 at 06:02 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

(comments are locked)

Answer 4

0

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

Leo
1.6k ● 48 ● 54 ● 58

@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 :)

Nov 05 '10 at 10:05 AM Oleg

@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)

Nov 08 '10 at 04:52 AM Leo

@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

Nov 08 '10 at 08:47 AM Oleg

(comments are locked)

Converting numeric to datetime

Follow this question

Related Questions