Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Server Procedure with dates

Posted on 2016-08-26
6
Medium Priority
?
90 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
  • 4
  • 2
6 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 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 2000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

578 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