Darryl Collins
Darryl.Collins at coles.com.au
Thu Oct 2 22:59:02 CDT 2008
Ok.... Found this in help "CONVERT(DATETIME, '7/19/1996', 103)" and it looks promising, but I have no idea where to put it into the sproc to make it actually work. All my attempts have been rather pathetic and ended in total and abject failure. The example shown in help is related to a query rather than fixing it up in a sproc - all rather new to this so sorry if this is a dumb question. hmmm.. :-/ Here is the sproc if it will help.... '==================================================================================== if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[stprPopulateCRSActuals]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[stprPopulateCRSActuals] GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /* Name: stprPopulateCRSActuals Description: Populate the CRS Actuals table from the Excel Uploader file. Author: Darryl collins Modification Log: Change Description Date Changed By Created procedure 03-Oct-2008 Darryl Collins */ CREATE PROCEDURE dbo.stprPopulateCRSActuals @EmployeeID int, @EmployeeFirstName nvarchar (100), @EmployeeSurname nvarchar (100), @Effort float, @ProductCode nvarchar (6), @ApprovePeriod int, @WeekEnding datetime, @CostCentreCode nvarchar (20), @ProjectID int, @HomeDepartmentCode nvarchar (10), @ProductTypeID int, @ProducGroupID int, @HomeCostCentre nvarchar (20), @TaskID nvarchar (20), @TaskName nvarchar (255), @AccountsCategoryDescription nvarchar (20), @CRSValue money, @Capex money, @ChargeOutRate money, @Opex money, @FYID int, @FYPID int, @MECID int, @ForecastStatusID int AS INSERT INTO dbo.ITFCT_tblActualCRS_Effort (EmployeeID, EmployeeFirstName, EmployeeSurname, Effort, ProductCode, ApprovePeriod, WeekEnding, CostCentreCode, ProjectID, HomeDepartmentCode, ProductTypeID, ProducGroupID, HomeCostCentre, TaskID, TaskName, AccountsCategoryDescription, CRSValue, Capex, ChargeOutRate, Opex, FYID, FYPID, MECID, ForecastStatusID) VALUES (@EmployeeID, @EmployeeFirstName, @EmployeeSurname, @Effort, @ProductCode, @ApprovePeriod, @WeekEnding, @CostCentreCode, @ProjectID, @HomeDepartmentCode, @ProductTypeID, @ProducGroupID, @HomeCostCentre, @TaskID, @TaskName, @AccountsCategoryDescription, @CRSValue, @Capex, @ChargeOutRate, @Opex, @FYID, @FYPID, @MECID, @ForecastStatusID) SELECT 0 AS Result GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO '==================================================================== -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Darryl Collins Sent: Friday, 3 October 2008 1:40 PM To: 'Discussion concerning MS SQL Server' Subject: [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 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.