[UziTech]

I have a stored procedure with a datetime variable. When I use the the procedure it returns an empty set.

CREATE PROC InvoiceTotals
@date datetime
AS
SELECT InvoiceID, Total, Date
FROM Invoices
WHERE Total > 0 and Date > @date

EXEC InvoiceTotals @date = '20120823'

This returns an empty set, but if I write a select statement it works.

SELECT InvoiceID, Total, Date
FROM Invoices
WHERE Total > 0 and Date > '20120823'

This returns the correct information

Any ideas?

[modi123_1]

Try throwing hyphens in between the year, month, and day. Having it mushed together isn't typically an acceptable date format. As for the second working, my guess is sql is doing some conversions and this instance is a happy accident.

[UziTech]

dumb mistake I had a 3 instead of a 2 in front of the date


EXEC InvoiceTotals @date = '30120823'

modi123_1, on 21 September 2012 - 04:09 PM, said:

Try throwing hyphens in between the year, month, and day. Having it mushed together isn't typically an acceptable date format.

actually no hyphens is the only way to make sure it won't break

http://sqlblog.com/b...ge-queries.aspx

Quote

For those of you not fluent in Français, that essentially says (in my best Quebec accent), "There is no month 13, dummy!" This is because in French that date format is interpreted as YYYY-DD-MM. (For some background on the attempts we've made to deprecate this interpretation, see Connect #290971.)

As opposed to YYYY-MM-DD, YYYYMMDD will never break. If you decide to use any other format for your date string literals, at least for DATETIME and SMALLDATETIME types, you are leaving yourself open to errors or incorrect data should a user have different session settings, or should the application be moved to servers with different settings. In SQL Server 2008, the new types are a little more insulated from user or machine settings; still, I use YYYYMMDD for consistency and to be safe.

This post has been edited by UziTech: 21 September 2012 - 02:11 PM

[modi123_1]

Bleh.. that's why one should always roll the ISO standards of:

YYYY-MM-DDTHH:MM:SS