Examples of conversion operations between dates and strings in SQL

This article brings you relevant knowledge about SQL, which mainly introduces the relevant content of conversion operations between dates and strings. Let’s take a look at it together. I hope it will be helpful to everyone.

Recommended study: "SQL Tutorial"

1. Convert Oracle dates and strings to each other

1.1 Date to characterString

##1.1.1 yyyy year mm month dd day hh24 hour mi minute ss second

Manual splicing Year Month Day

select
      to_char(sysdate, 'yyyy') || '年' 
   || to_char(sysdate, 'mm') || '月' 
   || to_char(sysdate, 'dd') || '日'
   || ' '
   || to_char(sysdate, 'hh24') || '時' 
   || to_char(sysdate, 'mi') || '分' 
   || to_char(sysdate, 'ss') || '秒' 
from
  dual

Result

September 08, 2021

##1.1.2 yyyy-mm -dd hh24:mi:ss

Do not remove 0 from the date, and display it in 24-hour format

select
  to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') 
from
  dual

Result

2021-09-08 11:12:02

##1.1.3 yyyyfm-mm-dd hh24:mi:ss

The date removes 0 and displays it in 24-hour format

select
  to_char(sysdate, 'yyyyfm-mm-dd hh24:mi:ss') 
from
  dual

Result

##2021-9-8 11:21:55

1.1.4 yyyy/mm/dd

Only display the year, month and day, and there are separators

select
  to_char(sysdate, 'yyyy/mm/dd') 
from
  dual

Result

2021/09/08

1.1.5 yyyymmdd

Only displays the year, month and day without separators

select
  to_char(sysdate, 'yyyymmdd') 
from
  dual

Result

20210908

1.2 Convert string to date

Use to_date('date', 'format') function, the specific format is the same as that in to_char()

select
  to_date('20210908', 'yyyymmdd') 
from
  dual

Result

2021/09/ 08 0:00:00

select
  to_date('2021-9-8 11:21:55', 'yyyyfm-mm-dd hh24:mi:ss') 
from
  dual

Result

2021/09/08 11:21:55

二. SqlServer date and string conversion

2.1 Date to string

2.1.1 yyyy/mm/dd

SELECT
  CONVERT(varchar (100), GETDATE(), 111)

RESULT

2021/09/08

##2.1.2 yyyy-mm-dd

SELECT
  CONVERT(varchar (100), GETDATE(), 23)

RESULT

2021-09-08

##2.1.3 yyyymmdd

SELECT
  CONVERT(varchar (100), GETDATE(), 112)

Result

20210908

2.1.4 yyyy-mm-dd hh:mm:ss

SELECT
  CONVERT(varchar (100), GETDATE(), 120)

Result

2021-09-08 12:30:33

2.1.5 yyyy mm month dd day

select
    CONVERT(varchar, DATEPART(yy, GETDATE())) + '年' 
  + CONVERT(varchar, DATEPART(mm, GETDATE())) + '月' 
  + CONVERT(varchar, DATEPART(dd, GETDATE())) + '日'

Result

September 8, 2021

2.2 Convert string to date

CAST('String date' as 'Data type- Usually date or datetime')

If the string date is not a legal date, an error will be reported TRY_CAST('String date' as 'Data type - usually date or datetime' )

Try to convert a string date to a date type. If the conversion fails, it will return

NULL

SELECT
  CAST('20200908' as datetime)

result

2020/09/08 0:00:00

SELECT
  CAST('20200908' as date)

RESULT

2020/09/08

SELECT 
  TRY_CAST('2021-09-08' as datetime)

RESULT

2021/09/08 0:00:00

SELECT 
  TRY_CAST('2021/09/08 11:21:55' as datetime)

Result

2021/09/08 11:21:55

3. Mysql date and string conversion

Reference: Date and time type and formatting in MySQL

3.1 Convert date to string

DATE_FORMAT( ) function

3.1.1 yyyy year mm month dd day hh hour ii minute ss second

SELECT
	DATE_FORMAT(NOW(), '%Y年%m月%d日 %H时%i分%s秒' );

Result

##2021-09-08 21:04:59

3.1.2 yyyy-mm -dd hh:ii:ss

SELECT
	DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')

3.2 Convert string to date

STR_TO_DATE function

SELECT
	STR_TO_DATE('2019年01月17日 19时05分05秒', '%Y年%m月%d日 %H时%i分%s秒');

Result

2019-01-17 19:05:05

Recommended study: "
SQL Tutorial

"

The above is the detailed content of Examples of conversion operations between dates and strings in SQL. For more information, please follow other related articles on the PHP Chinese website!