March 30, 2012 at 9:44 am
Hello
Can not get this query to bring up the rows with just the dates i have asked for in the between - tried writing the dates another way but still not working.
select *
from TableName
where convert(char(10),SubDate,103)between '26-03-2012' and '30-03-2012';
Don't know if this helps, but it is in the table design
CONVERT (datetime, SubDate, 103)
CONVERT (char(10), SubDate, 103)
Thanks for any help
March 30, 2012 at 9:52 am
subdate column datatype ? Can you share the table schema.
March 30, 2012 at 10:00 am
Yeah what is Subdate data type?
If its datetime keep it as datetime in the WHERE clause...
March 30, 2012 at 10:00 am
Hello
SubDate is char(10)
schema is dbo
thanks
March 30, 2012 at 1:12 pm
The query that was used in your first post caused an alphabetic range to be created and used (char(10)). This would lead to the range '26-03-2012' to '30-03-2012' to include all dates that start with 27,28,29 to be "between" regardless of month and year (i.e. 27-03-2050, 29-12-2012 would both be returned).
select *
from TableName
where convert(char(10),SubDate,103)between '26-03-2012' and '30-03-2012';
Stick either dates or reverse the date format used such as 20120326. Easier to deal with dates using date datatypes. In the code that follows I use datetime but you could use datetime2 or date (SQL 2008 onwards).
Option (1) using dates [assuming char(10) for SubDate data type]
select *
from TableName
where convert(datetime,SubDate,103) between '26-03-2012' and '30-03-2012';
Option (2) using char(10)
select *
from TableName
where convert(char(10),convert(datetime,SubDate,103),112) between '20120326' and '20120330';
Fitz
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply