CODEWITHSUNDEEP
sql-server
Ajay Patel
Ajay Patel

Reputation: 13

"Conversion failed when converting date and/or time from character string." error

I have the following query:

DECLARE @Month int
DECLARE @Year int

set @Month = 2 
set @Year = 2004  

Declare @MonthStartDate datetime
declare @MonthEndDate datetime  

set @MonthStartDate = 'select DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0))' 

set @MonthEndDate = 'select DATEADD(day,-1,DATEADD(month,@Month,DATEADD(year,@Year-1900,0)))'

return @MonthStartDate , @MonthEndDate

But it returns:

"Conversion failed when converting date and/or time from character string."

What's wrong here?

Upvotes: 0

Views: 238

Answers (3)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Looking at your Query (Since you don't have enough Description on the Question ) What I understood is that you are trying to get the First and Last day of a Given month. If you are using a SQL Server Version 2012 or Above, then you have an Inbuild Function called EOMONTH() which can be used to calculate the End of any given month. Otherwise, you may try the below method on any Version on SQL Server 

Declare @MonthStartDate datetime,
        @MonthEndDate datetime,
        @Year int,
        @Month int --It's Better to Declare all the variables in the same space for easy handling

SELECT
    @Month = 2,
    @Year = 2004  -- Common Assignments can be done together

;WITH MNT
AS
(
    SELECT
        MonthStartDate = CAST(@Month AS VARCHAR(20))+'/01/'+CAST(@Year AS VARCHAR(20)) -- Hardcoded Day as 1 since Month Start always at 1
)
SELECT
    @MonthStartDate = MonthStartDate,
    @MonthEndDate = DATEADD(DAY,-1,DATEADD(MONTH,1,MonthStartDate))
    FROM MNT

Upvotes: 0

Dileep Namburu
Dileep Namburu

Reputation: 161

Alternatively, you can also use as follow..

select @MonthStartDate = DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0))
select @MonthEndDate = DATEADD(day,-1,DATEADD(month,@Month,DATEADD(year,@Year-1900,0)))

Upvotes: 1

Paweł Dyl
Paweł Dyl

Reputation: 9143

You should use DateTime expression instead of string literal. Just remove quotes:

DECLARE @Month int

DECLARE @Year int

set @Month = 2 

set @Year = 2004  

Declare @MonthStartDate datetime
declare @MonthEndDate datetime  

set @MonthStartDate = DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0))

set @MonthEndDate = DATEADD(day,-1,DATEADD(month,@Month,DATEADD(year,@Year-1900,0)))

Upvotes: 1

Related Questions