CODEWITHSUNDEEP
sqlsql-server-2008
maztt
maztt

Reputation: 12294

SQL Server datetime comparison in different formats

I have a SQL query in which I will be passing dd/mm/yyyy but the SQL query requires mm/dd/yyyy.

How can I enable this query to take dd/mm/yyyy and display the correct result based on the correct format? What is the best way to achieve this thing in SQL Server 2008?

SELECT int_ExchangeRateId, int_LocationId, dtm_Date
FROM ExchangeRate
WHERE dtm_Date >= '01/02/2006' AND dtm_Date <= '12/02/2006' AND int_LocationId = 98

THIS WORKS CORRECTLY:

SELECT int_ExchangeRateId, int_LocationId,dtm_Date 
FROM ExchangeRate 
WHERE CAST(dtm_Date AS DATE) BETWEEN '2006-02-02' AND '2006-02-12' 
  AND int_LocationId=98

Upvotes: 0

Views: 19564

Answers (2)

Tony
Tony

Reputation: 10327

For the query I would use dates in YYYY-MM-DD format. As to the output see kalyan's answer.

EDIT: To summarise the comments below (thanks to marc_s) it is necessary to use the format YYYYMMDD when the time part is not included unless you are using the new date types date, datetime2 and datetimeoffset in SQL Server 2008.

Upvotes: 5

Sai Kalyan Kumar Akshinthala
Sai Kalyan Kumar Akshinthala

Reputation: 11844

Refer to the DateTime Formating for this purpose, by providing certain parameters we can format the date into required format.

Upvotes: 2

Related Questions