Simple DateTime sql query
Solution 1
You missed single quote sign:
SELECT *
FROM TABLENAME
WHERE DateTime >= '12/04/2011 12:00:00 AM' AND DateTime <= '25/05/2011 3:53:04 AM'
Also, it is recommended to use ISO8601 format YYYY-MM-DDThh:mm:ss.nnn[ Z ], as this one will not depend on your server's local culture.
SELECT *
FROM TABLENAME
WHERE
DateTime >= '2011-04-12T00:00:00.000' AND
DateTime <= '2011-05-25T03:53:04.000'
Solution 2
You need quotes around the string you're trying to pass off as a date, and you can also use BETWEEN here:
SELECT *
FROM TABLENAME
WHERE DateTime BETWEEN '04/12/2011 12:00:00 AM' AND '05/25/2011 3:53:04 AM'
See answer to the following question for examples on how to explicitly convert strings to dates while specifying the format:
Sql Server string to date conversion
Solution 3
This has worked for me in both SQL Server 2005 and 2008:
SELECT * from TABLE
WHERE FIELDNAME > {ts '2013-02-01 15:00:00.001'}
AND FIELDNAME < {ts '2013-08-05 00:00:00.000'}
Solution 4
You can execute below code
SELECT Time FROM [TableName] where DATEPART(YYYY,[Time])='2018' and DATEPART(MM,[Time])='06' and DATEPART(DD,[Time])='14
Solution 5
select getdate()
O/P
----
2011-05-25 17:29:44.763
select convert(varchar(30),getdate(),131) >= '12/04/2011 12:00:00 AM'
O/P
---
22/06/1432 5:29:44:763PM
ove
Updated on January 01, 2022Comments
-
ove over 2 years
How do I query DateTime database field within a certain range?
I am using SQL SERVER 2005
Error code below
SELECT * FROM TABLENAME WHERE DateTime >= 12/04/2011 12:00:00 AM AND DateTime <= 25/05/2011 3:53:04 AM
Note that I need to get rows within a certain time range. Example, 10 mins time range.
Currently SQL return with Incorrect syntax near '12'."
-
ove almost 13 yearsDoes not work as well Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
-
ove almost 13 yearsSame error as above. Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. I am really confused.
-
Alex Aza almost 13 years@overule - try using the second example
-
Alex Aza almost 13 years@overule - also, make sure that your column has datetime type.
-
Alex Aza almost 13 years@Eric - yyyymmdd doesn't have time.
-
Milimetric almost 13 yearsoh, I just copied your date time, you've got 25/05 but it's probably expecting month/date so switch those both around. I edited my answer and linked to a good question here on SO that shows how to convert strings to dates explicitly