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

Question

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?

can you explain what are you trying to do here ? The error is because you are assigning a query string to datetime data type — Squirrel
– Squirrel, Commented Sep 11, 2018 at 8:27
Can you rephrase your question in the format of sample input and then the expected output? — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 11, 2018 at 8:29

Dileep Namburu · Accepted Answer · 2018-09-11 08:36:07Z

1

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)))

answered Sep 11, 2018 at 8:36

Dileep Namburu

1612 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paweł Dyl · Accepted Answer · 2018-09-11 08:29:07Z

1

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)))

answered Sep 11, 2018 at 8:29

Paweł Dyl

9,1831 gold badge13 silver badges28 bronze badges

Comments

Jayasurya Satheesh · Accepted Answer · 2018-09-11 08:51:33Z

0

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

answered Sep 11, 2018 at 8:51

Jayasurya Satheesh

8,0733 gold badges25 silver badges40 bronze badges

1 Comment

Damien_The_Unbeliever Over a year ago

"Hardcoded Day as 1 since Month Start always at 1" - except for all of the SQL Server installs/users who don't use m/d/y format, in which case you've hard coded the month instead. If you're going to use string conversions (and there really is no need here), at least use an unambiguous format such as YYYYMMDD which is always interpreted the same no matter what date/language settings are in force.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related