[dba-SQLServer] Passing a valid date to a sproc

Hi Darryl,

I think using SET DATEFORMAT is the easiest way - this should work for you:

SET DATEFORMAT dmy
stprPopulateCRSActuals 7437, 'Robyn X', 'Freeman', 4.625, 'A050', 3,
'14/09/2008', 'HCSMVO', '24227', 'ISO', '2', '2', 'HCSMVO', 'IS0001',
'Problem', 'Staff', 2178.19, 2178.19, 58.87, 0, 7, 74, 1, 2

Asger


-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Darryl
Collins
Sendt: 3. oktober 2008 05:40
Til: 'Discussion concerning MS SQL Server'
Emne: [dba-SQLServer] Passing a valid date to a sproc

Hi folks,

*$##!@!! date Formats.  They get me everytime.  wow, not sure where to
start.

I have a stored proc that I am feeding variable to via a SQL string from
Excel.  On of the values is a date that is formatted in Aussie Style (that
dd/mm/yyyy).  The sproc fails as it is reading the date as mm/dd/yyyy so any
date that i have where the dd bit is > 12 it fails.

How can i ensure that the date passed is in an acceptable format?

For example, this line will pass (although wrongly I suspect as July 9,
2008)
stprPopulateCRSActuals 7437, 'Robyn X', 'Freeman', 4.875, 'A050', 3,
'7/09/2008', 'HCSMVO', '24227', 'ISO', '2', '2', 'HCSMVO', 'IS0001',
'Problem', 'Staff', 2295.93, 2295.93, 58.87, 0, 7, 74, 1, 2

But this one will fail

stprPopulateCRSActuals 7437, 'Robyn X', 'Freeman', 4.625, 'A050', 3,
'14/09/2008', 'HCSMVO', '24227', 'ISO', '2', '2', 'HCSMVO', 'IS0001',
'Problem', 'Staff', 2178.19, 2178.19, 58.87, 0, 7, 74, 1, 2

because the date is 14/09/2008 (dd/mm/yyyy) and being read as (mm/dd/yyyy).

cheers
Darryl.


This email and any attachments may contain privileged and confidential
information
and are intended for the named addressee only. If you have received this
e-mail in
error, please notify the sender and delete this e-mail immediately. Any
confidentiality, privilege or copyright is not waived or lost because this
e-mail
has been sent to you in error. It is your responsibility to check this
e-mail and
any attachments for viruses.  No warranty is made that this material is free
from
computer virus or any other defect or error.  Any loss/damage incurred by
using this
material is not the sender's responsibility.  The sender's entire liability
will be
limited to resupplying the material.


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com