Converting Integer to Date

Question

Post reply

Converting Integer to Date

Nelson Viggiani-332216

Ten Centuries

Points: 1264
More actions
June 23, 2006 at 7:57 am

#94469

I have integer Fields with this data = 1137699938
that is a date integer, how do I transform it in date yymmdd ??
thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

Normally this would work......

SELECT CONVERT(DATETIME, 1137699938)

But it's not working for your value. How do you 'manually' convert that to a date? And is it coming from some other application? SQL Server uses Jan 2 1900 as it's 'start date', other apps use other start dates.

-SQLBill

Nelson Viggiani-332216 Ten Centuries Points: 1264 More actions · Answer 2

it's not working, it come from other application and the data type is int 4

Sassan Karai SSC Veteran Points: 290 More actions · Answer 3

Your most likely looking at seconds expired since '01-01-1970' in case you want to 'translate' into datetime you can write it like this

SELECT

DATEADD(ss, (A.[yourtime]- 14400), '01-01-1970') AS [UTC-4] -- [EasternDT]

, CONVERT (CHAR (3),(DATENAME(dw,(SELECT DATEADD(ss, (A.[yourtime]- 14400), '01-01-1970'))))) AS [Day]

You can take out the -14400 seconds (4h) I had to subtract since time was recorded in UTC and I wanted to display Eastern time.

See also:

http://groups.msn.com/Atlantamdf/general.msnw?action=get_message&mview=0&ID_Message=1155&LastModified=4675575952801013832

Nelson Viggiani-332216 Ten Centuries Points: 1264 More actions · Answer 4

not sure what you're saying...

Let me try to explain it again.

I have a field called dat_Submitted_on and that field is Integer 4 and the data in there is 1137699938 represent the data that ticket was open, so I need build a report but with the Open Date, in another words I need to translate that "1137699938" in yymmdd.

any ideas??

thanks

RyanRandall SSChampion Points: 13623 More actions · Answer 5

skarai has got it, I reckon. To explain more simply...

declare @i int

set @i = 1137699938

SELECT DATEADD(ss, @i, '01-01-1970') as 'My date'

/*results

My date

------------------------------------------------------

2006-01-19 19:45:38.000

*/

Make sense?

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall SSChampion Points: 13623 More actions · Answer 6

...obviously to get this date to yymmdd format, you just need to use convert...

declare @i int

set @i = 1137699938

SELECT convert(varchar, DATEADD(ss, @i, '01-01-1970'), 112) as 'My date'

/*

My date

------------------------------------------------------

20060119

*/

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 7

Just riding Ryan's solution, here.

To get yymmdd use 12 in the convert function, and 112 to get yyyymmdd:

SELECT convert(varchar, DATEADD(ss, @i, '01-01-1970'), 112) as 'My date yyyy'

/*

My date yyyy

------------------------------------------------------

20060119

*/

SELECT convert(varchar, DATEADD(ss, @i, '01-01-1970'), 12) as 'My date yy'

/*

My date yy

------------------------------------------------------

060119

*/

Eddie Wuerch
MCM: SQL

Nelson Viggiani-332216 Ten Centuries Points: 1264 More actions · Answer 8

THANK YOU THANK YOU THANK YOU... that works...appreciated...thanks again

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 9

???

SQL Server has a start date of January 1, 1753 and end date of December 31, 9999 when using DATETIME. SMALLDATETIME has start date of January 1, 1900 and end date of June 6, 2079.

What I think you are referring to is "Date Zero", which incidentally is January 1, 1900 in SQL Server. MS Access has December 30, 1899 as "Date Zero".

N 56°04'39.16"
E 12°55'05.25"