Gowtham Ramamoorthy Gowtham Ramamoorthy - 5 months ago 21
SQL Question

Argument data type date is invalid for argument 1 of substring function error for date data type

I'm trying to execute the below code but yet its throwing me the error for this type of combination.

colud anyone let me know why this error occurs ?

BEGIN
DECLARE @TimeDimensionDate AS DATE

SET @TimeDimensionDate = '1998-01-01'
Select CONVERT ( DATE,REPLACE (@TimeDimensionDate,SUBSTRING (@TimeDimensionDate,6,2),'01'))

END


Thanks

Answer

You are trying to use replace() on a date. That means that the date is being converted to a string, using the local settings. One method would be to explicitly convert the date to a string using a format such as 121.

Alternatively:

select dateadd(month, 1 - month(@TimeDimensionDate), @TimeDimensionDate)