MS Access / Excel SQL Server query producing Conversion error DATETIME

Question

I've looked around for a solution for this, but none of them seem to fix my problem

SELECT TOP 10 [Appointment_Date]
FROM dbo.RF_Performance_Referrals_Main
WHERE (([Appointment_Date]) < '7/21/2014')
ORDER BY [Appointment_Date] DESC

Above is the Simplified version of the query I am trying to. I keep get an error

[Microsoft][ODBC SQL Server Driver][SQL Server]Then Conversion of a varchar datatype to a datetime data type resulted in an out of range value

I thought maybe there is a problem with my connection...

so I tried it on Excel and got the exact same error...

I checked the SQL Server table that particular field is set to DATETIME

So why is it giving an error?

I tried all sorts of stuff on Cast/Convert, but none of them seem to work i still get the same error. I don't really get why.

Please help if fix this.

Thanks a lot in advance

have you tried using a date time? '7/21/2014 00:00:00' and what local is on the sever? stackoverflow.com/questions/1947436/datetime-in-where-clause — xQbert, Commented Jul 21, 2014 at 11:25
codeSELECT TOP 10 [Appointment_Date] FROM dbo.RF_Performance_Referrals_Main WHERE ([Appointment_Date] < '7/21/2014 00:00:00') ORDER BY [Appointment_Date] DESC Tried what you said... Same Error.. it used to work brilliantly till someone higher up decided they wanted to merge prev & future appointments. I am going to try reading through the second link. This problem is really affecting the department we can't see who requires and urgent appointment >_< and the Information Team that made this table don't have a clue why either it's been 3-4 weeks they are still trying to sort it out T_T — RcK, Commented Jul 21, 2014 at 11:49
Nevermind.. Think it's working don't understand why, but it does not like any other date format except '2014-21-7' Thanks a lot -- Would you like to post that so that I can accept your answer ... it was the second link that got me trying '2014-21-7' — RcK, Commented Jul 21, 2014 at 12:16

xQbert · Accepted Answer · 2014-07-21 12:26:13Z

1

SQL Server's default date time syntax is YYYY-MM-DD HH:MM:SS as in (1900-01-01 00:00:00)

You either have to cast your string to a date or reformat your input to the expected datetime defaults: http://msdn.microsoft.com/en-us/library/ms187819.aspx: Assuming US Local and standard defaults.

answered Jul 21, 2014 at 12:26

xQbert

35.5k2 gold badges45 silver badges67 bronze badges

baradosa · Accepted Answer · 2014-09-16 16:20:33Z

0

Change to

SELECT TOP 10 [Appointment_Date]
FROM dbo.RF_Performance_Referrals_Main
WHERE (([Appointment_Date]) < #2014/07/21#)
ORDER BY [Appointment_Date] DESC

answered Sep 16, 2014 at 16:20

baradosa

547 bronze badges

2 Answers 2