Solved

SQL Server Procedure with dates

Posted on 2016-08-26
6
33 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now