April 5, 2012 at 12:49 pm
So I was moving my DB from 2008 to 2012, and while restoring patches of our software, I came across this issue in one of the stored procs.
DATE + TIME Add works good in SQL2008R2 Fails in SQL2012RC0
--WORKS IN 2008R2 ,2012
DECLARE @ESTDate DATE = '01/01/2012',
@ESTime TIME(7) = '01:00 PM'
SELECT CONVERT(DATETIME, @ESTDate, ( 108 ))+ @ESTime
--WORKS IN 2008R2 (
DECLARE @ESTRecords TABLE
(
ESTime TIME(7) NOT NULL,
ESTDate DATE NOT NULL,
ESTDateTime AS ( CONVERT(DATETIME, ESTDate, ( 108 )) + ESTime ) PERSISTED
)
INSERT INTO @ESTRecords (ESTime,ESTDate)
VALUES( '12:00 PM','12/31/2012')
SELECT * FROM @ESTRecords
--DOESNOT WORK IN 2012RC0 (
DECLARE @ESTRecords TABLE
(
ESTime TIME(7) NOT NULL,
ESTDate DATE NOT NULL,
ESTDateTime AS ( CONVERT(DATETIME, ESTDate, ( 108 )) + ESTime ) PERSISTED
)
INSERT INTO @ESTRecords (ESTime,ESTDate)
VALUES( '12:00 PM','12/31/2012')
SELECT * FROM @ESTRecords
Any Inputs? Suggestions. Is this happening to others as well?
April 5, 2012 at 12:52 pm
Error messages?
April 5, 2012 at 1:26 pm
Lynn Pettis (4/5/2012)
Error messages?
Msg 402, Level 16, State 1, Line 6
The data types datetime and time are incompatible in the add operator.
April 5, 2012 at 1:36 pm
Relying on implicit conversion is never a good idea.
Either convert the time to datetime then add (which is still replying on implied behaviour), or use dateadd and datediff.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 5, 2012 at 1:39 pm
yes, change your code a bit to do as Gail suggests.
DECLARE @ESTRecords TABLE
(
ESTime TIME(7) NOT NULL,
ESTDate DATE NOT NULL,
ESTDateTime AS ( CONVERT(DATETIME, ESTDate, ( 108 )) + convert(datetime,ESTime ))
)
INSERT INTO @ESTRecords (ESTime,ESTDate)
VALUES( '12:00 PM','12/31/2012')
SELECT * FROM @ESTRecords
April 5, 2012 at 1:41 pm
JackIntheBox (4/5/2012)
So I was moving my DB from 2008 to 2012, and while restoring patches of our software, I came across this issue in one of the stored procs.DATE + TIME Add works good in SQL2008R2 Fails in SQL2012RC0
--WORKS IN 2008R2 ,2012
DECLARE @ESTDate DATE = '01/01/2012',
@ESTime TIME(7) = '01:00 PM'
SELECT CONVERT(DATETIME, @ESTDate, ( 108 ))+ @ESTime
--WORKS IN 2008R2 (
DECLARE @ESTRecords TABLE
(
ESTime TIME(7) NOT NULL,
ESTDate DATE NOT NULL,
ESTDateTime AS ( CONVERT(DATETIME, ESTDate, ( 108 )) + ESTime ) PERSISTED
)
INSERT INTO @ESTRecords (ESTime,ESTDate)
VALUES( '12:00 PM','12/31/2012')
SELECT * FROM @ESTRecords
--DOESNOT WORK IN 2012RC0 (
DECLARE @ESTRecords TABLE
(
ESTime TIME(7) NOT NULL,
ESTDate DATE NOT NULL,
ESTDateTime AS ( CONVERT(DATETIME, ESTDate, ( 108 )) + ESTime ) PERSISTED
)
INSERT INTO @ESTRecords (ESTime,ESTDate)
VALUES( '12:00 PM','12/31/2012')
SELECT * FROM @ESTRecords
Any Inputs? Suggestions. Is this happening to others as well?
Try this:
DECLARE @ESTRecords TABLE
(
ESTime TIME(7) NOT NULL,
ESTDate DATE NOT NULL,
ESTDateTime AS ( CONVERT(DATETIME, ESTDate, ( 108 )) + CONVERT(DATETIME, ESTime, 114) ) PERSISTED
)
INSERT INTO @ESTRecords (ESTime,ESTDate)
VALUES( '12:00 PM','12/31/2012')
SELECT * FROM @ESTRecords
April 5, 2012 at 2:34 pm
Yeah you guys are right; Our dev's coded that and now since I was playing around with 2012 instance, I have to fix their codes.
Hopefully it is just in one of the procs..
I was wondering because I wasn't sure if this issue was specific to me.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply