INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL 2008 Stored Procedure Date Format

SQL 2008 Stored Procedure Date Format

(OP)
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 --> SQL

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 --> SQL

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.

RE: SQL 2008 Stored Procedure Date Format

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

RE: SQL 2008 Stored Procedure Date Format

(OP)
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 winky smile

RE: SQL 2008 Stored Procedure Date Format

Try:

CODE --> SQL

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

RE: SQL 2008 Stored Procedure Date Format

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

RE: SQL 2008 Stored Procedure Date Format

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:

CODE

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/da...

-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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close