Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server Procedure with dates

Posted on 2016-08-26
6
Medium Priority
?
81 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 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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

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!

Question has a verified solution.

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

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…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.

610 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