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.
Author |
Topic |
KlausEngel
Yak Posting Veteran
85 Posts |
Posted - 2011-02-10 : 10:24:26
|
I have a simple query retrieving sales information that I have wrapped into an SP. I need to adjust the SP to return only records with a date from within the current month so I just get the month to date records. Any direction would be appreciated.Thanks. |
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-10 : 10:33:29
|
Use the following in the where clause of your select statementWhere (DateColumn > dateadd (d,-day(getdate()),GETDATE()) and day(DateColumn)=day(getdate())) |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-02-10 : 13:31:00
|
quote: Originally posted by MIK_2008 Use the following in the where clause of your select statementWhere (DateColumn > dateadd (d,-day(getdate()),GETDATE()) and day(DateColumn)=day(getdate()))
Assuming that the DateColumn has an index on it, you really don't want to use funtions on a column if you can avoid it. Unfortunately, SQL cannot index seek when you apply a function to the column. It's better to do date math on the Date and then compare to column directly.WHERE DateColumn >= DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) AND DateColumn < DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 1) |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-11 : 00:58:31
|
@lamp, Unfortunately, SQL cannot index seek when you apply a function to the column. what i understood is that my query can degrade performance (due to no index seek) if DateColumn is indexed. well are you referring to the portion Day(Datecolumn)?Next, trying to understand the usage of non-date values in the Date Diff section of your code .. e.g. the highlighted one DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) any link where i can find its detail I know its giving the first day of the current month... But how is my concern here. i would appreciate your help ... Many thanks CheersMIK |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-02-11 : 11:20:40
|
quote: Originally posted by MIK_2008 @lamp, Unfortunately, SQL cannot index seek when you apply a function to the column. what i understood is that my query can degrade performance (due to no index seek) if DateColumn is indexed. well are you referring to the portion Day(Datecolumn)?
That is correct.quote: Next, trying to understand the usage of non-date values in the Date Diff section of your code .. e.g. the highlighted one DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) any link where i can find its detail I know its giving the first day of the current month... But how is my concern here. i would appreciate your help ... Many thanks
Dates are just numbers to SQL, so in this case 0 is day zero (1900-01-01). You could pretty much use any number and long as you are consistant. Here is a link that talks about how SQL Dates:http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
javad_ghasemiro
Starting Member
5 Posts |
Posted - 2011-02-14 : 12:12:00
|
try this and see result :select getdate() - 30wow it works very well !!because datetime datatype is a float datatype thateach step is a day for example this statement returns 12 hours agoselect getdate() - 0.5 Javad Ghasemi |
|
|
KlausEngel
Yak Posting Veteran
85 Posts |
Posted - 2011-02-18 : 17:48:41
|
Thanks for all the great input! |
|
|
|
|
|
|
|