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

Hi Darryl

> All my attempts have been rather pathetic and ended in total and abject failure. 

At least you are honest about this!

That said, as you have control of the source, this is not a KISS method but the right method - to solve the issue at the source.
However, the approach with using the spelled out month names is dangerous as these are localized. The preferred format is to use the ISO format:

ie strSQL = .....& Format(R1C1,"yyyy-mm-dd") & .....

as it will never fail. 

/gustav

>>> Darryl.Collins at coles.com.au 03-10-2008 07:24 >>>

took the "KISS" approach and hey! it works really well.  Feel like a goose as I should have tried/thought of that.

If I ever get to PNG, i owe you a few bottles of your fav one afternoon.

Many thanks :)

cheers
Darryl

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com 
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Stuart
McLachlan
Sent: Friday, 3 October 2008 2:56 PM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] Passing a valid date to a sproc


Simplest way is to pass them formatted as "7 Sep 2008"
If you are building and passing the string in Excel VBA, use the Format()
function just as you would in Access
ie strSQL = .....& Format(R1C1,"d mmm yyyy") & .....

On 3 Oct 2008 at 13:40, Darryl Collins wrote:

> 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.