Solved

SQL Server Procedure with dates

Posted on 2016-08-26
6
41 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 142

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 142

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

776 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