SQL 2008 Stored Procedure Date Format 1

[craigward]

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.

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:

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.

 

[kjv1611]

I haven't tested it, but have you tried the same but without the N? So @startdate = '13/07/2016'?

You don't need the leading N. You're telling SQL to convert a varchar to nvarchar instead of letting it "do it's thing" and convert the varchar to datetime.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[craigward]

Hi, thanks for the reply. I have tried that and still have the same issue. I'm sure it will end up being something simple ;-)

 

[micang]

Try:

EXEC	@return_value = [dbo].[TotalSalesThisMonth_Won]
		@startdate = '2016-07-13',
		@enddate = '2016-07-13'

 

[craigward]

Thank you micang, that did the trick it is now working!

 

[gmmastros]

SQL Server will interpret your dates based on the language settings of the user logged in. When passing dates as parameters to stored procedures, it's best to use an un-ambiguous date format like "YYYYMMDD" Ex:

EXEC	@return_value = [dbo].[TotalSalesThisMonth_Won]
		@startdate = '20160713',
		@enddate = '20160713'

I blogged about this a while ago. You can find the blog here:

http://blogs.lessthandot.com/index.php/datamgmt/datadesign/setting-a-standard-dateformat-for-sql-se/

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[craigward]

Thank you gmmastros very useful.

http://www.tek-tips.com/