Need help in Date Parameter Format in a procedure
-
Hi team,
Could you help me to resolve the issue in my Procedure. it looks fine . But if i change the input parameter format, i am not getting desire result or error.
please find the table info
CREATE TABLE dbo.machineinfo
( machine_id INT NOT NULL,
machine_name VARCHAR(50) NOT NULL,
timeperiod DATE NOT NULL
);
begin
INSERT INTO dbo.machineinfo (machine_id,machine_name,timeperiod) VALUES (56457,'GPRWXY3','2016-06-03')
INSERT INTO dbo.machineinfo (machine_id,machine_name,timeperiod) VALUES (56458,'SAIBY0','2016-06-03')
INSERT INTO dbo.machineinfo (machine_id,machine_name,timeperiod) VALUES (56457,'AIIWXY6','2016-06-01')
INSERT INTO dbo.machineinfo (machine_id,machine_name,timeperiod) VALUES (56458,'TRYYUR3','2016-06-12')
INSERT INTO dbo.machineinfo (machine_id,machine_name,timeperiod) VALUES (56459,'CTREHJ3','2016-06-12')
end
My Procedure
CREATE PROCEDURE [dbo].[testingpurpose]
@StartDate_in date,
@EndDate_in date
AS
BEGIN TRY
SET NOCOUNT ON;
DECLARE @EXISTS int,
@loadcheck date,
@loadcheck1 date
IF DATEPART(dd, @EndDate_in) <= 15
BEGIN
PRINT 'MID MONTHLY'
SET @loadcheck = DATEADD(wk, DATEDIFF(wk, 0, @EndDate_in) - 0, -1)
PRINT 'Check Latest Sunday data in machineinfo table ' + CONVERT(char(23), @loadcheck, 121)
SET @loadcheck1 = DATEADD(wk, DATEDIFF(wk, 0, @EndDate_in) - 1, -1) --last-previous sunday
PRINT 'Check previous of latest Sunday data in machineinfo table ' + CONVERT(char(23), @loadcheck1, 121)
IF EXISTS (SELECT timeperiod
FROM dbo.machineinfo
WHERE timeperiod BETWEEN @loadcheck1 AND @loadcheck)
BEGIN
SET @Exists = 1
END
ELSE
BEGIN
SET @Exists = 0
END
RETURN @Exists
END
END TRY
BEGIN CATCH
Print ('Error')
END CATCH;
GO
when i execute in DDMONYYYY format every thing looks good and i got desire result.
DECLARE@return_value int
EXEC@return_value = [dbo].[testingpurpose]
@StartDate_in = '01-JUN-2016',
@EndDate_in = '12-JUN-2016'
SELECT'Return Value' = @return_value
Go
but if a change the date format i got error
DECLARE@return_value int
EXEC@return_value = [dbo].[testingpurpose]
@StartDate_in = '01-JUN-2016',
@EndDate_in = '14-06-2016'
SELECT'Return Value' = @return_value
Go
Msg 8114, Level 16, State 1, Procedure testingpurpose, Line 0
Error converting data type varchar to date.
(1 row(s) affected)
below throws wrong answer
DECLARE@return_value int
EXEC@return_value = [dbo].[testingpurpose]
@StartDate_in = '01-JUN-2016',
@EndDate_in = '12-06-2016'
SELECT'Return Value' = @return_value
Go
MID MONTHLY
Check Latest Sunday data in machineinfo table 2016-12-04
Check previous of latest Sunday data in machineinfo table 2016-11-27
(1 row(s) affected)
It looks like date format issue. but i am not able to resolve it. please help.
Torrid
-
Quick suggestion, use the CONVERT (link to BOL) function to explicitly convert the string values to the datetime data type rather than depending on any implicit conversion
-
torrid.inundate (10/28/2016)
<snip>...but if a change the date format i got errorDECLARE@return_value int
EXEC@return_value = [dbo].[testingpurpose]
@StartDate_in = '01-JUN-2016',
@EndDate_in = '14-06-2016'
SELECT'Return Value' = @return_value
Go
Msg 8114, Level 16, State 1, Procedure testingpurpose, Line 0
Error converting data type varchar to date.
(1 row(s) affected)
below throws wrong answer
DECLARE@return_value int
EXEC@return_value = [dbo].[testingpurpose]
@StartDate_in = '01-JUN-2016',
@EndDate_in = '12-06-2016'
SELECT'Return Value' = @return_value
Go
MID MONTHLY
Check Latest Sunday data in machineinfo table 2016-12-04
Check previous of latest Sunday data in machineinfo table 2016-11-27
(1 row(s) affected)
It looks like date format issue. but i am not able to resolve it. please help.
Torrid
If you pass the date value as a string in a parameter you'll need to use an unambiguous date format (yyyy-MM-ddTHH24:mi:ss or yyyyMMdd HH24:mi:ss). In your code the value "12-06-2016" is implicitly converted to "December 6th 2016". The value "14-06-2016" cannot be converted because a year only have 12 months, resulting in the error message.
** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! ** -
Thank you Eirikur Eiriksson/ HanShi
I got your point. is there any way to raise an exception ,if the given input parameter format is not DDMONYYYY.
Torrid.
-
Still i failed to get desire result.
/*
The following example displays the current date and time, uses CAST to change the current date and time to a character data type, and then uses CONVERT display the date and time in the ISO 8901 format.
*/
declare @dt date
set @dt = '17-07-2016'
SELECT
@dt AS UnconvertedDateTime,
CAST(@dt AS nvarchar(30)) AS UsingCast,
CONVERT(nvarchar(30), @dt, 103) AS UsingConvertTo_ISO8601
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.
-
torrid.inundate (10/28/2016)
Still i failed to get desire result./*
The following example displays the current date and time, uses CAST to change the current date and time to a character data type, and then uses CONVERT display the date and time in the ISO 8901 format.
*/
declare @dt date
set @dt = '17-07-2016'
SELECT
@dt AS UnconvertedDateTime,
CAST(@dt AS nvarchar(30)) AS UsingCast,
CONVERT(nvarchar(30), @dt, 103) AS UsingConvertTo_ISO8601
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.
You are getting an error on the initial variable assignment.
BTW: you are using the wrong format, 103 is British/French (dd/mm/yyyy), change this to 126 for ISO8601
declare @dt dateset @dt = CONVERT(DATE,'17-07-2016',105)SELECT@dt AS UnconvertedDateTime,CAST(@dt AS nvarchar(30)) AS UsingCast,CONVERT(nvarchar(30), @dt, 126) AS UsingConvertTo_ISO8601 ; -
Thanks for your valuable suggestion.
I am using MS SQL Server Management Studio, but i am unable to implement the suggested solution in the existing procedure, would it be possible for you to amend the changes to StoredProcedure and provide?
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply