November 12, 2015 at 11:16 am
Hi All,
I have a small test stored proc. I keep getting an error saying Error converting data type varchar to smalldatetime.
create procedure test
@TestDate smalldatetime
AS
if @TestDate > '06/06/2079'
select @TestDate = null
else
print 'test1'
when I do this test '12/01/2079'. I get an error "converting data type varchar to smalldatetime." How can I resolve this error.
any help will be greatly appreciated.
November 12, 2015 at 11:24 am
YOu need to make your parameter DATETIME, DATE, or DATETIME2 because you are getting the error trying to put 12/1/2079 into the parameter. If you aren't using the time portion I'd use DATE.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 12, 2015 at 11:27 am
Also, literal dates are best written in 'YYYYMMDD' format, eg, '20791201', to avoid any possible DMY, MDY confusion.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 12, 2015 at 11:33 am
I did that. Now I am getting an error saying:
The conversion of a datetime datatype to a smalldatetime resulted in a out of range value.
The testDate column in the table is smalldatetime, but I changed the parameter passed in to datetime
November 12, 2015 at 11:49 am
anjaliagarwal5 (11/12/2015)
I did that. Now I am getting an error saying:The conversion of a datetime datatype to a smalldatetime resulted in a out of range value.
The testDate column in the table is smalldatetime, but I changed the parameter passed in to datetime
Smalldatetime is only valid for the date range of January 1, 1900, through June 6, 2079.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
November 12, 2015 at 11:53 am
Can you post the real SP?
Here's an example I put together that works:
IF OBJECT_ID('tempdb.dbo.DateTest', 'U') IS NOT NULL
BEGIN
DROP TABLE dbo.DateTest;
END;
GO
CREATE TABLE dbo.DateTest
(
theDate SMALLDATETIME
);
GO
IF OBJECT_ID('tempdb.dbo.test', 'P') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.test;
END;
GO
CREATE PROCEDURE dbo.test
@TestDate SMALLDATETIME
AS
PRINT CONVERT(VARCHAR(20), @TestDate);
IF @TestDate > '06/06/2079'
PRINT 'Cannot put passed in date in table because it overflows the data type';
ELSE
INSERT INTO dbo.DateTest
(theDate)
VALUES
(@TestDate -- theDate - smalldatetime
);
GO
PRINT 'Running SP with valid smalldatetime value';
EXEC dbo.test @TestDate = '06/06/2079';
-- smalldatetime
PRINT 'Running SP with invalid smalldatetime value';
EXEC dbo.test @TestDate = '06/07/2079';
SELECT
*
FROM
dbo.DateTest AS DT;
GO
ALTER PROCEDURE dbo.test @TestDate DATETIME
AS
PRINT CONVERT(VARCHAR(20), @TestDate);
IF @TestDate > '06/06/2079'
PRINT 'Cannot put passed in date in table because it overflows the data type';
ELSE
INSERT INTO dbo.DateTest
(theDate)
VALUES
(@TestDate -- theDate - smalldatetime
);
GO
PRINT 'Running SP with valid smalldatetime value';
EXEC dbo.test @TestDate = '06/06/2079';
-- smalldatetime
PRINT 'Running SP with invalid smalldatetime value';
EXEC dbo.test @TestDate = '06/07/2079';
SELECT
*
FROM
dbo.DateTest AS DT;
GO
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 12, 2015 at 12:25 pm
Thank you for taking time to write a test stored procedure. There was something wrong with the stored procedure that was causing the error. Now it is resolved.
I really appreciate your help!!
November 15, 2015 at 11:11 pm
Update your table to flip-flop the month and day first, then the conversion should go through.
update YourTable
set Data = SUBSTRING(Data,4,3) + LEFT(Data,3) + RIGHT(Data,4)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply