CODEWITHSUNDEEP
sqlstored-proceduressql-server-2012
WiiMaxx
WiiMaxx

Reputation: 5420

Can't convert Datetime to Int

I am trying to SELECT rows based on two values with this stored procedure:

ALTER PROCEDURE [dbo].[MYSelect]

    -- Parameters with default values
    @MitarbeiterId          AS int,
    @Wann                   AS datetime2(7)

AS

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
        SET NOCOUNT ON;

        SELECT  *
        FROM    [Plan] 
        WHERE   RefMitarbeiterId    =   @MitarbeiterId
        AND     Jahr                <=  YEAR(@Wann)
        AND     Monat               <=  MONTH(@Wann)
        AND     Abgeschlossen       =   0

The problem is the following part:

        AND     Jahr                <=  YEAR(@Wann)
        AND     Monat               <=  MONTH(@Wann)

I don't know why this part is causing a (syntax) problem. With this lines included, using MSSQL Studio, I receive an error unable to convert DateTime to Integer. Removing these returns results and has no syntax error. I use YEAR() and MONTH() elsewhere, so I'm confused why there is a problem here.

This is the Plan table:

Plan Table

Upvotes: 0

Views: 188

Answers (2)

WiiMaxx
WiiMaxx

Reputation: 5420

After testing different ways of running this Procedure,
i now think its a bug if you use the View with a Datetime Parameter in SQL Server Manangement Studio

because i tested

EXEC

enter image description here

which works well

then removed my parameters

enter image description here
this returns an error which tells me that it was unable to convert '01.07.2014 00:00:00' to a datetime but it returns the right Rows

if i now do it again with my parameters and add my values

enter image description here

i get an exception which tells me the same think like the error above so i have to remove the ' and then i get the unable to convert DateTime to Integer which it's a bug in my opinion

Upvotes: 0

Serpiton
Serpiton

Reputation: 3684

The conditions

AND  Jahr <=  YEAR(@Wann)
AND  Monat <= MONTH(@Wann)

separate the condition to year and month.

It doesn't mean where the years and month are less then the parameter but where year is less or equal than the year of the parameter and the month is less or equal than the month of the parameter.

Using today (2014-06-30) as parameter your query will return every row where with Monat between 1 and 6 for the past and current year: the rows from December 2013, for example, will not be returned.

To get all the rows before the date of the parameter you'll need to change the conditions to 

AND  ((Jahr = YEAR(@Wann) AND  Monat <= MONTH(@Wann))
   OR  Jahr < YEAR(@Wann))

Upvotes: 1

Related Questions