Formatting Dates in SQL Server 2005

Hello,

I have an issue where i need to format dates in an SQL Server 2005 stored procedure where dates are currently being passed in as 'dd-MM-YYYY hh:mm:ss' and i think that i need them to be passed in as 'YYYY-MM-dd hh:mm:ss'

Just to provide a little background on what i am doing. I am currently using an assembly in SQL server to update records in exchange server. This assembly is linked to a stored procedure and i have another stored procedure passing values into the assembly linked SP by using a cursor to get values from a table.

Code example calling the Assembly SP is as follows:
_____________________________________________________

ALTER proc [dbo].[spExchangeAppUpdateDeleteInsert] as
-- =============================================
-- Declare and using a READ_ONLY cursor
-- =============================================
DECLARE WorkCursor CURSOR
READ_ONLY
FOR SELECT [ExchangeAppPendingId]
      ,[ExchangeId]
      ,[Subject]
      ,[Location]
        --,[StartDate]
        --,[EndDate]
        ,convert(nvarchar(19),[StartDate],120) as StartDate
        ,convert(nvarchar(19),[EndDate],120) as EndDate
        ,[MessageBody]
      ,[Action]
      ,[Completed]
  FROM [eSAY_MSCRM].[dbo].[tExchangeAppPendingActions]
  WHERE Completed is null
 

DECLARE @ExchangeAppPendingId int
DECLARE @ExchangeId uniqueidentifier
DECLARE @Subject varchar(100)
DECLARE @Location varchar(100)
DECLARE @StartDate Datetime
DECLARE @EndDate Datetime
DECLARE @MessageBody varchar(100)
DECLARE @Action varchar(40)
DECLARE @Completed bit

OPEN WorkCursor


FETCH NEXT FROM WorkCursor INTO @ExchangeAppPendingId,
      @ExchangeId,
      @Subject ,
      @Location ,
      @StartDate,
      @EndDate,
      @MessageBody,
      @Action ,
      @Completed


WHILE (@@fetch_status <> -1)
BEGIN
      IF (@@fetch_status <> -2)
      BEGIN
--            PRINT 'add user defined code here'
--            eg.
            DECLARE @message varchar(100)
            SELECT @message = 'Appointment is: ' + @Action
            PRINT @message
            PRINT @StartDate

--            If @Action = 'Update'
--            begin
--                  exec dbo.sp_UpdateExchApp @Subject, @Location, @StartDate, @EndDate, @MessageBody, @ExchangeId
--                  
--            end

            if @Action = 'Delete'
            begin
                  exec dbo.sp_DeleteExchApp @ExchangeId
            end

            if @Action = 'Insert'
            begin
                  Print @StartDate
                  select @MessageBody = [Description] from dbo.ActivityPointerBase where ActivityId = @ExchangeId
                  select @Location = Location from dbo.AppointmentBase where ActivityId = @ExchangeId
                  exec dbo.sp_AppCreate @Subject, @Location, @MessageBody, @StartDate, @EndDate

                  --exec dbo.sp_AppCreate 'Test', 'Tets','Test', '2007-10-30 20:00:00', '2007-10-30 20:30:00'
            end


      END

      update [eSAY_MSCRM].[dbo].[tExchangeAppPendingActions]
            set completed = 1
      where ExchangeAppPendingId =@ExchangeAppPendingId

      FETCH NEXT FROM WorkCursor INTO @ExchangeAppPendingId,
            @ExchangeId,
            @Subject ,
            @Location ,
            @StartDate,
            @EndDate,
            @MessageBody,
            @Action ,
            @Completed
END

CLOSE WorkCursor
DEALLOCATE WorkCursor
________________________________________________________

When i try to execute the assembly manually (commented out line with static values) the assembly works fine. But when i try to get the assembly to update from the sp passing in the values from the table i get an error.
'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.'
and the format of the dates in the table are 'dd-MM-yyyy hh:mm:ss'.
As you can also see from the commented out lines at the select part of the SP i have tried the CONVERT function with no success.

So can sombody help me out.

PS, Let me know if i need to clear any of this up.

Thanks in advanced.


Start Free Trial