Selecting todays events 1

[lhg1]

Hi

I have a date format that is giving me trouble. I have to convert dates into numbers every time.

What I want is an sql that can give me tickets where TIME_WORK_START is today
TIME_WORK_START = '21-07-2009'

This is an eksample that shows all the tickets from now and 250 hours forward.

SELECT ticketid,description,Status,
CONVERT(VARCHAR, DATEADD(ss, TIME_WORK_START - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120),
CONVERT(VARCHAR, DATEADD(ss, TIME_PERM__SOLUTION - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)
from SC_Helpdesk
where ticketid in (SELECT ticketid from SC_Helpdesk where status <= 3 and ticket_type = 10 and (CONVERT(int, DATEDIFF(ss, '1970-01-01 00:00', GETDATE()), 120) - CONVERT(int,TIME_WORK_START,120)) between -900000 and 0)

Thanks
lhg

 

[PDreyer]

There is a problem in your where clause

This part is seconds
CONVERT(int, DATEDIFF(ss, '1970-01-01 00:00', GETDATE()), 120)

And this part is days
- CONVERT(int,TIME_WORK_START,120)


Also the way I see it, it can’t be negative unless TIME_WORK_START start in the future

FYI: The code will fail in 2038
e.g..
select 'OK'=dateadd(ss,2147483647,'19700101')
select 'FAIL'=dateadd(ss,2147483648,'19700101')

To get the data for the 21st try this

SELECT ticketid,description,Status,
CONVERT(VARCHAR, DATEADD(ss, TIME_WORK_START - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120),
CONVERT(VARCHAR, DATEADD(ss, TIME_PERM__SOLUTION - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)
from SC_Helpdesk
where status <= 3 
  and ticket_type = 10 
  and TIME_WORK_START>=datediff(dd,'19700101','20090721')
        *24*60*60+DATEDIFF(ss, GETDATE(), GETUTCDATE())
  and TIME_WORK_START<(datediff(dd,'19700101','20090721')+1)
        *24*60*60+DATEDIFF(ss, GETDATE(), GETUTCDATE())

 

[lhg1]

Thanks

TIME_WORK_START is in the future.

And your SQL is a lot cleaner.

/Lhg