Convert integer to datetime

Question

0.00/5 (No votes)

See more:

Hi all, I have a SQL Server table where dates are stored as integer values such as 1473861331 how do I convert them to a Date / DateTime ?

What I have tried:

SQL

declare @mydate integer

select @mydate = 1473861331

SELECT CAST(CAST(@mydate AS VARCHAR) AS DATETIME)

But I receive this error

Conversion failed when converting date and/or time from character string.

Posted 7-Oct-16 2:47am

pkfox

Updated 7-Oct-16 18:23pm

Add a Solution

Comments

Member 11137292 7-Oct-16 8:51am

What is the integer value? number of days? number of hours?

Member 11137292 7-Oct-16 8:54am

If its a number of time units you could add them to a base datetime, although the @mydate value causes an overflow.

declare @mydate integer

select @mydate = 1473861331

select DATEADD(hour,@mydate,'1 jan 1900' )

Afzaal Ahmad Zeeshan 7-Oct-16 8:56am

Why cannot you cast the integer to date time using a programming language, such as C# or C++?

Member 11137292 7-Oct-16 9:01am

good point, it all depends on what is consuming the data. OP makes no reference to programming language, just SQL.

pkfox 7-Oct-16 12:05pm

Sorry I should have been clearer, this needs to be done in a SQL Server stored procedure

Patrice T 7-Oct-16 14:51pm

How the date is supposed to be encoded in the integer ?

3 solutions

Solution 1

Moving onward from the comment I left on your question, basically SQL can let you convert data from one format to another, but for that you would need to learn how to do that: Every SQL language developer (Oracle, SQL Server, MySQL) have their own implementations for these functions. That is why, it is sometimes hard to help you out, anyways try the following codes, http://stackoverflow.com/questions/3855867/convert-int-to-datetime-sql[^].

Finally, since you tagged it with SQL Server (with a special comma, intentionally, perhaps?) see this MSDN guide for more on CAST and CONVERT functions of SQL: https://msdn.microsoft.com/en-us/library/ms187928.aspx[^]

Posted 7-Oct-16 3:06am

Afzaal Ahmad Zeeshan

Comments

pkfox 7-Oct-16 12:07pm

This needs to be done in a SQL Server stored procedure

Afzaal Ahmad Zeeshan 7-Oct-16 13:51pm

See the first link and the second one for more. I provided the links for code in SQL Server.

Solution 3

Take a look at these links:
http://stackoverflow.com/questions/3855867/convert-int-to-datetime-sql
http://www.sqlservercentral.com/Forums/Topic1547707-391-1.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70515

Posted 7-Oct-16 18:23pm

Daniel Jones

Updated 7-Oct-16 18:24pm

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

manu_dhobale · Accepted Answer · 2016-10-07T10:36:00

Solution 2

Assuming your datetime stored in Epoch & Unix Timestamp format, please ignore if this is not the case

declare @mydate integer
set @mydate = 1473861331
select DATEADD(hour,-5,(dateadd(second ,@mydate, '1/1/1970')))

--Result: 2016-09-14 08:55:31.000

Posted 7-Oct-16 10:36am

manu_dhobale

Updated 7-Oct-16 10:37am

v2

Comments

pkfox 8-Oct-16 3:47am

That's similar to what I've done now and it seems to be OK , why are you doing the -5 part though ?