SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 [RESOLVED] Date condition
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 02/10/2011 :  10:24:26  Show Profile  Reply with Quote
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.


Edited by - KlausEngel on 02/20/2011 18:24:11

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 02/10/2011 :  10:33:29  Show Profile  Reply with Quote
Use the following in the where clause of your select statement

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


Edited by - MIK_2008 on 02/10/2011 10:34:34
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/10/2011 :  13:31:00  Show Profile  Reply with Quote
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)

Edited by - Lamprey on 02/10/2011 13:32:39
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 02/11/2011 :  00:58:31  Show Profile  Reply with Quote
@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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/11/2011 :  11:20:40  Show Profile  Reply with Quote
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

India
22761 Posts

Posted - 02/14/2011 :  05:46:34  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Iraq
5 Posts

Posted - 02/14/2011 :  12:12:00  Show Profile  Reply with Quote
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 - 02/18/2011 :  17:48:41  Show Profile  Reply with Quote
Thanks for all the great input!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000