SQL syntax
-
Hi,
A very easy question for someone who has the time to respond....
I want to get the last months' data from an SQL Server table. The DB2 equivalent would be:
select * from table
where date(timestamp_column) = current date - 1 month;
thank you...
-
I did this by using udf's to work out the start and end date of the month.
The link below explains how you can do this
http://www.sql-server-helper.com/functions/get-last-day-of-month.aspx
Hope this helps.
-
select * from table
where timestamp_column >= dateadd(mm, datediff(mm, 0, GETDATE()) -1, 0) + DAY(GETDATE()) - 1
AND timestamp_column < dateadd(mm, datediff(mm, 0, GETDATE()) -1, 0) +DAY(GETDATE())
_____________
Code for TallyGenerator -
Or even easier:
select * from table
where timestamp_column >= dateadd(mm, -1, dateadd(dd, datediff(dd, 0, GETDATE()), 0) )
AND timestamp_column < dateadd(mm, -1, dateadd(dd, datediff(dd, 0, GETDATE()), 0) ) + 1
_____________
Code for TallyGenerator -
Hi,
I think I posted my question wrong....I meant I wanted to get ALL of the data within the last month, not just the last day of the month, ie something like:
select * from table
where date(timestamp_column) >= current date - 1 month;
tks..
-
No, there was no problem - the question was understood and answered.
If you want to get data for a certain month, the best way (performance wise) is to find date_start and date_end first, and then select everything between these dates. It may look complicated, but so it is... and that's what the code posted by Sergiy does.
Please read this, it should answer your questions why.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=321625
Now I realized there is another possibility, that you need the data not from previous month, but from last 30 days. Then you could write simply
WHERE date_column >= GETDATE()-30
but... as explained in the link, it will be 30 days including hours and minutes and seconds (so that eg. data from 12:35 of the first day will not be included, while data from 12:36 of the same day will). If that doesn't suit you, you have to cut off time portion frist.
-
I think DB2 has SQL Server beat on this one!
-
Select * From Table
Where Date(timestamp_column) >= DateAdd(Day, 1, DateAdd(Month, -1, Convert(Char(10), GetDate(), 120)))
If today is Nov 14, 12:07pm, for example, then...
GetDate() returns 2006-11-14 12:07:00.000
Convert() returns 2006-11-14
The inner DateAdd() returns 2006-10-14 00:00:00.000
The outer DateAdd() returns 2006-10-15 00:00:00.000
So, if you run the query on Nov 14, 2006, you will get all records timestamped Oct 15, 2006 and after.
I don't know the format of timestamp_column so the Date() is not exact syntax, but just a generic representation of using the timestamp_column in a date format.
-
Edwin, don't do datetime to varchar conversion! Ever! Unless it's for reporting purposes.
Such conversion is terribly slow and creates huge load on CPU.
Create 100000 rows temp table and compare performance of your and my queries. It will speak for itself.
db2l, I believe it's not "SQL syntax" question anymore. It's "I don't want to think" issue.
I posted the query to return records with dates from month ago to month ago + 1 day. Now you need dates from month ago to today.
How many seconds of thinking effort it takes?
And don't tell you are not familiar with syntax (corrected, thanks Edwin). Everything you need is in my query. You just need to remove parts you don't need.
_____________
Code for TallyGenerator -
Wow. What prompted all of that hostility?
What you are saying would be true IF I were doing the Convert() on a column (meaning it would have to do the Convert() on every row), but I'm not. SQL Server will do the Convert() ONCE and then compare the resulting value to the timestamp_column of each row. The average time per row will actually decrease as the number of rows increases, and the time taken to calculate the comparison value is spread out over more rows.
-
Edwin,
Yes, in this case you do this stupid conversion once. So, overhead is not that big, but it's still overhead. Unnecessary overhead.
Why stupid? Because you may achieve the same result in another way, without conversion overhead.
But it does not take rocket technology to understand - if you use conversion here you use it everywhere. Am I wrong?
An did you actually compare the performance of the queries? Do it, and than speak in favour of your option. If you'll find the words.
_____________
Code for TallyGenerator -
Is that what all the hostility was about? Because I had the temerity to offer up a solution that was different from yours? Sorry, if I is bein' uppity, massa! I be much better from now on!
-
I wonder, where did you see hostility?
I'm trying to help you to avoid wrong solutions, and you name it hostility???
Man, get rid of you false pride, your solution is really bad, there is nothing to be proud of, you better appreciate the guy who brought you better option. For free.
_____________
Code for TallyGenerator -
Edwin,
Personally, I'd rather get it right first then work on enhancing performance. If doing a date conversion in a query makes it clearer what you are trying to do, then do it that way. After it is right, then you can look for ways to enhance performance.
As a community, we are here to offer suggestions, not flame people for offering suggestions that may not be optimal.
Keep on offering your suggestions!
-
Sergiy: "db2l, I believe it's not "SQL syntax" question anymore. It's "I don't want to think" issue."
Sergiy: "How many seconds of thinking effort it takes?"
Sergiy: "And don't tell you are not familiar with index."
Sergiy: "Yes, in this case you do this stupid conversion once."
Sergiy: "An did you actually compare the performance of the queries? Do it, and than speak in favour of your option. If you'll find the words."
Sergiy: "Man, get rid of you false pride, your solution is really bad, there is nothing to be proud of, you better appreciate the guy who brought you better option."
Gee, you're right. No hostility, there
And, yes, your solution may be a few thousandths of a second faster. I bow to your god-like intellect, and offer an apology from the galactically stupid.
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply