Darryl Collins
Darryl.Collins at coles.com.au
Fri Oct 3 00:24:42 CDT 2008
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. > > > 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 > _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com 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.