Really Silly Date Question. Please Help 2

[hegartyjp]

Hi All,

This is going to sound rather silly I fear.
I have just started using stored procedures and am trying to select a number of records with a start date less than 21/03/03.

If I link the table I am running the stored procedure on to access and run a query I get results no problem.

The stored procedure however returns nothing.

I have stripped the conditions and joins out and am left with this:

SELECT *
FROM myTable
WHERE myField <21/03/03

I have also tried

SELECT *
FROM myTable
WHERE convert(smalldatetime,myField,103) <21/03/03

etc, etc.


Can some one please tell me what is wrong.

I know it is a basic question but it is driving me mad.

Thank you all.

 

[gmmastros]

when specifying dates, you need to surround them with apostrophes. I suspect SQL Server is treating that condition as 21 divide by 3 divide by 3 again.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[hegartyjp]

If I put in '21/03/03'

I get a charachter to date conversion error msg.

Thanks for answering so quickly

 

[SQLDenis]

use ISO dates (YYYYMMDD)
'20030321'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[hegartyjp]

Thank you so much....

I can go home now...

 

[SQLBill]

OR start your script with
SET DATEFORMAT DMY

SQL Server has to know how to interpret dates that are strings. Is 21/03/03 the 21st of March or is it the 3rd day of the 21st month (which doesn't exist)? YYYYMMDD or YYYY-MM-DD are not mis-interpreted by SQL Server, which is why SQLDenis suggested that.

-SQLBill

Posting advice: FAQ481-4875