I have a table ABS

Create table ABS (P_DATE DATETIME);

Now I want to store value in P_DATE column in YYYYMMDD HH24:MI:SS format in ABS table. Date is coming in YYMMDD format like 230118.

Want to save date like 20230144 00:00:00 format.

Kindly help, how can I achieve this

CodePudding user response：

The question misunderstands how datetime columns work. There is no human-readable format.

Instead, they store the data as a binary 8-byte decimal value optimized for storage/memory use, correctness, indexing, and date math/comparison operations.

This is good, and you should not give in to any temptation to instead use a varchar column to get the format you want. Instead, only worry about format when selecting data back out of the table, and even then it's usually better to let the client program or reporting tool worry about it.

CodePudding user response：

What need is to convert the string, and give the right style in your case 12

SELECT CONVERT(datetime, '230125',12)

Create table ABS (P_DATE DATETIME);

INSERT INTO ABS VALUES(CONVERT(datetime, '230125',12))

1 rows affected

SELECT * FROM ABS

fiddle