Comparing dates in Sql

Question

I am having trouble trying to compare dates in my sql query:

SELECT restrictions, restrictions_adddate FROM restrictions WHERE id = '555555'
AND restrictions_adddate BETWEEN ? and ?;

Both of the parameters system print in the format 'mm-dd-yyyy'. I am getting a wrong date format error, but have tried doing it in multiple ways. Any help is appreciated, and if more info is needed, please let me know. thanks.

yes, I tried this as well, I get a literal does not match string error. — Mr Man
– Mr Man, Commented May 26, 2011 at 14:07

user330315 · Accepted Answer · 2011-05-26 14:20:05Z

1

This should work on any standard compliant DBMS:

SELECT restrictions, restrictions_adddate 
FROM restrictions 
WHERE id = '555555'
  AND restrictions_adddate BETWEEN DATE '2011-01-01' and DATE '2011-12-31';

answered May 26, 2011 at 14:20

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

user330315 Over a year ago

@Dems: that is not true. The format DATE '2011-01-01' is defined by the ANSI standard such that the literal is required to be in ISO format (yyyy-mm-dd). If you leave out the keyword DATE then you are absolutely right.

Comfortably Numb Over a year ago

@a_horse_with_no_name: Actually it is true. SQL Server when in British English mode inteprets this format as 'yyyy-dd-mm' unless you add a time part e.g. "2012-07-13T00:00:00"

user330315 Over a year ago

@Trekstuff: then SQL Server badly violates the standard.

James Hill · Accepted Answer · 2011-05-26 14:05:16Z

0

This is my preference:

AND restrictions_adddate BETWEEN
    CONVERT(datetime, FLOOR(CONVERT(float, CAST('01-01-1900' AS DATETIME))))
    AND CONVERT(datetime, FLOOR(CONVERT(float, Getdate())));

This allows you to compare dates (minus the time stamps).

answered May 26, 2011 at 14:05

James Hill

62.1k22 gold badges150 silver badges166 bronze badges

3 Comments

Mr Man Over a year ago

I tried this, but I am unfamiliar with most of the terms. How exactly is this comparing the dates?

MatBailie Over a year ago

He's taking a date with a time and removing the time part. which isn't really pasrt of your question.

James Hill Over a year ago

@Dems, the questions was how to compare two dates. Regardless of whether I'm removing the time or not, that's what the code does. My example shows how to use the current date, as well as any date that you want to plug in as a string - '01-01-1900'.

GDP · Accepted Answer · 2012-08-02 00:24:20Z

0

Try checking out the CONVERT function T-SQL reference in BOL. You should be able to use this dictate the format of your dates.

edited Aug 2, 2012 at 0:24

GDP

8,1636 gold badges48 silver badges84 bronze badges

answered May 26, 2011 at 14:00

megamanblue

11 bronze badge

Collectives™ on Stack Overflow

Comparing dates in Sql

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related