How to compare dates in SQL Server?

Question

I have a row with date column in my table:

date1 = 2013-12-11 00:00:00.000

And I want to compare it with date:

date2 = 11.12.2013 0:00:00

I do:

SELECT *
FROM ServiceOrder 
WHERE CreateDateTime <= '11.12.2013 0:00:00'

And I am expecting that as a result I get a row with date1 but I don't get it.

How to compare this dates correctly?

UPDATE

I tried:

SELECT *
FROM ServiceOrder 
WHERE CreateDateTime<= CONVERT(VARCHAR(23),'11.12.2013 0:00:00', 121)

But same result.

UPDATE2

SELECT *
FROM ServiceOrder 
WHERE CreateDateTime <= REPLACE(CONVERT(VARCHAR(19),'11.12.2013', 111),'/','-')

Does not work, either.

UPDATE3

SELECT *
FROM ServiceOrder 
WHERE CreateDateTime <= CONVERT(datetime,'11.12.2013 0:00:00', 104)

Does not work, either.

Answer 1

So it is a bit tricky to convert your format because it is too specific and ms sql doesn't have easy date format functions to do it. so here is an way to convert your entire date:

SELECT *
  FROM ServiceOrder 
 WHERE CreateDateTime <= (
                         select convert(datetime, 
                          concat(y,'-',m,'-',d,' ',h), 20) as dtConverted
                         from ( select substring(dt,1,2) d,
                                       substring(dt,4,2) m,
                                       substring(dt,7,4) y,
                                       substring(dt,12,10) h
                                  from (select 
                                         '11.12.2013 13:00:00' dt) as value
                               ) as convDate
                        )

To convert just to a date without time it would be:

SELECT *
  FROM ServiceOrder 
 WHERE convert(date, CreateDateTime , 104) <= convert(date, 
                               substring('11.12.2013 0:00:00',1,10), 104 )

See it here on fiddle: http://sqlfiddle.com/#!6/3787b/1

Answer 2

You should stick with same date time constant format.

Check out SET DATEFORMAT.

Sets the order of the month, day, and year date parts for interpreting date, smalldatetime, datetime, datetime2 and datetimeoffset character strings.

http://technet.microsoft.com/en-us/library/ms189491.aspx.

Default format is month, day, year.

TSQL is smart enough to know that date1 is not in the default format since 2013 can not be a month. So it interprets it as 11 DEC 2013.

Date 2 has both the month and day in acceptable ranges. So you get 12 NOV 2013. Therefore nothing is returned from the where clause in your TSQL statement.

Check half way down on this page, it shows acceptable literal strings for date time in both numeric and alpha-numeric format.

http://technet.microsoft.com/en-us/library/ms187819.aspx

Lets cap this off with a little test program. Simple table, three months (OCT, NOV, DEC) of the 11th day. It returns only one date that is less than 10 NOV 2013. Correct answer.

-- use temp db
use tempdb;
go

-- simple test table
create table my_test
( my_date datetime );

-- add 3 rows
insert into my_test values ('2013-12-11 00:00:00.000');
insert into my_test values ('2013-11-11 00:00:00.000');
insert into my_test values ('2013-10-11 00:00:00.000');

-- show the data
select * from my_test;

-- just show october
select * from my_test where my_date < '2013-11-10';

I hope this helps you out.

Answer 3

Use this. You need to convert the string into datetime for right comparison. And also use the correct format number here 104

SELECT *
FROM ServiceOrder 
WHERE CreateDateTime<=CONVERT(datetime,'11.12.2013 0:00:00', 104)

And the better practice is that to take a variable and use that in query instead of converting them in query. Like this

Declare @CompareDate as DateTime
SET @CompareDate=CONVERT(datetime,'11.12.2013 0:00:00', 104)

    SELECT *
    FROM ServiceOrder 
    WHERE CreateDateTime<=@CompareDate