Date in Where clause

[jpillonel]

Hello,

I'have problem to write a sql query on my MSSQL Database. I have a field which contain date and time values in the unix format (seconds since 1/1/1970). I can convert the values without problem but I have a problem using GETDATE in the where clause.

I like to add a where clause to restrict data for only logs of today or for example tommorow. The filed is [log].time_stamp. I try this but the results show nothing and I'm sure that there is value, off course a problem of conversion...

SELECT call.ref_num, [log].description, loc.l_name, int_org.iorg_name, DATEADD(SECOND, [log].time_stamp, '01/01/1970') AS TIMELOG
FROM call_req call INNER JOIN
act_log [log] ON call.persid = [log].call_req_id INNER JOIN
ctct ON call.customer = ctct.id INNER JOIN
loc ON ctct.c_l_id = loc.id INNER JOIN
int_org ON ctct.c_org_id = int_org.id
WHERE (call.persid = 'cr:569514') AND ([log].type IN ('CB', 'LC', 'LO'))
AND DATEADD(SECOND, [log].time_stamp, '01/01/1970') = getdate()

Thanks for your help !

 

[rac2]

Good job converting that timestamp to a DATETIME value.

I just note that both the DATEADD() and getdate() functions return DATETIME values which include the time down to the millisecond. That seems like the reason the query returns no values with the equal criterion.

One way to do this is convert the DATETIME values to strings with just the date characters.

CONVERT(CHAR(10),DATEADD(SECOND, [log].time_stamp, '01/01/1970'), 120) 

= 

CONVERT(CHAR(10), getdate(), 120)

This gives a comparison between strings of the format
yyyy-mm-dd. See Books Online for other date formats.