convert numeric to datetime in SQL Server

[lovetodance]

I need to convert dates in numeric format to datetime
Example:

Numeric Datetime
50423 04/23/2005
1126 11/26/2000
218 02/18/2000
30108 01/08/2003

Thanks in advance

 

[rac2]

By "numeric" do you mean that 30108 is the number thirty-thousand-one-hundred-eightteen stored in a database with datatype INT or similar?

Or do you mean it is a string of 5 characters, three, zero, one,zero, and eight stored in a database with datatype VARCHAR or similar?

 

[lovetodance]

it's a number

 

[rac2]

Do any of the numbers represent dates prior to the year 2000?

 

[lovetodance]

yes,
year 1999 like 990112 01/12/1999
year 2000 like 314 03/14/2000

 

[SkipVought]

Hi,

TIP...

they may be stored as numbers (yuk) but in reality they ought to be handled as CHARACTERS.

1. Pad with leading zeros to 6 characters
[tt]
050423
001126
000218
030108
990112
000314
[/tt]
2. If the first two characters are less than, what, "20" then prefix with "20", otherwise prefix with "19". Now you have an EIGHT character string.
[tt]
20050423
20001126
20000218
20030108
19990112
20000314
[/tt]
3. Convert the string to a date, parsing
1 to 4 into YEAR,
5 to 6 into MONTH,
7 to 8 into DAY.

Skip,

_[red][/red]

 

[rac2]

Here are some essential pieces using numbers stored in variables. If the numbers are in a database table you would use the column name where I used variable names.

DECLARE @n INT
SET @n = 218

DECLARE @m INT
SET @m = 1218

DECLARE @k INT
SET @k = 990112

SELECT @n, 
       CAST(@n AS VARCHAR), 
       '2000' + '0' + CAST(@n AS VARCHAR), 
       CONVERT(DATETIME, '2000' + '0' + CAST(@n AS VARCHAR) )

SELECT @m, 
       CAST(@m AS VARCHAR), 
       '2000'       + CAST(@m AS VARCHAR), 
       CONVERT(DATETIME, '2000'       + CAST(@m AS VARCHAR) )

SELECT @k, 
       CAST(@k AS VARCHAR), 
       '19'         + CAST(@k AS VARCHAR), 
       CONVERT(DATETIME, '19'         + CAST(@k AS VARCHAR) )

I showed all of the building blocks in each SELECT just to show what is going on; the last expression using the CONVERT function is the one you would actually use to convert the number to a DATETIME datatype.

If this is a one-time-only conversion I would create a new column for the DATETIME values and run several UPDATE statements for the different conversion expressions. So the year 2000, January through September, would be numbers BETWEEN 101 and 930 and would use the first expression.

If this is an ongoing process with new crumby data being added continuously and every query must do the conversion then you would write a CASE expression with all of the various possible expressions and ranges to which they apply.

Anyway that is what I would do. Maybe one of the others knows a magic formula for this.

 

[lovetodance]

Hi all, I appreciated your quick respond and help, I was thinking in the same direction, but thought may be exists other short way to do it, thanks again