question

tombiernacki avatar image
tombiernacki asked

convert datetime error

Hi I have a stored procedure that is trying to pass datetime parameters @StartDate and @EndDate but when I run the query I get the following Error.... Arithmetic overflow error converting expression to data type datetime. Below is my Stored Procedure.... ALTER Procedure [dbo].[Test] @StartDate datetime, @EndDate datetime as begin set nocount on; -- exec test '01/01/2009','01/01/2020' SELECT DISTINCT F.EpisodeSys , F.ResidentSys , VR.DisplayName , F.OrgEntSys AS FacilityID , VO.FacilityName , VE.AdmissionDate , VE.DischargeDate FROM [D].dbo.FactLoadExceptions AS F INNER JOIN [B].dbo.VisionvwResidents AS VR ON F.ResidentSys = VR.EntitySys INNER JOIN [B].dbo.VisionvwOrganizations AS VO ON F.OrgEntSys = VO.OrgEntSys INNER JOIN [B].dbo.VisionEpisodes AS VE ON F.EpisodeSys = VE.EpisodeSys WHERE CensusDateKey >= @StartDate AND CensusDateKey <= @EndDate ORDER BY AdmissionDate end I should note that The CENSUSDATEKEY field is an INT type I need to convert it to Datetime type
stored-proceduresdatetime
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

So what values are in the CensusDateKey field?
0 Likes 0 ·
The CensusDateKey has int date values like 20090101
0 Likes 0 ·
eghetto avatar image
eghetto answered
... CAST(CAST(CensusDateKey AS CHAR(8)) AS DATETIME) >= @StartDate AND CAST(CAST(CensusDateKey AS CHAR(8)) AS DATETIME) <= @EndDate ... should fix it.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Awesome that worked perfectly
0 Likes 0 ·
Usman Butt avatar image
Usman Butt answered
You need to be sure that there is valid data in the CensusDateKey column. One way of doing it is to execute a query using ISDATE function, something like SELECT CensusDateKey FROM TABLENAME WHERE ISDATE(CONVERT(VARCHAR,CensusDateKey)) = 0 This will give you any invalid data. Not to mention, storing date data in other data types always gives pain.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.