Converting an integer to datetime

Question

Converting an integer to datetime

SQL_Enthusiast

Hall of Fame

Points: 3805
More actions
May 14, 2012 at 2:01 pm

#260825

I have a web application that stores datetime as integers. It's not a simple integer (20120514 as 5-14-2012), it 1014405513. Now, it took me two hours to figure out what they were doing and still not not 100% sure... but it seems like they are using the magic number 86400. That is number of seconds in a day. Here's an example of what they have done in their user manual...
@WHERE ( (CLOSEDATE - SUBMITDATE) < (86400*14) )
Any idea how to convert 1014405513 to a date time because I am lost...!
Table is TTT and column is SUBMITDATE

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 1

I believe it's based on the number of seconds since the 1980 epoch which means the following will make your life a whole lot easier if you take the time to convert the data...

SELECT DATEADD(ss,1014405513,'1980')

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dan-572483 SSCoach Points: 15870 More actions · Answer 2

I believe the datatime datatype was new with SQL 2005, so applications developed before that used various schemes for dates & times. While upgrade routines of recent versions of some applications convert their timestamps to actual datetime columns, some keep the old varchar or integer formats, which can be a real pain if you're designing custom Crystal Reports or using other 3rd party aps that can do cool things with datetimes.

My solution to this was to add a view that converts an integer date & time used by a particular application to varchar, then parses it to a string that can be correctly converted to a datetime. The view can then be joined to a table and used for queries & reporting.

Feel free to adapt this approch to the DB & datetime scheme you're working with.

CREATE view [dbo].[v_Issue_Date] as

select IS_ISSUE_NO,

CONVERT(datetime,LEFT(CAST(CAST(IS_Received_DATETIME as BigInt) as varchar(14)),8)+' '+

SUBSTRING(CAST(CAST(IS_RECEIVED_DATETIME as BigInt) as varchar(14)),9,2)+':'+

SUBSTRING(CAST(CAST(IS_RECEIVED_DATETIME as BigInt) as varchar(14)),11,2)+':'+

SUBSTRING(CAST(CAST(IS_RECEIVED_DATETIME as BigInt) as varchar(14)),13,2)) as 'Issue_Date'

from issues

where IS_RECEIVED_DATETIME > 0

SQL_Enthusiast Hall of Fame Points: 3805 More actions · Answer 3

Sorry, you answered the question exactly how I asked it. Shame on me for not being more thorough. SUBMITDATE has hundreds of records that are integer. SUBMITDATE is just one column in a larger SQL statement. Is there a way to use what you just submitted as part of a query that convert that integer within the larger SELECT statement?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

dan-572483 (5/14/2012)
I believe the datatime datatype was new with SQL 2005, so applications developed before that used various schemes for dates & times. While upgrade routines of recent versions of some applications convert their timestamps to actual datetime columns, some keep the old varchar or integer formats, which can be a real pain if you're designing custom Crystal Reports or using other 3rd party aps that can do cool things with datetimes.
My solution to this was to add a view that converts an integer date & time used by a particular application to varchar, then parses it to a string that can be correctly converted to a datetime. The view can then be joined to a table and used for queries & reporting.
Feel free to adapt this approch to the DB & datetime scheme you're working with.
CREATE view [dbo].[v_Issue_Date] as
select IS_ISSUE_NO,
CONVERT(datetime,LEFT(CAST(CAST(IS_Received_DATETIME as BigInt) as varchar(14)),8)+' '+
SUBSTRING(CAST(CAST(IS_RECEIVED_DATETIME as BigInt) as varchar(14)),9,2)+':'+
SUBSTRING(CAST(CAST(IS_RECEIVED_DATETIME as BigInt) as varchar(14)),11,2)+':'+
SUBSTRING(CAST(CAST(IS_RECEIVED_DATETIME as BigInt) as varchar(14)),13,2)) as 'Issue_Date'
from issues
where IS_RECEIVED_DATETIME > 0

Pretty sure that the DATETIME and SMALLDATETIME data types have been around since at least SQL Server 6.5 (I'll have to pull out my SQL Server 6.5 book at home tonight).

I know they existed in SQL Server 7.0, had problems porting dates from a Paradox database into SQL Server 7 as January 1, 2000 came around.

SQL_Enthusiast Hall of Fame Points: 3805 More actions · Answer 5

Never mind. My inability to spell has struck me yet again. I was able to add the DATEADD(...) to to query and it worked... Once I spelled SUBMITDATE right... Thank you

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 6

dan-572483 (5/14/2012)
I believe the datatime datatype was new with SQL 2005,...

The DATETIME datatype has been around for a lot longer than SQL Server has been. It started out in the original Rushmore Engine which is what both Sybase and SQL Server are based on.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 7

SQL_Enthusiast-AZ (5/14/2012)
Never mind. My inability to spell has struck me yet again. I was able to add the DATEADD(...) to to query and it worked... Once I spelled SUBMITDATE right... Thank you

Heh.... the devil's in the details. 🙂 Thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dan-572483 SSCoach Points: 15870 More actions · Answer 8

I stand corrected. Now why would a developer use anything other than a datetime to store a timestamp? It seems to lead to more complexity with no benefit.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 9

dan-572483 (5/14/2012)
I stand corrected. Now why would a developer use anything other than a datetime to store a timestamp? It seems to lead to more complexity with no benefit.

You're ok. The answer to your question is because some systems are based on Unix style dates which are based on seconds since the start of some epoch. It helps make life interesting. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Jeff Moden (5/14/2012)
dan-572483 (5/14/2012)
I stand corrected. Now why would a developer use anything other than a datetime to store a timestamp? It seems to lead to more complexity with no benefit.
You're ok. The answer to your question is because some systems are based on Unix style dates which are based on seconds since the start of some epoch. It helps make life interesting. 😀

Or they are storing dates as character strings because that allows them to format them as they want. Also, they may have been instructed to do so from "on high," and this directive cannot be ignored.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 11

Lynn Pettis (5/14/2012)
Jeff Moden (5/14/2012)
dan-572483 (5/14/2012)
I stand corrected. Now why would a developer use anything other than a datetime to store a timestamp? It seems to lead to more complexity with no benefit.
You're ok. The answer to your question is because some systems are based on Unix style dates which are based on seconds since the start of some epoch. It helps make life interesting. 😀
Or they are storing dates as character strings because that allows them to format them as they want. Also, they may have been instructed to do so from "on high," and this directive cannot be ignored.

I take great pride in ignoring directives from "on high" unless they were thoughful enough to include their directive in the spec and list of deliverables. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)