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"
Manual splicing Year Month DayResultselect 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 dualCopy after login
September 08, 2021##1.1.2 yyyy-mm -dd hh24:mi:ss
Resultselect to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dualCopy after login
##1.1.3 yyyyfm-mm-dd hh24:mi:ss
select to_char(sysdate, 'yyyyfm-mm-dd hh24:mi:ss') from dualCopy after loginResult
##2021-9-8 11:21:55
1.1.4 yyyy/mm/dd
select to_char(sysdate, 'yyyy/mm/dd') from dual
Result
2021/09/08
1.1.5 yyyymmdd
select to_char(sysdate, 'yyyymmdd') from dual
Result
20210908
1.2 Convert string to date
select to_date('20210908', 'yyyymmdd') from dual
Result2021/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 conversion2.1 Date to string
SELECT CONVERT(varchar (100), GETDATE(), 111)
##2.1.2 yyyy-mm-ddRESULTSELECT CONVERT(varchar (100), GETDATE(), 23)Copy after login
ResultSELECT CONVERT(varchar (100), GETDATE(), 112)Copy after login
ResultSELECT CONVERT(varchar (100), GETDATE(), 120)Copy after login
Resultselect CONVERT(varchar, DATEPART(yy, GETDATE())) + '年' + CONVERT(varchar, DATEPART(mm, GETDATE())) + '月' + CONVERT(varchar, DATEPART(dd, GETDATE())) + '日'Copy after login
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
NULLSELECT 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
3.1 Convert date to stringReference: Date and time type and formatting in MySQL
DATE_FORMAT( ) function
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')Copy after login3.2 Convert string to date
SELECT STR_TO_DATE('2019年01月17日 19时05分05秒', '%Y年%m月%d日 %H时%i分%s秒');
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!