Return all data from last month

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 2000 Forums

SQL Server Administration (2000)

Return all data from last month

Author

Topic

The1Ash10
Starting Member

15 Posts

Posted - 2009-04-01 : 10:30:44

I trying to query all data that is older than the current month, but something is wrong with the query I'm using...

select *
from Table
WHERE dateadd(ss,time_stamp,'19700101') < DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)

The1Ash10
Starting Member

15 Posts

Posted - 2009-04-01 : 10:47:53

Edit: I still cant get it to work...

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-01 : 11:24:49

this part doesn't look good. "dateadd(ss,time_stamp,'19700101')".
what is the data type of time_stamp ?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-01 : 11:27:21

Hopefully its datetime.
I think this should be good.

select *
from Table
WHERE time_stamp < DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)

The1Ash10
Starting Member

15 Posts

Posted - 2009-04-01 : 11:36:20

quote:
Originally posted by sakets_2000

this part doesn't look good. "dateadd(ss,time_stamp,'19700101')".
what is the data type of time_stamp ?

the time stamp is in unix time, therefore it is used to convert to something sql can read.

The1Ash10
Starting Member

15 Posts

Posted - 2009-04-01 : 11:40:05

quote:
Originally posted by sakets_2000

Hopefully its datetime.
I think this should be good.

select *
from Table
WHERE time_stamp < DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)

that is what I'm using and it doesn't seem to work...

It seems like that is a more simplistic way of doing this, but I'm just not sure what it would be.... I just want it to return all data from the previous month.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-01 : 11:40:41

you can use the function here to convert from unix-time
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66858

The1Ash10
Starting Member

15 Posts

Posted - 2009-04-01 : 11:43:52

quote:
Originally posted by sakets_2000

you can use the function here to convert from unix-time
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66858

This dateadd(ss,time_stamp,'19700101') already does the job.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-01 : 11:49:36

whats the problem then ? What you have looks ok to me.

The1Ash10
Starting Member

15 Posts

Posted - 2009-04-01 : 11:52:47

quote:
Originally posted by sakets_2000

whats the problem then ? What you have looks ok to me.

I have a table that has data in it from last month (March) and this month. The problem I'm having with the query that I have is that it is not returning anything from last month.

I just need the query to return all data from the previous month (whatever month that might be) and nothing from the current month. Thanks again for all of your help.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-01 : 12:35:21

Can you post a few sample data from the table which you expect to be returned ?

The1Ash10
Starting Member

15 Posts

Posted - 2009-04-01 : 14:51:50

got it
WHERE dateadd(ss,time_stamp,'19700101') <= DATEADD(month, DATEDIFF(month, 0, GETDATE()),0);

It was set to pull data older than last month (Feb/Jan). This does the trick.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources