February 13, 2013 at 4:58 am
Query below has just started giving overflow error.
/** This script changes the Visit details for visits that have been entered for Sub-Contractors or when the details have been entered on the PDA after the event.
The script can change any of the following Visit date, Visit Reference Number, Inspector Name and RequiredByDate
The options have to be chosen manually. **/
USE [IdealOffice_NSI_Live]
GO
DECLARE @RollNumber Integer, @PDADate nVarChar(50), @Requiredby nvarChar(50), @UpdateDate1 DateTime, @UpdateDate2 DateTime,
@VisitDate nvarchar(10), @BookedDate nvarchar(30) ,@StatusUpdateDate nvarchar(30), @NewRefNum nvarchar(5),@OriginalRefNum nvarchar(5),
@ID uniqueidentifier, @status UniqueIdentifier, @InspectorID smallint, @InspectorFirstName nvarChar(20), @InspectorLastName nvarChar(30), @Duration int
-- Original Visit Details
-- Set RollNumber to the Approval Roll Number for visit
SET @RollNumber = 1856
-- Set PDADate to the date entered on PDA dd/mm/yyyy format
SET @PDADate = '13/12/2012'
--Set OriginalRefNum to reflect the original visit reference number
SET @OriginalRefNum = '02'
--New Visit details
--Set VisitDate to the correct visit date dd/mm/yyyy format
SET @VisitDate = '04/12/2012'
--Set RefNum to reflect the correct Visit number
SET @NewRefNum = '02'
-- Set Duration (hours) if duration incorrect otherwise comment out the [Duration] column below.
SET @Duration = 24
--Set first name and last name of inspector. If the Inspector name remains unchanged then comment out
-- the [Inspector] and [UserCreated] columns in the Visit and Results queries below.
SET @InspectorFirstName = 'Peter'
SET @InspectorLastName = 'Herbert'
--Only needs to be changed if Inspector has entered an incorrect date otherwise comment out the
-- [RequiredBy] column in the query below.
SET @RequiredBy = '31/12/2011'
-- Converts the PDA date to a datetime value
SET @UpdateDate1 = Cast(@PDADate + ' 00:00:00' as DateTime)
-- Adds 24 hours to PDA date
SET @UpdateDate2 = DateAdd(hour,23, @UpdateDate1)
SET @UpdateDate2 = DateAdd(minute,59,@UpdateDate2)
-- Corrects the format of BookedDate and StatusUpdateDate
SET @BookedDate = @Visitdate + ' 00:00:00'
SET @StatusUpdateDate = @VisitDate + ' 17:00:00:00'
-- Gets the status GUID for a complated visit
SET @status = (SELECT [ApprovalVisitStatusID] FROM [dbo].[tlkpApprovalVisitStatus] WHERE [Name] = 'Completed')
--Gets the visit ID for the actual visit
SET @ID = (SELECT [ApprovalVisitId] FROM [dbo].[tblApprovalVisit]
WHERE [ApprovalRollNumber] = @RollNumber
AND [ApprovalVisitStatusUpdateDate] BETWEEN @UpdateDate1 AND @UpdateDate2
AND [ApprovalVisitStatusID] = @status AND [NumberReference] = @OriginalRefNum)
--Get PersonnelID for SubContractor
SET @InspectorID = 0
SET @InspectorID = (SELECT [personnelID] FROM dbo.[tblPersonnel]
WHERE FirstName = @InspectorFirstName AND LastName = @InspectorLastName)
--Main update Query
IF @InspectorID <> 0
BEGIN
--Update the visit detail
UPDATE [dbo].[tblApprovalVisit]
SET[VisitEndTime] = CAST( @VisitDate + ' 17:00:00.000' as datetime)
,[VisitStartTime] = CAST(@VisitDate + ' 09:00:00.000' as datetime)
,[NumberReference] = @NewRefNum
,[approvalVisitStatusUpdateDate] = CAST(@StatusUpdateDate as datetime)
,[BookedDate] = CAST(@BookedDate as datetime)
--,[Inspector] = @InspectorID --Comment Line out if Inspector does not need updating.
--,[RequiredBy] = CAST(@RequiredBy + ' 00:00:00.000' AS Datetime)
--,[Duration] = @Duration
,[UserUpdated] = 101
--,[ApprovalVisitStatusID] = @status
WHERE [ApprovalVisitID] = @ID
--Update the result detail
UPDATE [dbo].[tblApprovalVisitXmlResult]
SET [DateCreated] = @BookedDate
--,[UserCreated] = @InspectorID --Comment out if not needed. This is only required when the Inspector's name is incorrect.
,[DateUpdated] = @StatusUpdateDate
,[UserUpdated] = 101
WHERE ApprovalVisitID = @ID
--Selects the visit that has been updated.
SELECT * FROM [dbo].[tblApprovalVisit]
WHERE [ApprovalVisitID] = @ID
END
ELSE
BEGIN
Print 'Inspector Name could not be found'
END
February 13, 2013 at 5:07 am
Random guess: -
USE [IdealOffice_NSI_Live]
GO
DECLARE @RollNumber INT, @PDADate NVARCHAR(50), @Requiredby NVARCHAR(50), @UpdateDate1 DATETIME, @UpdateDate2 DATETIME,
@VisitDate NVARCHAR(10), @BookedDate NVARCHAR(30), @StatusUpdateDate NVARCHAR(30), @NewRefNum NVARCHAR(5), @OriginalRefNum NVARCHAR(5),
@ID UNIQUEIDENTIFIER, @status UNIQUEIDENTIFIER, @InspectorID SMALLINT, @InspectorFirstName NVARCHAR(20), @InspectorLastName NVARCHAR(30),
@Duration INT;
-- Original Visit Details
-- Set RollNumber to the Approval Roll Number for visit
SET @RollNumber = 1856;
-- Set PDADate to the date entered on PDA YYYY-MM-DD format
SET @PDADate = '2012-12-13';
--Set OriginalRefNum to reflect the original visit reference number
SET @OriginalRefNum = '02';
--New Visit details
--Set VisitDate to the correct visit date YYYY-MM-DD format
SET @VisitDate = '2012-12-04';
--Set RefNum to reflect the correct Visit number
SET @NewRefNum = '02';
-- Set Duration (hours) if duration incorrect otherwise comment out the [Duration] column below.
SET @Duration = 24;
--Set first name and last name of inspector. If the Inspector name remains unchanged then comment out
-- the [Inspector] and [UserCreated] columns in the Visit and Results queries below.
SET @InspectorFirstName = 'Peter';
SET @InspectorLastName = 'Herbert';
--Only needs to be changed if Inspector has entered an incorrect date otherwise comment out the
-- [RequiredBy] column in the query below.
SET @RequiredBy = '2011-12-31';
-- Converts the PDA date to a datetime value
SET @UpdateDate1 = Cast(@PDADate + ' 00:00:00' AS DATETIME);
-- Adds 24 hours to PDA date
SET @UpdateDate2 = DateAdd(hour, 23, @UpdateDate1);
SET @UpdateDate2 = DateAdd(minute, 59, @UpdateDate2);
-- Corrects the format of BookedDate and StatusUpdateDate
SET @BookedDate = @Visitdate + ' 00:00:00';
SET @StatusUpdateDate = @VisitDate + ' 17:00:00:00';
-- Gets the status GUID for a complated visit
SET @status = (
SELECT [ApprovalVisitStatusID]
FROM [dbo].[tlkpApprovalVisitStatus]
WHERE [Name] = 'Completed'
);
--Gets the visit ID for the actual visit
SET @ID = (
SELECT [ApprovalVisitId]
FROM [dbo].[tblApprovalVisit]
WHERE [ApprovalRollNumber] = @RollNumber
AND [ApprovalVisitStatusUpdateDate] BETWEEN @UpdateDate1
AND @UpdateDate2
AND [ApprovalVisitStatusID] = @status
AND [NumberReference] = @OriginalRefNum
);
--Get PersonnelID for SubContractor
SET @InspectorID = 0;
SET @InspectorID = (
SELECT [personnelID]
FROM dbo.[tblPersonnel]
WHERE FirstName = @InspectorFirstName
AND LastName = @InspectorLastName
);
--Main update Query
IF @InspectorID <> 0
BEGIN
--Update the visit detail
UPDATE [dbo].[tblApprovalVisit]
SET [VisitEndTime] = CAST(@VisitDate + ' 17:00:00.000' AS DATETIME),
[VisitStartTime] = CAST(@VisitDate + ' 09:00:00.000' AS DATETIME),
[NumberReference] = @NewRefNum,
[approvalVisitStatusUpdateDate] = CAST(@StatusUpdateDate AS DATETIME),
[BookedDate] = CAST(@BookedDate AS DATETIME)
--,[Inspector] = @InspectorID --Comment Line out if Inspector does not need updating.
--,[RequiredBy] = CAST(@RequiredBy + ' 00:00:00.000' AS Datetime)
--,[Duration] = @Duration
, [UserUpdated] = 101
--,[ApprovalVisitStatusID] = @status
WHERE [ApprovalVisitID] = @ID;
--Update the result detail
UPDATE [dbo].[tblApprovalVisitXmlResult]
SET [DateCreated] = @BookedDate
--,[UserCreated] = @InspectorID --Comment out if not needed. This is only required when the Inspector's name is incorrect.
, [DateUpdated] = @StatusUpdateDate, [UserUpdated] = 101
WHERE ApprovalVisitID = @ID;
--Selects the visit that has been updated.
SELECT *
FROM [dbo].[tblApprovalVisit]
WHERE [ApprovalVisitID] = @ID;
END;
ELSE
BEGIN
PRINT 'Inspector Name could not be found';
END;
February 13, 2013 at 5:25 am
Thanks but can not see what change is
February 13, 2013 at 5:41 am
As your dates are in DMY format, you need to ensure that the connection is set to use dates in DMY format or put an implicit change in the procedure to do SET DATEFORMAT DMY
I am guessing its MDY format, so your "SET @UpdateDate1 = Cast(@PDADate + ' 00:00:00' as DateTime)" line is trying to say the 12th day of the 13th month 2012 which is not a valid date causing the overflow.
Craig's solution is using ISO date formats YYYY-MM-DD which will stop this implicit conversion from DMY MDY from happening, which is how you should be handling dates if you can.
February 13, 2013 at 6:20 am
anthony.green (2/13/2013)
As your dates are in DMY format, you need to ensure that the connection is set to use dates in DMY format or put an implicit change in the procedure to do SET DATEFORMAT DMYI am guessing its MDY format, so your "SET @UpdateDate1 = Cast(@PDADate + ' 00:00:00' as DateTime)" line is trying to say the 12th day of the 13th month 2012 which is not a valid date causing the overflow.
Craig's solution is using ISO date formats YYYY-MM-DD which will stop this implicit conversion from DMY MDY from happening, which is how you should be handling dates if you can.
100 points! Thanks for the explanation, I nipped out for lunch straight after posting and only just got back
February 13, 2013 at 6:56 am
Or just use a language-neutral form, that is always interpreted as ymd, eg:
set @VisitDate=N'20131225'
February 13, 2013 at 7:07 am
Thanks everyone, problem has mysteriously rectified itself!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply