Select datetime data from SQL Server colum

Question

SELECT [NEXT_UPDATE_TIME] 
FROM [BGREX_UpdateQueue]
WHERE [NEXT_UPDATE_TIME] <  18/12/2009

I want to select those dates from the records where datetime value in column is less than some datetime, we need to compare. I have several value lass than "18/12/2009", but getting no rows returned.

following syntax does not work either

SELECT 18/12/2009 // returns 0 
WHERE [NEXT_UPDATE_TIME] < '18/12/2009'; // The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

What is the right syntax to pass '18/12/2009' or some other date ? thanks

Answer 1

Try:

select next_update_time from bgrex_updatequeue
  where next_update_time < '2009-12-18';

Answer 2

This should work :

SELECT [NEXT_UPDATE_TIME] 
FROM [BGREX_UpdateQueue]
WHERE [NEXT_UPDATE_TIME] < '2009-12-18'

By the way you're getting the The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. error because of the date's format, if you try 12/18/2009, it should work also, but the query above is the best one I think.

Answer 3

DATEDIFF is your friend in this case.

Answer 4

If you are using a DataView, it can be accomplished easily doing this (for example between dates):

myDataView = new DataView(myDataSet.Tables["TABLE"], "TIMESTAMP >= '" + Convert.ToDateTime(fromDate) + "' AND TIMESTAMP <= '" + Convert.ToDateTime(toDate) + "'", "TIMESTAMP", DataViewRowState.CurrentRows);

toDate and fromDate are of type string

There is also an article on Working with SQL Server Date/Time Variables: Part Three - Searching for Particular Date Values and Ranges

Answer 5

The query in your example is missing quotes around the date, it should be:

SELECT [NEXT_UPDATE_TIME] 
FROM [BGREX_UpdateQueue]
WHERE [NEXT_UPDATE_TIME] <  '18/12/2009'

Answer 6

try universal SQL Date Format YYYYMMDD

WHERE [NEXT_UPDATE_TIME] < '20091218'

see standard SQL datetime formats