Examples of conversion operations between dates and strings in SQL

This article brings you relevant knowledge aboutSQL, 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

Copy after login

September 08, 2021

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

Copy after login

Result

##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

Copy after login

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

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.1 yyyy/mm/dd

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

RESULT

##2.1.2 yyyy-mm-dd

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

Copy after login

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

Copy after login

Result

20210908

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

Copy after login

Result

2021-09-08 12:30:33

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

Copy after login

Result

September 8, 2021

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

If the string date is not a legal date, an error will be reportedTRY_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

SELECT CAST('20200908' as datetime)

result

2020/09/08 0:00:00

SELECT CAST('20200908' as date)

2020/09/08

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

2021/09/08 0:00:00

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

2021/09/08 11:21:55

3. Mysql date and string conversion

Reference: Date and time type and formatting in MySQL

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

3.1.2 yyyy-mm -dd hh:ii:ss

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

Copy after login

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秒');

Copy after login

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!