Solved

# Convert an int to a datetime in SQL Server2008

Posted on 2011-05-05
Medium Priority
357 Views
Hi,
I've a table in a sqlserver 2008 database, one of the columns in the  table is an int which stores values as 60110  which I'm trying to convert to an datetime of 2010-06-10 00:00:00.000
or 120211 which I'm trying to convert to an datetime of 2011-02-12 00:00:00.000

I've

SELECT column1, convert(datetime,CONVERT(int,column1))
from table

but for 60105 the above converts it to 2064-07-24 00:00:00.000 rather than
2005-01-06  00:00:00.000

Any help would be much appreciated.

0
Question by:davystocks

LVL 9

Accepted Solution

sureshbabukrish earned 2000 total points
ID: 35697309
use this below
replace @dv with your column name which is of INT datatype

select convert(datetime,'20' + right(convert(varchar(6),@dv),2) + '-' + (left(right(convert(varchar(6),@dv),4),2))+ '-' + right( '0' + substring(convert(varchar(6),@dv),1, len(convert(varchar(6),@dv))-4),2))
0

LVL 9

Expert Comment

ID: 35697322
You can use this

SELECT
CASE LEN(column1) WHEN 6 THEN
convert(datetime,left(column1,2) + '/' + substring(cast(column1 as varchar(6)),3,2) + '/' + cast (right(column1,2) as varchar(2)))
ELSE
convert(datetime,left(column1,1) + '/' + substring(cast(column1 as varchar(5)),3,2) + '/' + cast (right(column1,2) as varchar(2)))
FROM Table1
0

LVL 2

Expert Comment

ID: 35697395
As far as I know, julian number stores dates in YYDDD format i.e YY = year and DDD = number of day in that year.  For example 60015 would be 15-jan-1960 and 20015 would be 15-jan-2020.

Here is a code example to convert julian number in a date

``````declare @julian int
set @julian = 60215
SELECT DATEADD(day, CAST(RIGHT(@julian,3) AS int) - 1, CONVERT(datetime, LEFT(@julian,2) + '0101', 112))
``````
0

LVL 21

Expert Comment

ID: 35697449
0

LVL 75

Expert Comment

ID: 35697905
For date formats MMDDYY it is as simple as this:
CONVERT(datetime, STUFF(STUFF(RIGHT('0' + CAST(@YourColumn as varchar(6)), 6), 3, 0, '-'), 6, 0, '-'), 10)
0

LVL 4

Author Comment

ID: 35698354
Hi sureshbabukrish:,

When I use your code I'm getting

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Hi Kaminda,

I'm currently getting
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'FROM'.

when I use  SELECT
CASE LEN(column1) WHEN 6 THEN
convert(datetime,left(column1,2) + '/' + substring(cast(column1 as varchar(6)),3,2) + '/' + cast (right(column1,2) as varchar(2)))
ELSE
convert(datetime,left(column1,1) + '/' + substring(cast(column1 as varchar(5)),3,2) + '/' + cast (right(column1,2) as varchar(2)))
FROM Table1
0

LVL 4

Author Comment

ID: 35699199
select convert(datetime,'20' + right(convert(varchar(6),@dv),2) + '-' + (left(right(convert(varchar(6),@dv),4),2))+ '-' + right( '0' + substring(convert(varchar(6),@dv),1, len(convert(varchar(6),@dv))-4),2))

120103 returns a value of 2003-12-01 00:00:00.000

which  should be 2003-01-12 00:00:00.000

and

130111  returns

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

So it looks like I need to switch dd with mm

0

LVL 4

Author Closing Comment

ID: 35699321
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down ā¦