Home > Database > SQL > body text

Examples of conversion operations between dates and strings in SQL

WBOY
Release: 2022-11-03 20:39:40
forward
6206 people have browsed it

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.

Examples of conversion operations between dates and strings in SQL

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
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
Copy after login

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
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
Copy after login
Result

2021/09/08

1.1.5 yyyymmdd

Only displays the year, month and day without separators

select
  to_char(sysdate, 'yyyymmdd') 
from
  dual
Copy after login
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
Copy after login
Result

2021/09/ 08 0:00:00

select
  to_date('2021-9-8 11:21:55', 'yyyyfm-mm-dd hh24:mi:ss') 
from
  dual
Copy after login
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)
Copy after login

RESULT

2021/09/08

##2.1.2 yyyy-mm-dd

SELECT
  CONVERT(varchar (100), GETDATE(), 23)
Copy after login
RESULT

2021-09-08


##2.1.3 yyyymmdd

SELECT
  CONVERT(varchar (100), GETDATE(), 112)
Copy after login

Result

20210908


2.1.4 yyyy-mm-dd hh:mm:ss

SELECT
  CONVERT(varchar (100), GETDATE(), 120)
Copy after login

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())) + '日'
Copy after login

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)
Copy after login

result

2020/09/08 0:00:00

SELECT
  CAST('20200908' as date)
Copy after login

RESULT

2020/09/08

SELECT 
  TRY_CAST('2021-09-08' as datetime)
Copy after login

RESULT

2021/09/08 0:00:00

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

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秒' );
Copy after login
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 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!

Related labels:
sql
source:jb51.net
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template