asked on

Convert an int to a datetime in SQL Server2008

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.

ASKER CERTIFIED SOLUTION

sureshbabukrish

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kaminda

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

rajesh_chd

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

Open in new window

Alpesh Patel

http://blogs.msdn.com/b/manisblog/archive/2007/08/28/sql-server-2008-enhancements-in-date-and-time-data-types.aspx

Anthony Perkins

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)

davystocks

ASKER

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

davystocks

ASKER

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

davystocks

ASKER

Thanks for your help