convert date within my SQL Statement 1

[chuckh70]

The date in the database is in Unix time format,
and I need to group by that field for the whole day. and
display the result in a readable date format.

I am not sure how to do this.

select create_time as 'Date Open',count(*) as 'tcktOpen' from HPD_HelpDesk where Assigned_To_Group_ = 'System Help Desk' and status < 4 and Create_time >= 1101859200 group by create_time

 

[mrdenny]

Getting just the date is fairly easy. The time is a pain in the *ss. Changes are in Red.

select [COLOR=red]dateadd(dd, (Create_Time/86400), '1/1/1970')[/color] as 'Date Open',
    count(*) as 'tcktOpen'
from HPD_HelpDesk 
where Assigned_To_Group_ = 'System Help Desk' 
    and status < 4 
    and [COLOR=red]dateadd(dd, (Create_Time/86400), '1/1/1970')[/color] >= [COLOR=red]'12/1/2004'[/color]
group by [COLOR=red]dateadd(dd, (Create_Time/86400), '1/1/1970')[/color]

Give that a shot. If Should do the trick for you.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[mrdenny]

I forgot to mention, I got my math from here.

http://www.rebol.net/cookbook/recipes/0051.html

It's in perl, but I was able to convert it to T/SQL for you.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[chuckh70]

Perfect Thanks.

That was just what I was looking for.. Also saved me all the trouble of doing it on the page itself.

 

[mrdenny]

no problem.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)