Solved

SQL Server Procedure with dates

Posted on 2016-08-26
6
62 Views
Last Modified: 2016-08-31
Hello Expert Exchange
I have written a store procedure for SQL Server, but every time I try to run it I get the error message of;
Msg 241, Level 16, State 1, Procedure pTest, Line 79
Conversion failed when converting date and/or time from character string.

This is my procedure;
CREATE PROCEDURE [dbo].[pTest] 
(
@Start_Date_Format DATETIME= '2016-03-06', 
  @End_Date_Format DATETIME= '2016-03-19'
)
AS
DECLARE @SQL NVARCHAR(4000)
DECLARE @FinalSQL NVARCHAR(4000)
SET @SQL='Select Zip, Sum(orig_qty) as orig_qty
			from stk_transfer
			inner join data_location dl1 on stk_transfer.desp_loc_id = dl1.id
			inner join data_location dl2 on stk_transfer.del_loc_id = dl2.id
			inner join address on dl2.address_id = address.id
			where received_date between '+ @Start_Date_Format + ' and ' +@End_Date_Format + '
			and desp_loc_id = 101
			and del_loc_id <> 8
			Group by Zip'
SET @FinalSQL = 'Select B.[GeoLocation],A.zip,A.orig_qty
From OPENQUERY([MYSQLCYBERTILL], ' + '''' + @SQL + '''' + ') as A
inner join [dbo].[PostcodeUKAll] as B on A.Zip = B.Postcode' 
PRINT @FinalSQL
EXEC(@FinalSQL)
GO

Open in new window


This is how I am trying to run it;
EXEC pTest @Start_Date_Format = '2016-05-01 00:00:00', 
  @End_Date_Format = '2016-08-26 23:59:59'

Open in new window


Can anyone see the problem with my procedure?

Regards

SQLSearcher
0
Comment
Question by:SQLSearcher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 41771486
yes, you are assuming implicit data type conversion...
https://www.experts-exchange.com/articles/1499/DATE-and-TIME-don't-be-scared-and-do-it-right-the-first-time.html
on this line:
where received_date between '+ @Start_Date_Format + ' and ' +@End_Date_Format + '

it should be:
where received_date between convert(datetime, '''+ convert(varchar(10), @Start_Date_Format , 120) + ''', 120)  and convert(datetime, '''+ convert(varchar(10), @End_Date_Format , 120) + ''', 120)
0
 

Author Comment

by:SQLSearcher
ID: 41771501
Hi Guy
I made the change to the procedure;
CREATE PROCEDURE [dbo].[pTest] 
(
@Start_Date_Format DATETIME= '2016-03-06', 
  @End_Date_Format DATETIME= '2016-03-19'
)
AS
DECLARE @SQL NVARCHAR(4000)
DECLARE @FinalSQL NVARCHAR(4000)
SET @SQL='Select Zip, Sum(orig_qty) as orig_qty
			from stk_transfer
			inner join data_location dl1 on stk_transfer.desp_loc_id = dl1.id
			inner join data_location dl2 on stk_transfer.del_loc_id = dl2.id
			inner join address on dl2.address_id = address.id
			where where received_date between convert(datetime, '''+ convert(varchar(10), @Start_Date_Format , 120) + ''', 120)  and convert(datetime, '''+ convert(varchar(10), @End_Date_Format , 120) + ''', 120) 
			and desp_loc_id = 101
			and del_loc_id <> 8
			Group by Zip'
SET @FinalSQL = 'Select B.[GeoLocation],A.zip,A.orig_qty
From OPENQUERY([MYSQLCYBERTILL], ' + '''' + @SQL + '''' + ') as A
inner join [dbo].[PostcodeUKAll] as B on A.Zip = B.Postcode' 
PRINT @FinalSQL
EXEC(@FinalSQL)
GO

Open in new window


But now I get this message.

Select B.[GeoLocation],A.zip,A.orig_qty
From OPENQUERY([MYSQLCYBERTILL], 'Select Zip, Sum(orig_qty) as orig_qty
                  from stk_transfer
                  inner join data_location dl1 on stk_transfer.desp_loc_id = dl1.id
                  inner join data_location dl2 on stk_transfer.del_loc_id = dl2.id
                  inner join address on dl2.address_id = address.id
                  where where received_date between convert(datetime, '2016-05-01', 120)  and convert(datetime, '2016-08-26', 120)
                  and desp_loc_id = 101
                  and del_loc_id <> 8
                  Group by Zip') as A
inner join [dbo].[PostcodeUKAll] as B on A.Zip = B.Postcode
Msg 102, Level 15, State 1, Line 77
Incorrect syntax near '2016'.

What do I need to do?

Regards

SQLSearcher
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 41771506
this is because of the "quoting hell" ... you have quotes in the @sql , which need to be "doubled"

do this:
SET @FinalSQL = 'Select B.[GeoLocation],A.zip,A.orig_qty
From OPENQUERY([MYSQLCYBERTILL], ' + '''' + replace(@SQL, '''', '''''') + '''' + ') as A
inner join [dbo].[PostcodeUKAll] as B on A.Zip = B.Postcode' 

Open in new window

0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

Author Comment

by:SQLSearcher
ID: 41771567
Hello Guy
I'm still having a nightmare with the singe quote's.

Here is the error message;
Select B.[GeoLocation],A.zip,A.orig_qty
From OPENQUERY([MYSQLCYBERTILL], 'Select Zip, Sum(orig_qty) as orig_qty
                  from stk_transfer
                  inner join data_location dl1 on stk_transfer.desp_loc_id = dl1.id
                  inner join data_location dl2 on stk_transfer.del_loc_id = dl2.id
                  inner join address on dl2.address_id = address.id
                  where received_date between convert(datetime, ''2016-05-01'', 120)
                  and convert(datetime, ''2016-08-26'', 120)
                  Group by Zip') as A
inner join [dbo].[PostcodeUKAll] as B on A.Zip = B.Postcode
OLE DB provider "MSDASQL" for linked server "MYSQLCYBERTILL" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.5.38-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2016-05-01', 120)
                  and convert(datetime, '2016-08-26', 120)
                  Group by Zip' at line 6".
Msg 7350, Level 16, State 2, Line 70
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "MYSQLCYBERTILL".

Here is the store procedure;
CREATE PROCEDURE [dbo].[pTest] 
(
@Start_Date_Format DATETIME= '2016-03-06', 
  @End_Date_Format DATETIME= '2016-03-19'
)
AS
DECLARE @SQL NVARCHAR(4000)
DECLARE @FinalSQL NVARCHAR(4000)
SET @SQL='Select Zip, Sum(orig_qty) as orig_qty
			from stk_transfer
			inner join data_location dl1 on stk_transfer.desp_loc_id = dl1.id
			inner join data_location dl2 on stk_transfer.del_loc_id = dl2.id
			inner join address on dl2.address_id = address.id
			where received_date between convert(datetime, '''+ convert(varchar(10), @Start_Date_Format , 120) + ''', 120) 
			and convert(datetime, '''+ convert(varchar(10), @End_Date_Format , 120) + ''', 120) 
			Group by Zip'
SET @FinalSQL = 'Select B.[GeoLocation],A.zip,A.orig_qty
From OPENQUERY([MYSQLCYBERTILL], ' + '''' + replace(@SQL, '''', '''''') + '''' + ') as A
inner join [dbo].[PostcodeUKAll] as B on A.Zip = B.Postcode'
PRINT @FinalSQL
EXEC(@FinalSQL)
GO

Open in new window


Can you see anything?

Regards

SQLSearcher
0
 

Accepted Solution

by:
SQLSearcher earned 0 total points
ID: 41771726
Hello Guy
I found the problem, it was with the convert of the date type in MySQL.

So I finished with this;

CREATE PROCEDURE [dbo].[pTest] 
(
@Start_Date_Format DATETIME= '2016-03-06', 
  @End_Date_Format DATETIME= '2016-03-19'
)
AS
DECLARE @SQL NVARCHAR(4000)
DECLARE @FinalSQL NVARCHAR(4000)
SET @SQL='Select Zip, Sum(orig_qty) as orig_qty
			from stk_transfer
			inner join data_location dl1 on stk_transfer.desp_loc_id = dl1.id
			inner join data_location dl2 on stk_transfer.del_loc_id = dl2.id
			inner join address on dl2.address_id = address.id
			where received_date between DATE_FORMAT('''+ convert(varchar(10), @Start_Date_Format , 120) + ''',''%Y-%m-%d %h:%i:%s'')
			and DATE_FORMAT('''+ convert(varchar(10), @End_Date_Format , 120) + ''',''%Y-%m-%d %h:%i:%s'')
			and desp_loc_id = 101
			and del_loc_id <> 8 
			Group by Zip'
SET @FinalSQL = 'Select B.[GeoLocation],A.zip,A.orig_qty
From OPENQUERY([MYSQLCYBERTILL], ' + '''' + replace(@SQL, '''', '''''') + '''' + ') as A
inner join [dbo].[PostcodeUKAll] as B on A.Zip = B.Postcode'
PRINT @FinalSQL
EXEC(@FinalSQL)
GO

Open in new window

0
 

Author Closing Comment

by:SQLSearcher
ID: 41777760
Thank you very much for your help.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question