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 2008 Forums
 Transact-SQL (2008)
 [RESOLVED] Date condition

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 statement

Where (DateColumn > dateadd (d,-day(getdate()),GETDATE())
and day(DateColumn)=day(getdate()))

Go to Top of Page

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 statement

Where (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)
Go to Top of Page

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


Cheers
MIK
Go to Top of Page

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
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-14 : 05:46:34
or read at date as number part here
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

javad_ghasemiro
Starting Member

5 Posts

Posted - 2011-02-14 : 12:12:00
try this and see result :

select getdate() - 30

wow it works very well !!

because datetime datatype is a float datatype that
each step is a day

for example this statement returns 12 hours ago

select getdate() - 0.5




Javad Ghasemi
Go to Top of Page

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2011-02-18 : 17:48:41
Thanks for all the great input!
Go to Top of Page
   

- Advertisement -