Menu
I have a column in my table for dates (DateTime) and I am trying to create a WHERE clause that says, WHERE dates BETWEEN 12-11-2012 and 12-13-2012
A sample value of dates column = 2012-05-24 00:38:40.260
I want to say WHERE dates BETWEEN MM-DD-YYYY and MM-DD-YYYY.
I tried doing
WHERE dates BETWEEN ((convert(nvarchar(10), dates,110) = '2012-12-12') AND (convert(nvarchar(10), dates,110) = '2012-12-12')) but doesn't seem to work. "Incorrect syntax near ="
Please help
EDIT:
Thanks for various options and description guys. Got it working with @RichardTheKiwi's options.
219
To filter by a date part, use the WHERE clause with the EXTRACT() function, and pass it the desired date parts (year, month, or day). To filter by a date field, use the WHERE clause with a logical operator. This example filters by year.
If you'd like to see the latest date first and the earliest date last, you need to sort in descending order. Use the DESC keyword in this case. ORDER BY ExamDate DESC ; Note that in T-SQL, NULL s are displayed first when sorting in ascending order and last when sorting in descending order.
Correct syntax for dates in the Query function Per the Query Language documentation, we need to include the date keyword and ensure that the date is in the format yyyy-mm-dd to use a date as a filter in the WHERE clause of our Query function.
If your dates column does not contain time information, you could get away with:
WHERE dates BETWEEN '20121211' and '20121213' However, given your dates column is actually datetime, you want this
WHERE dates >= '20121211' AND dates < '20121214' -- i.e. 00:00 of the next day Another option for SQL Server 2008 onwards that retains SARGability (ability to use index for good performance) is:
WHERE CAST(dates as date) BETWEEN '20121211' and '20121213' Note: always use ISO-8601 format YYYYMMDD with SQL Server for unambiguous date literals.
51
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
