comparing Dates in SQL
-
possibly a dumb question here but in a SQL statement, how do i compare dates?
for instance, i have a table with a date field of type datetime.
i want to select all records from the table where the date field is greater than 1/1/2005
i tried
Select *
FROM tablename
WHERE datefield > 1/1/2005
but that did nto return the desired results.
how do i do this?
-
Select *
FROM tablename
WHERE datefield > '1/1/2005'
This is assuming you're having a syntaxe error..
What results did you get... what did you want to have?
-
well it looks like when i say:
Select *
FROM tablename
WHERE datefield > '1/1/2005'
it acts as if i said:
Select *
FROM tablename
WHERE datefield >= '1/1/2005'
because the results include everything on 1/1/2005 and after.
-
If you don't supply a time along with a date, SQL Server automatically assumes midnight (there are no independant Date and Time datatypes up to SQL Server 2000, there are supossed to be ones in SQL Server 2005). Look up the DATETIME datatype in SQL Server Books Online.
If you want all records greater than 1/1/2005, try:
Select *
FROM tablename
WHERE datefield >= '1/1/2005 23:59:59.000'
or '1/1/2005 23:59:59.999' or whatever works for you.
(do a Select getdate() to see the current time in this format).
Also, do a search on this site for DATETIME. I believe there are several articles on the subject.
-
cool - thank you!
-
To compare dates in T-SQL use DATEDIFF function and you won't need to warry about the time part.
Select *
FROM tablename
WHERE DATEDIFF(day, '1/1/2005’, datefield) > 0
-
Also beware international settings '1/1/2005' is fine - but what if you want 1st Feb - is it '1/2/2005' or '2/1/2005' - that depneds on reginal settings etc - I prefer to use '1 Feb 2005' but even that may fail is your lang settings are not some English variant
In that case you should use some ISO format e.g. '20050201' SQL will always take this as YYYYMMDD
James Horsley
Workflow Consulting Limited -
DATEDIFF is not the best option on a larger table as it will prevent the use of an index
See if this helps: http://www.sql-server-performance.com/fk_datetime.asp
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
users typically only submit m/d/yyyy. SQL Server stores time as well. So, i create the date string equalizing the time then query
Create Proc p_Orders_GetByDateRange
(
@StartDate DATETIME
,@EndDate DATETIME)
AS
DECLARE @NewStartDate DATETIME
DECLARE @NewEndDate DATETIME
SET @NewStartDate = CONVERT(VARCHAR(12),@StartDate,101) + ' 00:00:00 AM'
SET @NewEndDate = CONVERT(VARCHAR(12),@EndDate,101) + ' 11:59:59 PM'
SELECT OrderID,OrderDate, CustomerID, TotalFreight
FROM tblOrders
WHERE OrderDate Between @NewStartDate AND @NewEndDate
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply