March 28, 2014 at 7:08 am
I want to retrieve all the records from a table where the ActiveFromDate(column with datetime datatype) is less than or equal to current date and
ActiveToDate(column with datetime datatype) is greater than equal to current date and I am using the following query:
select * from vendorInfo where (activefromdate <=Convert(datetime, GetDate(), 101)) AND (activetodate >= Convert(datetime, GetDate(), 101))
It is working well except in one scenario when ActiveToDate = currentDate, where it doesn't return true.
The reason is I am saving only date in these fields and I want to compare only the date and not datetime
but this query is taking time as well into consideration.
Can some one please help me modify the query that checks only date and not datetime. Thankyou.
March 28, 2014 at 7:44 am
You need to convert the current datetime (from GetDate() ) to a DATE datatype instead of a DATETIME datatype. When comparing this to a DATETIME value it will be converted to a DATETIME again and the time will be set to "00:00:00.000".
Convert(date, GetDate(), 101)
March 28, 2014 at 11:06 am
When I change
Convert(datetime, GetDate(), 101) to
Convert(date, GetDate(), 101)
I see following error:
'Type date is not a defined system type.'
Sorry I am actually using SQL server 2005 and not 2008, posted in the wrong forum..
Anyway, appreciate any help thanks!
March 28, 2014 at 12:33 pm
Annee (3/28/2014)
When I changeConvert(datetime, GetDate(), 101) to
Convert(date, GetDate(), 101)
I see following error:
'Type date is not a defined system type.'
Sorry I am actually using SQL server 2005 and not 2008, posted in the wrong forum..
Anyway, appreciate any help thanks!
Since you don't have the date datatype you need to use some date math to make this happen. Take a look at Lynn's blog post here. He has a ton of common datetime calculations on there. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 28, 2014 at 1:18 pm
I always use this when I need to compare dates...
select convert(char,getdate(),112)
RESULT = 20140328
hth,
..bob
March 28, 2014 at 1:25 pm
Bob McClellan-320407 (3/28/2014)
I always use this when I need to compare dates...select convert(char,getdate(),112)
RESULT = 20140328
hth,
..bob
The problem with this type of approach is you also have to convert your actual datetime column which renders your query nonSARGable.
Where convert(char, YourDateColumn, 112) < convert(char, getdate(), 112)
UGH!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 28, 2014 at 1:34 pm
Bob McClellan-320407 (3/28/2014)
I always use this when I need to compare dates...select convert(char,getdate(),112)
RESULT = 20140328
hth,
..bob
Faster over time: select dateadd(day,datediff(day,0,getdate()),0)
If you are on a SQL Server 2008 or newer: select cast(getdate() as date) -- actually usable in SARGable queries.
March 28, 2014 at 1:35 pm
Thanks Sean... I appreciate the heads up on that.
Embarrassed to say I did not consider that 🙁
March 28, 2014 at 1:56 pm
Bob McClellan-320407 (3/28/2014)
Thanks Sean... I appreciate the heads up on that.Embarrassed to say I did not consider that 🙁
No need to be embarrassed at all. This whole sql thing is a continuous learning experience for ALL of us. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 28, 2014 at 2:02 pm
Thanks 🙂 ... I agree. That's the fun part... 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply