3 Replies - 1274 Views - Last Post: 21 September 2012 - 02:21 PM

#1 UziTech  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 64
  • Joined: 26-October 10

Stored procedure datetime variable

Posted 21 September 2012 - 01:57 PM

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?

Is This A Good Question/Topic? 0
  • +

Replies To: Stored procedure datetime variable

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8939
  • View blog
  • Posts: 33,521
  • Joined: 12-June 08

Re: Stored procedure datetime variable

Posted 21 September 2012 - 02:09 PM

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.
Was This Post Helpful? 0
  • +
  • -

#3 UziTech  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 64
  • Joined: 26-October 10

Re: Stored procedure datetime variable

Posted 21 September 2012 - 02:15 PM

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


EXEC InvoiceTotals @date = '30120823'

View Postmodi123_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

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8939
  • View blog
  • Posts: 33,521
  • Joined: 12-June 08

Re: Stored procedure datetime variable

Posted 21 September 2012 - 02:21 PM

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

YYYY-MM-DDTHH:MM:SS
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1