x

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.`
more ▼

asked Nov 04, 2010 at 04:58 PM in Default

sharon gravatar image

sharon
151 12 13 15

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);
Nov 08, 2010 at 03:30 PM sharon

@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.
Nov 08, 2010 at 04:51 PM Oleg
(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

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)
more ▼

answered Nov 04, 2010 at 05:22 PM

Kev Riley gravatar image

Kev Riley ♦♦
53.8k 47 49 76

+1 - but I went for CONVERT(datetime, CONVERT(varchar, FLOOR([resdate] / 100) + ((resdate % 100) * 10000)))
Nov 05, 2010 at 01:59 AM Matt Whitfield ♦♦
Yep there are sooooooooo many ways
Nov 05, 2010 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, 2010 at 08:51 AM Oleg
@Oleg - good spot... '20' + Right('000000' + ... , 6) ... or something similar?
Nov 05, 2010 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, 2010 at 06:37 PM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Nov 05, 2010 at 06:05 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

@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, 2010 at 08:03 AM Oleg
Good point. Good fix too from Kev.
Nov 05, 2010 at 03:07 PM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left

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]
:)
more ▼

answered Nov 08, 2010 at 06:02 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

(comments are locked)
10|1200 characters needed characters left
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
more ▼

answered Nov 05, 2010 at 09:47 AM

Leo gravatar image

Leo
1.6k 54 56 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, 2010 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, 2010 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, 2010 at 08:47 AM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x990
x95
x29

asked: Nov 04, 2010 at 04:58 PM

Seen: 4002 times

Last Updated: Nov 04, 2010 at 05:23 PM