Conversion error (date from character)

Transact-SQL
1

Hi. The code below is returning this error when trying to insert the 6 columns into a table which are all defined as varchar -

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

Can anyone tell me why I am getting the error? Thanks.

CONVERT(VARCHAR,LEFT(Table__4.S_PERIOD,4) + 1) + SUBSTRING(Table__4.S_PERIOD,5,10) AS S_PERIOD
CONVERT(VARCHAR(4),YEAR(Table__4.SubDate) + 1) + RIGHT(('00' + CONVERT(VARCHAR(2),MONTH(Table__4.SubDate))),2) AS S_PERIODNum,
Table__4.S_PERIOD AS S_PERIODPrior,
CONVERT(VARCHAR,LEFT(Table__4.I_PERIOD,4) + 1) + SUBSTRING(Table__4.I_PERIOD,5,10) AS I_PERIOD,
CONVERT(VARCHAR(4),YEAR(Table__4.IncDate) + 1) + RIGHT(('00' + CONVERT(VARCHAR(2),MONTH(Table__4.IncDate))),2) AS I_PERIODNum,
Table__4.I_PERIOD AS I_PERIODPrior,

2

One or more of the functions that require a valid date input has invalid data coming in. If the original column is varchar, and you're trying to use it as a date / datetime, the original format is not valid for that.

3

Recommend you include a SIZE for that VARCHAR (and the one later on). Default size of VARCHAR is risky - under some circumstances the default size is 1 :frowning:

Its probably one of those two. If [SubDate] and [IncDate] are NOT Date / DateTime datatype then try:

SELECT TOP 100 *
FROM Table__4
WHERE IsDate(SubDate) <> 1 OR IsDate(IncDate) <> 1

Only uniformly unambiguous format for conversion to date is 'yyyymmdd'. For conversion to DATE then format 'yyyy-mm-dd' is also permitted, but I don't know if a conversion from VARCHAR to the YEAR() function uses implicit conversion to DATE or DATETIME ...

If your date is in some other format you can "force" the date format using

SET DATEFORMAT dmy
... Try IsDate test here ...