How to convert Unixtime to GMTime in SQL Server

Hi guys i am trying to get last 30 days data but its unix datetime So,how do i convert and get the data for last days on a daily basis..

Thanks
danny1620Asked:
Who is Participating?
 
HwkrangerConnect With a Mentor Commented:
Or the other way around...

SELECT *
FROM <YOUR TABLE>
WHERE
 <YOUR TABLE COLUMN IN UNIX FORMAT> >= DATEDIFF(s, '19700101', Dateadd(dd, -30,GETDATE()))
0
 
ezraaConnect With a Mentor Commented:
MyTime = DATEADD(s, UnixTime, '1970-01-01 00:00:00')
0
 
HwkrangerCommented:
SELECT dateadd(s, ,<YOUR DATE TIME IN UNIX>, '19700101')
eg.
SELECT dateadd(s, 1113211532, '19700101')

returns; 2005-04-11 09:25:32.000
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
danny1620Author Commented:
Thanks Guys ...
I was using the below SQL to get the last 30 days data .. How can i use the same to get the last 30days data which has uNIX timestamp

SELECT ID Where Date >= DATEADD(D,DATEDIFF(DAY, 0 ,GETDATE())-30, 0)
and Date < DATEADD(D,DATEDIFF(DAY, 0 ,GETDATE()), 0)
0
 
danny1620Author Commented:
I am sorry its the otherway .. I need to check GMT date against Unix Date by converting GMT to UNIX..
0
 
ezraaCommented:
just reverse it:

DATEDIFF(s, '1970-01-01 00:00:00', UnixTime)
0
 
ezraaCommented:
correction:

UnixTime  = DATEDIFF(s, '1970-01-01 00:00:00',MyTime)
0
 
HwkrangerCommented:
SELECT
 *
FROM
 [YOUR TABLE]
WHERE
  Convert(datetime, dateadd(s, ,<YOUR DATE TIME COLUMN IN UNIX>, '19700101')) >= dateadd(dd, -30, getdate())
0
 
danny1620Author Commented:
Thanks guys
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.