#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2006
    Posts
    11
    Rep Power
    0

    Question SQL 2008 Stored Procedure Date Format


    Hi,

    I have a strange data formatting issue that I can't get my head round.

    In SQL 2008 R2 I have built a really simple stored procedure that accepts two date parameters. See below.

    Here is the create/alter code for the procedure.



    Code:
    USE [database]
    GO
    /****** Object:  StoredProcedure [dbo].[TotalSalesThisMonth_Won]    Script Date: 08/02/2016 11:37:42 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[TotalSalesThisMonth_Won]
     @startdate datetime,
     @enddate datetime
    AS
    
    SELECT
          sum(soi.[Total]) as Total
      FROM [WiredContact_Site].[dbo].[wce_sage_sales_orders] so inner join wce_sage_sales_orders_items soi 
      ON so.SalesOrderID = soi.SalesOrderID 
      WHERE ((soi.EditTime BETWEEN @startdate AND @enddate)
      AND (status like '%Won%')
    Here is where I execute the procedure above and manually enter the start and end date params.

    I need to pass the dates in UK format dd/mm/yyyy like below but SQL will only accept mm/dd/yyyy which I think is US format. See the error below the SQL code:

    Code:
    USE [database]
    GO
    
    DECLARE	@return_value int
    
    EXEC	@return_value = [dbo].[TotalSalesThisMonth_Won]
    		@startdate = N'13/07/2016',
    		@enddate = N'13/07/2016'
    
    SELECT	'Return Value' = @return_value
    
    GO 
    
     Error:
    
     Msg 8114, Level 16, State 1, Procedure TotalSalesThisMonth_Won, Line 0
     Error converting data type nvarchar to datetime.
    
     (1 row(s) affected)
    Any advice on this would be really helpful. Many thanks.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2016
    Location
    United Kingdom
    Posts
    9
    Rep Power
    0
    Could be your regional settings. Try this one
    Code:
    EXEC	@return_value = [dbo].[TotalSalesThisMonth_Won]
    		@startdate = '20160713',
    		@enddate = '20160713'

IMN logo majestic logo threadwatch logo seochat tools logo