Simple DateTime SQL Query

Question

How do I set a DateTime database field in a specific 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 

Please note that I need to get rows in a specific time range. Example: a time interval of 10 minutes.

SQL is currently being returned with the wrong syntax near "12".

Answer 1

You have missed the single quote mark:

 SELECT * FROM TABLENAME WHERE DateTime >= '12/04/2011 12:00:00 AM' AND DateTime <= '25/05/2011 3:53:04 AM' 

In addition, it is recommended to use the ISO8601 format YYYY-MM-DDThh: mm: ss.nnn [Z], as this will not depend on the local culture of your server.

 SELECT * FROM TABLENAME WHERE DateTime >= '2011-04-12T00:00:00.000' AND DateTime <= '2011-05-25T03:53:04.000' 

Answer 2

You need quotes around the string you are trying to pass as the date, and here you can also use BETWEEN:

  SELECT * FROM TABLENAME WHERE DateTime BETWEEN '04/12/2011 12:00:00 AM' AND '05/25/2011 3:53:04 AM' 

See the answer to the following question for examples of how to explicitly convert strings to dates when specifying a format:

Convert Sql Server string to date

Answer 3

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

Answer 4

Others have already said that date literals in SQL Server require a single quote environment, but I wanted to add that you can solve the month / day mixing problem in two ways (that is, the problem when 25 is treated as a month and 5 per day):

• Use explicit Convert(datetime, 'datevalue', style) , where style is one of the numeric style codes, see Insert and Convert . The style parameter is designed not only to convert dates to strings, but also to determine how strings are parsed by date.

• Use a region-independent format for dates stored as strings. The one I'm using is "yyyymmdd hh: mm: ss" or is considering the ISO format, yyyy-mm-ddThh:mi:ss.mmm . Based on experiments, there is no other line that does not contain a language. (Although I think you can include the time zone at the end, see the link above).

Answer 5

Open the access file to which you are trying to export SQL data. Delete all Requests that are. Each time you start the SQL Server Import Wizard, even if it fails, it creates a query in the access database that needs to be deleted before you can start the SQL Export Wizard again.

Answer 6

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

If this does not work, script submit your table and post it here. this will help us quickly get the right answer.

Answer 7

 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