CODEWITHSUNDEEP
sql
Mr Man
Mr Man

Reputation: 1588

Comparing dates in Sql

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.

Upvotes: 0

Views: 287

Answers (3)

megamanblue
megamanblue

Reputation: 1

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

Upvotes: 0

user330315
user330315

Reputation:

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';

Upvotes: 1

James Hill
James Hill

Reputation: 61792

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).

Upvotes: 0

Related Questions