CODEWITHSUNDEEP
sql-serverdatetime
Garrett Jones
Garrett Jones

Reputation: 47

Search By Date in SQL Server 2012

We just upgraded to SQL Server 2012 from 2005. While I'm a novice, something this simple couldn't be this difficult. I used to be able to pull data from a table based on the date vs date and time. As it now stands I have:

Select * from receipts_table where receipt_cancel_date = '2013-09-20'

before we upgraded this would work fine. How can I run this and actually get the desired results as I know there's receipts with a cancel date of 2013-09-20.

Thanx

Upvotes: 1

Views: 9204

Answers (1)

Kaf
Kaf

Reputation: 33829

If you are passing string for a date parameter, best format is ISO (yyyymmdd) format. Otherwise even though your string work in some servers it might not work in another depending on the culture of the server. ISO format is culture independent.

Also remove the time part from receipt_cancel_date column by converting it to a DATE (if DATETIME) for comparison purpose.

Try this:

Select * from receipts_table 
where convert(date, receipt_cancel_date) = convert(date,'20130920')

Or use 120 style with your format:

Select * from receipts_table 
where convert(date, receipt_cancel_date) = convert(date,'2013-09-20',120)

Upvotes: 1

Related Questions