Convert INT to DATETIME 1

[octavian10]

Hello,
I need help converting a column that is set up as an integer to date time.
My current query - SELECT dateTimeConnect FROM dbo.CallDetailRecord - returns 1284149324.
I need to convert this number into datetime for some custom reporting. This conversion needs to be part of this query as the results get emaild. Thanks a bunch in advance.


"I hear and I forget. I see and I remember. I do and I understand."
- Confucius (551 BC - 479)

 

[gmmastros]

[!]1284149324[/!]

This looks like a 'unix' time, which is basically the number of seconds that have elapsed since Jan 1, 1970. A simple 'DateAdd' should be all you need here.

SELECT [!]DateAdd(Second, [/!]dateTimeConnect[!], '19700101') As DateTimeConnect[/!]
FROM   dbo.CallDetailRecord

-George

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

 

[gmmastros]

If you want to get ONLY the date part, you should do some integer math on it first.

There are 86,400 seconds in a day. If you divide your value by this number, and you truly have an integer, you will get integer math and the fractional part will be removed for you. So...

Declare @Test Int
Set @Test = 1284149324

Select DateAdd(Day, @Test / 86400, '19700101') As DateOnly
Select DateAdd(Second, @Test, '19700101') As DateAndTime

-George

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

 

[octavian10]

It is a UNIX second number and your reply is exactly what I was looking for. Thanks a bunch again!

"I hear and I forget. I see and I remember. I do and I understand."
- Confucius (551 BC - 479)

 

[octavian10]

A little more help with this please. I need to use the above SELECT statement on a table but only for the current date. I am not sure how to accomplish this with this conversion. In other words the above SELECT statement needs to have a WHERE clause that asks only for the current date. Not the time just the date. Thanks again in advance.

"I hear and I forget. I see and I remember. I do and I understand."
- Confucius (551 BC - 479)

 

[djj55]

George may correct me but use something like

WHERE DateAdd(Day, @Test / 86400, '19700101') = CAST(GETDATE() AS DATE)

which I have not tried but should give you the idea.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

Sounds like you want to get all rows where the dateTimeConnect represents today. Any time today.

To convert Today's date and time to unix time:

Select DateDiff(second, '19700101', GetDate())

To get the start of today we can divide by 86400 and then multiply by 86400. Ordinarily you might think this is wrong, but, since DateDiff returns an integer, and you hard code a couple integers, we will get integer math. So...

Select DateDiff(second, '19700101', GetDate()) / 86400 * 86400

Putting this all together...

SELECT * -- Column List here
FROM   dbo.CallDetailRecord
Where  dateTimeConnect >= DateDiff(second, '19700101', GetDate()) / 86400 * 86400
       and dateTimeConnect < DateDiff(second, '19700101', GetDate()) / 86400 * 86400 + 86400

Now.... I know what you are thinking. You're probably thinking, "That's a lot of code, and it's kinda ugly". I agree with you. Really, I do. But, this query will return the rows you are looking for AND it's sargable. Since all the values for each where clause condition does not do "math" on the column data, SQL Server will be able to use an index seek (if an appropriate index exists) to speed up this query. If you don't know what sargable means, you should spend a few minutes learning about it now. [google]SQL Server Sargable[/google]


-George

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

 

[djj55]

Told you George would correct me.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[octavian10]

One last question I swear! This works great but it returns a UTC/GMT time. I need to convert this to EST time. Cant seem to do it with out messing up the query. Thank you

"I hear and I forget. I see and I remember. I do and I understand."
- Confucius (551 BC - 479)

 

[gmmastros]

What have you tried?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[octavian10]

I have tried this:
select (datetimeorigination - DATEDIFF(s,0,getutcdate() - getdate()))
Which does the EST change in seconds but I cant get it work with conversion you showed me. I have also tried other variations of the above but cant seem to get any of them to work. It seems that some how I need to subtract the number of seconds from UTC/GMT to reflect EST. Thanks

"I hear and I forget. I see and I remember. I do and I understand."
- Confucius (551 BC - 479)

 

[Goondu]

octavain10,

Here one web site.

http://www.sqlservercurry.com/2010/02/convert-from-est-to-gmt-time-and-vice.html

A user asked me if there were any functions to convert EST to GMT time in SQL Server. Well I do not think there is an existing function, but using the DATEADD() function, we should be able to achieve this easily. The difference between EST and GMT is -5 hours, so 4.00pm EST is will be 9.00pm GMT

The query would be like this:

DECLARE @ESTTime as DateTimeSET @ESTTime = '2010-02-13 11:30:55.293'
SELECT DATEADD(HOUR, 5, @ESTTime) as GMTTime
DECLARE @GMTTime as DateTimeSET @GMTTime = '2010-02-13 16:30:55.293'
SELECT DATEADD(HOUR, -5, @GMTTime) as ESTTime