INSERT DATE FROM STRING COLUMN

Hi,
Pls try .. data type conversion issue.

INSERT INTO TMCLDR (TglKldr , CrtId, UpdId)
SELECT REPLACE(CAST(A.TglKldr AS VARCHAR(25)), '2016', '2017') AS TglKldr
, A.CrtId
, A.UpdId
FROM TMCLDR AS A
WHERE YEAR(A.TglKldr) = 2016

Select allOpen in new window

Hi Pawan,

Still get the same error.

Thank you.

try..

INSERT INTO TMCLDR (TglKldr , CrtId, UpdId)
SELECT REPLACE(CAST(A.TglKldr AS VARCHAR(25)), '2016', '2017') AS TglKldr
, A.CrtId
, A.UpdId
FROM TMCLDR AS A
WHERE CAST(A.TglKldr AS VARCHAR(25)) LIKE '%2016%'

Select allOpen in new window

Hi Pawan,

Still get the same error.

Thank you.

Data types of columns in TMCLDR ?

- Data types of columns in TMCLDR ?
Date

Thank you.

I try this.

INSERT INTO TMCLDR (TglKldr)
SELECT REPLACE(CAST(A.TglKldr AS VARCHAR(25)), '2016', '2017') AS TglKldr
FROM TMCLDR AS A
WHERE YEAR(A.TglKldr) = 2016

Still get the same error.

Thank you.

The select is fine, shows data.
When try to insert is the problem.

Thank you.

Pls try..

INSERT INTO TMCLDR (TglKldr)
SELECT CAST(REPLACE(CAST(A.TglKldr AS VARCHAR(25)), '2016', '2017') AS DATE) AS TglKldr
FROM TMCLDR AS A
WHERE YEAR(A.TglKldr) = 2016

Select allOpen in new window

Hope it helps!

The select itself has problem.

--INSERT INTO TMCLDR (TglKldr)
SELECT CAST(REPLACE(CAST(A.TglKldr AS VARCHAR(25)), '2016', '2017') AS DATE) AS TglKldr
FROM TMCLDR AS A
WHERE YEAR(A.TglKldr) = 2016

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.

Thank you.

One question you are getting data from TMCLDR and inserting also in TMCLDR ? Is that required ?

- One question you are getting data from TMCLDR and inserting also in TMCLDR ?
Yes, from last year to next year.

Is that required ?
Yes, suppose there are more data, not just the date, which are the same.

Thank you.

Hi Pawan,

Sorry just reply.

INSERT INTO TMCLDR (TglKldr)
SELECT CAST(REPLACE(CAST(A.TglKldr AS VARCHAR(25)), '2016', '2017') AS DATE) AS TglKldr
FROM TMCLDR AS A
WHERE YEAR(A.TglKldr) = 2016
AND MONTH(A.TglKldr) <> 2

Above query work.

The problem is at month 2. At 2016, it has 29 days, at 2017, it has 28 days.

About your latest solution.
It become has 2 rows of 2017-02-28

Thank you.

Great....that it works.

2017 is not a LEAP year. So in feb we can only have 28 days.

In a leap year we have 1 extra day in feb ( that is 29 days in feb )
2016 is a Leap year. That is why we have 29 days in the month of feb.

You can read more on Leap year from - https://en.wikipedia.org/wiki/Leap_year

Hope it helps !

Hi emi_sastra,
Do we have change anything in this or we can close this?

Regards,
Pawan

Great, it works.

Thank you.

Welcome !!