Convert int date to proper date

[neemi]

Can someone help me with how I can convert dates which are stored as Integer values to proper date values...

An example of a date which is stored in a field is 1190286440 !!!

I have tried

convert(datetime, A.activeDate)

which I got from a web reference but it does not work!!!

Urgent help appreciated..

Regards,
Neemi

 

[bborissov]

And what is 1190286440? What represent that value?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[vongrunt]

This is probably UNIX timestamp. Try:

select dateadd(ss, 1190286440, '1970' )

------
chemistry - the only natural science that can be broken down into the categories a) making drugs and b) blowing stuff up

 

[gmmastros]

I'm gonna guess that 1190286440 represents the number of seconds since Jan 1, 1970. (Wild guess ).

If this is true, then you need to add the number of seconds and convert to date time, like this...

[tt][blue]Select DateAdd(Second, YourColumnName, '19700101') As YourDateColumn[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[neemi]

Thank you all for your response...

The date stored did end up being a Unix datestamp and using dateadd(ss, mycolumn,1970) did the trick!!

I have not used this way of storing dates before so was something new to me...

What are the benefits of storing the date in this was as abosed tp as a bit or just simply as a date?

 

[SQLDenis]

>>What are the benefits of storing the date in this

besides saving 4 bytes nothing!
where clause is no sargable if you are using datetimes you have to convert to unix date

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLDenis]

AFAIK & IIRC then UNIX dates are also in GMT timezone (it is the number of seconds elapsed since midnight UTC of January 1, 1970, not counting leap seconds
) you need to account for that

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

You can alter your UTC dates to make them display properly at all times in the client. But if this is impossible, then you have recourse:

select datediff(mi, getutcdate(), getdate()) -- your current difference in minutes from UTC time.
-- I'd use hours but some time zones are off by 1/2 an hour.

select UTCDateColumn + getdate() - getutcdate() from YourTableWithAUTCDateColumn
-- the UTC date column adjusted for the server's time.

 

[ESquared]

Actually, it's not so simple as that. Denis is completely right about UTC. We all gloss over the inherent problem by just using server time all the time, but anyone who has time-zone spanning clients is eventually going to run into problems with this.

The moment you try to solve it with UTC you're going to get additional problems showing up. The additional problems aren't problems with UTC at all: they're problems the system had from the start that an incomplete UTC solution highlights. UTC IS still the right solution, but a simple replacement of getdate() with getutcdate() is not enough.

If you are interested, here are some more thoughts on database time storage and display problems.