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 !

 

[jpillonel]

If I insert values manually in this format, it works fine:
2004-01-29 09:13:15.000

But when I like to use getdate, it doesn't works. I use convert to have exactly the datetime format without success:

where DATEADD(SECOND, [log].time_stamp, '01-01-1970 01:00:00.000') between
Convert(datetime,Convert(char(10),GetDate(),101))
and
Convert(datetime,Convert(char(10),GetDate(),101)) -1

I need some help ! Thanks !

 

[SQLBill]

Have you tested the

DATEADD(SECOND, [log].time_stamp, '01/01/1970')

to make sure it gives you what you expect?

Regardless of that, GETDATE() won't do what you want. You want all the records for today's date (ie 1 Sept 2004). GETDATE() returns the value of today'd date AND THE CURRENT TIME.

Try this:

SELECT (DATEADD(SECOND, [log].time_stamp, '01/01/1970')) AS 'Log Time',
   GETDATE() AS 'Current Date'

If you want values for all of one day and not just a specific second, you need to convert the GETDATE. Try this:

WHERE DATEADD(SECOND, [log].time_stamp, '01/01/1970') = (CONVERT(VARCHAR(10), GETDATE(), 121)

-SQLBill

 

[jpillonel]

Oupss...

No value are returned with this query:

WHERE DATEADD(SECOND, [log].time_stamp, '01/01/1970') = (CONVERT(VARCHAR(10), GETDATE(), 121)

If I try to use between:
DATEADD(SECOND, [log].time_stamp, '01/01/1970') = (CONVERT(VARCHAR(10), GETDATE(), 121)
and
WHERE DATEADD(SECOND, [log].time_stamp, '01/01/1970') = (CONVERT(VARCHAR(10), GETDATE(), 121) - 1

I receive this error message:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '2004-09-01' to a column of data type int.

I don't really understand where the problems comes. I think from getdate format and you ?


Thanks for your help !

 

[SQLBill]

Let's start with this. Run this and post what it returns.

SELECT DATEADD(SECOND, [log].time_stamp, '01/01/1970')
FROM act_log

If you have too many rows, you could try:

SELECT MAX(DATEADD(SECOND, [log].time_stamp, '01/01/1970'))
FROM act_log

-SQLBill

 

[jpillonel]

This is the result of the query with max parameter, I have some null value too:

2004-09-01 14:04:28.000


Thanks for your help !