Search by todays date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Search by todays date

Author

Topic

andysam23586
Starting Member

4 Posts

Posted - 2009-09-02 : 13:53:41

Hi Folks

I have a table called CallsLogged with a column called EnteredOn.
The data in this column is a date in unix Time Stamp format.
I need to write a T-SQL query to retrieve all entries where the value of "EnteredOn" is Today.

At the minute the query looks like this:


SELECT *
FROM CallsLogged
WHERE EnteredOn BETWEEN 1251849600 AND 1251935999

The values in the WHERE clause are 00:00:00 to 23:59:59 today.

This works fine but I have to change the values in the WHERE clause every day. Is there a way of changing the query so that I don't have to change the values and use something like GETDATE().

I don't have any control over the table as it belongs to a database stored on a server that is looked after by a third party.

I also need to query all entries from last week and last month as well but I am currently changing the values all the time.

I haven't worked with Unix time stamp before.
I hope someone can help.

Thankyou in advance

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-02 : 14:08:45

The Unix Time Stamp is the number of seconds since Jan 1, 1970. So you just need to do a little date math to get the Time Stamp(s) you are looking for. Here is one way written two different ways:

SELECT *
FROM CallsLogged
WHERE 
	EnteredOn BETWEEN DATEDIFF(SECOND, '19700101', DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0))
			AND DATEDIFF(SECOND, '19700101', DATEADD(DAY, DATEDIFF(DAY, -1, CURRENT_TIMESTAMP), 0)) - 1

-- OR

SELECT *
FROM CallsLogged
WHERE 
	EnteredOn >= DATEDIFF(SECOND, '19700101', DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0))
	AND EnteredOn < DATEDIFF(SECOND, '19700101', DATEADD(DAY, DATEDIFF(DAY, -1, CURRENT_TIMESTAMP), 0))

andysam23586
Starting Member

4 Posts

Posted - 2009-09-02 : 15:13:39

Thankyou very much Lamprey
This works a treat.

If I want to change the query to retrieve all entries where the value of "EnteredOn" is within the last week do I just change the "-1" to "-7" or do I need to change something else?

I tried this:


SELECT *
FROM CallsLogged
WHERE 
	EnteredOn BETWEEN DATEDIFF(SECOND, '19700101', DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0))
			AND DATEDIFF(SECOND, '19700101', DATEADD(DAY, DATEDIFF(DAY, -7, CURRENT_TIMESTAMP), 0)) - 7

But got the same results as the first query.
I also tried to retrieve entries where the value of "EnteredOn" is within the last month by changing the query to:


SELECT *
FROM CallsLogged
WHERE 
	EnteredOn BETWEEN DATEDIFF(SECOND, '19700101', DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0))
			AND DATEDIFF(SECOND, '19700101', DATEADD(DAY, DATEDIFF(DAY, -31, CURRENT_TIMESTAMP), 0)) - 31

but stil got the same results.

I must be missing something very simple but can't see it.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-02 : 15:30:45

It is strange, but the -1 is for tomorrow. So you need to change the 0 to 6 or 7 or you could chnage the DATEDIFF to use WEEKs instead of DAYs depending on what the exact range is that you want.

andysam23586
Starting Member

4 Posts

Posted - 2009-09-02 : 17:21:42

Thanks once again Lamprey

I have had a play around with it and would appreciate it if you would check my code for the following. I have tested it and it seems to work ok.

All calls for this week:


SELECT *
FROM CallsLogged
WHERE EnteredOn BETWEEN DATEDIFF(second, '19700101', DATEADD(week, DATEDIFF(week, 0, CURRENT_TIMESTAMP), 0)) AND 
DATEDIFF(second, '19700101', DATEADD(week, DATEDIFF(week, 0, CURRENT_TIMESTAMP) +1, 0)) -1

All calls for this month:


SELECT *
FROM CallsLogged
WHERE EnteredOn BETWEEN DATEDIFF(second, '19700101', DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)) AND 
DATEDIFF(second, '19700101', DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP) +1, 0)) -1

All calls for last month:


SELECT *
FROM CallsLogged
WHERE EnteredOn BETWEEN DATEDIFF(second, '19700101', DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP)-1, 0)) AND 
DATEDIFF(second, '19700101', DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP) , 0)) -1

All calls for last 6 months not including this month:


SELECT *
FROM CallsLogged
WHERE EnteredOn BETWEEN DATEDIFF(second, '19700101', DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP)-6, 0)) AND 
DATEDIFF(second, '19700101', DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP) , 0)) -1

All I need to change now for previous months is the "-6" to how ever many months I want to go back

Hope this looks ok

andysam23586
Starting Member

4 Posts

Posted - 2009-09-03 : 07:55:07

I have thoroughly tested these query's and they work fine.
Is there anyone who knows what the equivalent query would be to query an Oracle database.

I am getting an error saying DATEDIFF: invalid identifier

Can anyone help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources