asked on

SQL Dates

My results are not returning the correct results.

--[ApptBookedDate] datetime
--YYYY-MM-DD HH:MI:SS

WHERE
CONVERT(DATETIME, o.ApptBookedDateTime) < '2014-04-12 14:00:00:000'

returns - 2014-12-04 13:59:00.000

Haris Dulic

Hi,

not really sure what is the problem here, what should be returned i.e. desired output?

aneilg

ASKER

I want dates returned < 12th April 2014 - 2pm

my results return 04th December 2014.

aneilg

ASKER

o.ApptBookedDateTime_dte is a datetime field.

so I should be able just to do o.ApptBookedDateTime_dte < '2014-04-12 14:00:00:000

aneilg

ASKER

this seems to work. o.ApptBookedDateTime_dte < '2014-04-12'

if I remove the HH:MI:SS but I need them in the query.

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aneilg

ASKER

still returns 2014-12-04 13:59:00.000

04th December

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aneilg

ASKER

thanks.

forgot to add

SET DATEFORMAT MDY

Guy Hengel [angelIII / a3]

>still returns 2014-12-04 13:59:00.000
so, you want to format the OUTPUT ...

which is in the SELECT itself:

SELECT CONVERT(varchar(20), o.ApptBookedDateTime, 101)
FROM ...
WHERE o.ApptBookedDateTime < CONVERT(datetime, '2014-04-12 14:00:00:000' , 120)

Jim Horn

<wild guess>
Copy-paste the COMPLETE WHERE clause into this question, just in case there is any logic such as AND / OR or any precedence constraints ( ) that is causing this date comparison to only fire on a subset of rows.