I'm using PostgreSQL, but this question is for any modern dbms I want to basically convert a datetime column which has yyyy/mm/dd into just yyyy/mm

I tried getting months and year separately and using Concat, but the problem is the month comes as a single digit integers for values < 10 and that messes up ordering

select *,
concat(date_part('year' , date_old), '/', date_part('month' , date_old)) as date_new
from table 

CodePudding user response：

You can use to_char()

to_char(date_old, 'yyyy/mm')

CodePudding user response：

If you want to display your date in the format YYYY-MM then

• In PostgreSQL (db<>fiddle) and Oracle (db<>fiddle), use TO_CHAR:

  SELECT TO_CHAR(date_old, 'YYYY/MM') FROM table_name;
  

• In MySQL (db<>fiddle), use DATE_FORMAT:

  SELECT DATE_FORMAT(date_old, '%Y/%m') FROM table_name;
  

• In SQL Server (db<>fiddle), use CONVERT or, if you are using SQL Server 12 or later, FORMAT:

  SELECT CONVERT(varchar(7), date_old, 111) FROM table_name;
  SELECT FORMAT(date_old,'yyyy/MM') FROM table_name;
  

CodePudding user response：

Don't do this.

If you're able to use the date_part() function, what you have is not actually formatted as the yyyy/mm/dd value you say it is. Instead, it's a binary value that's not human-readable, and what you see is a convenience shown you by your tooling.

You should leave this binary value in place!

If you convert to yyyy/mm, you will lose the ability to directly call functions like date_part(), and you will lose the ability to index the column properly.

What you'll have left is a varchar column that only pretends to be a date value. Schemas that do this are considered BROKEN.