Convert datetime error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Convert datetime error

Author

Topic

zero1de
Posting Yak Master

105 Posts

Posted - 2014-06-25 : 14:33:21

Hi,

I need help with converting.
The View in himself functions so far, as soon as I would like to carry out a simply calculation, on the View i get a
what is wrong in my code ?

Error message.
SQL Server Database Error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

View:
ALTER VIEW [dbo].[MA_SumLohn_an]
AS
SELECT PERSTAMM.PSPERSNR AS Personalnr,
PERSTAMM.PSNR,
PERSTAMM.PSVORNA AS Vorname,
PERSTAMM.PSNACHNA AS Nachname,
'0' AS [Summe Lohnarten]
FROM dbo.BUCHEINZ AS BUCHEINZ
INNER JOIN
dbo.PERSTAMM AS PERSTAMM
ON BUCHEINZ.PSNR = PERSTAMM.PSNR
AND BUCHEINZ.FIRMA = PERSTAMM.FIRMA
WHERE (BUCHEINZ.FIRMA = 1)
AND (convert(datetime,convert(varchar(8),BUCHEINZ.BEDATE))
BETWEEN DateAdd(Month,DateDiff(Month,0,getdate())-1,0)
AND DateAdd(dd,-1,DateAdd(Month,DateDiff(Month,0,getdate()),0)))
AND (BUCHEINZ.PSNR NOT IN (SELECT PSNR FROM dbo.MA_SumLohn_abw))
AND (PERSTAMM.PSNR > 2)
AND (BUCHEINZ.LOANR NOT IN
('K', 'KO', 'KStd', 'KoA', 'KUR'))
GROUP BY PERSTAMM.PSPERSNR,
PERSTAMM.PSNR,
PERSTAMM.PSVORNA,
PERSTAMM.PSNACHNA,
PERSTAMM.PSPERSNR

Select which produce the error:

SELECT MA_ZUSATZFELD.Cardnr,
MA_SumLohn_an.Personalnr,
MA_SumLohn_an.PSNR,
MA_SumLohn_an.Vorname,
MA_SumLohn_an.Nachname,
MA_SumLohn_an.[Summe Lohnarten],
MA_ZUSATZFELD.KSTNR,
MA_ZUSATZFELD.FTE
-- CAST (replace (MA_ZUSATZFELD.FTE, ',', '.') AS float) * 44 AS Prämie
FROM Tisoware.dbo.MA_SumLohn_an MA_SumLohn_an
INNER JOIN Tisoware.dbo.MA_ZUSATZFELD MA_ZUSATZFELD
ON (MA_SumLohn_an.PSNR = MA_ZUSATZFELD.PSNR)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-25 : 15:13:56

Is MA_SumLohn_an.PSNR or MA_ZUSATZFELD.PSNR of data type DATETIME? If one is and the other is not, this can happen. I don't see anything else that could cause a problem given that if you select from the view that works correctly. If that were not the case, I would also look at what is in the column BUCHEINZ.BEDATE. Is it a character data type or is it a datetime data type? If it is character data type, that may be causing the problem when you are trying to convert that to a datetime type in the view.

zero1de
Posting Yak Master

105 Posts

Posted - 2014-06-25 : 15:33:01

Hi James,

thx for your quick reply.
here the definition of the Fields

MA_SumLohn_an.PSNR is int
MA_ZUSATZFELD.PSNR is int
BUCHEINZ.BEDATE is int

Kr
zero1de

quote:
Originally posted by James K

Is MA_SumLohn_an.PSNR or MA_ZUSATZFELD.PSNR of data type DATETIME? If one is and the other is not, this can happen. I don't see anything else that could cause a problem given that if you select from the view that works correctly. If that were not the case, I would also look at what is in the column BUCHEINZ.BEDATE. Is it a character data type or is it a datetime data type? If it is character data type, that may be causing the problem when you are trying to convert that to a datetime type in the view.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources