Simple DateTime sql query

Question

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'."

clyc's answer is correct: you need to add quotes. I've made this mistake before, because if you do `where date = 1/12/2011` for example, it will *appear* to work, but actually divide 1/12/2011 and get zero, and then treat zero as the minimum possible datetime value. — John Gibb, May 25 '11 at 05:20

Alex Aza · Accepted Answer · 2011-05-25T04:05:33.123

127

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'

edited May 25 '11 at 04:05

answered May 25 '11 at 04:00

Alex Aza

73,821
25
151
132

Does 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 May 25 '11 at 04:06
@overule - try using the second example – Alex Aza May 25 '11 at 04:07
@overule - also, make sure that your column has datetime type. – Alex Aza May 25 '11 at 04:09
3

@Eric - yyyymmdd doesn't have time. – Alex Aza May 25 '11 at 04:19

score 9 · Answer 2 · edited May 23 '17 at 11:33

9

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

edited May 23 '17 at 11:33

Community

1
1

answered May 25 '11 at 04:01

Milimetric

13,206
4
42
54

Same 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. – ove May 25 '11 at 04:07
oh, 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 – Milimetric May 25 '11 at 12:06

score 8 · Answer 3 · edited Sep 14 '13 at 23:50

8

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'}

edited Sep 14 '13 at 23:50

Adi Inbar

11,603
13
53
68

answered Sep 14 '13 at 23:27

Jamaria

81
1
2

score 6 · Answer 4 · edited Jun 14 '18 at 12:24

6

You can execute below code

SELECT Time FROM [TableName] where DATEPART(YYYY,[Time])='2018' and DATEPART(MM,[Time])='06' and DATEPART(DD,[Time])='14

edited Jun 14 '18 at 12:24

Ratan Uday Kumar

4,634
5
28
53

answered Jun 14 '18 at 11:31

Abdul Rafey

61
1
1

clyc · Answer 5 · 2011-05-25T04:22:53.820

1

SELECT * 
  FROM TABLENAME 
 WHERE [DateTime] >= '2011-04-12 12:00:00 AM'
   AND [DateTime] <= '2011-05-25 3:35:04 AM'

If this doesn't work, please script out your table and post it here. this will help us get you the correct answer quickly.

edited May 25 '11 at 04:22

answered May 25 '11 at 04:17

clyc

2,390
13
15

ErikE · Answer 6 · 2015-11-25T19:29:59.873

Others have already said that date literals in SQL Server require being surrounded with single quotes, but I wanted to add that you can solve your month/day mixup problem two ways (that is, the problem where 25 is seen as the month and 5 the day) :

Use an explicit Convert(datetime, 'datevalue', style) where style is one of the numeric style codes, see Cast and Convert. The style parameter isn't just for converting dates to strings but also for determining how strings are parsed to dates.
Use a region-independent format for dates stored as strings. The one I use is 'yyyymmdd hh:mm:ss', or consider ISO format, yyyy-mm-ddThh:mi:ss.mmm. Based on experimentation, there are NO other language-invariant format string. (Though I think you can include time zone at the end, see the above link).

score 1 · Answer 7 · answered May 25 '11 at 12:06

1

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

answered May 25 '11 at 12:06

jack.mike.info

120
3

score 0 · Answer 8 · answered Sep 26 '13 at 14:17

Open up the Access File you are trying to export SQL data to. Delete any Queries that are there. Everytime you run SQL Server Import wizard, even if it fails, it creates a Query in the Access DB that has to be deleted before you can run the SQL export Wizard again.

Simple DateTime sql query

8 Answers8

Linked