?
Solved

Convert an int to a datetime in SQL Server2008

Posted on 2011-05-05
8
Medium Priority
?
357 Views
Last Modified: 2012-05-11
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
Comment
Question by:davystocks
8 Comments
 
LVL 9

Accepted Solution

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

by:kaminda
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

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

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35697449
0
 
LVL 75

Expert Comment

by:Anthony Perkins
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

by:davystocks
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

by:davystocks
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

by:davystocks
ID: 35699321
Thanks for your help
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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 …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question