快捷導(dǎo)航

SQL常見函數(shù)整理之Format將日期、時(shí)間和數(shù)字值格式化

更新時(shí)間：2024年01月12日 11:11:04 作者：@北海怪獸

最近項(xiàng)目總是寫sql查詢時(shí)間,數(shù)據(jù)庫存的時(shí)間有各種格式,下面這篇文章主要給大家介紹了關(guān)于SQL常見函數(shù)整理之Format將日期、時(shí)間和數(shù)字值格式化的相關(guān)資料,需要的朋友可以參考下

1. 用法

用于將日期、時(shí)間和數(shù)字值格式化為指定的字符串表示形式。

2. 基本語法

FORMAT(value, format)

value ：要格式化的日期、時(shí)間或數(shù)字值。
format ：指定要應(yīng)用的格式的字符串。可以使用不同的格式代碼來定義所需的格式，格式代碼的具體取決于值的類型。

① 日期格式代碼

yyyy：四位數(shù)的年份。
MM：兩位數(shù)的月份，帶前導(dǎo)零（注意和分鐘區(qū)分，分鐘是小寫的mm）
MMM：縮寫的月份名稱（例如：Jan、Feb）
dd：兩位數(shù)的日期，帶前導(dǎo)零

select
 format(cast('2024-01-01 10:35:14.277' as datetime), 'yyyy') as Year
,format(cast('2024-01-01 10:35:14.277' as datetime), 'MM'  ) AS Month
,format(cast('2024-01-01 10:35:14.277' as datetime), 'MMM' ) AS MonthAbbr
,format(cast('2024-01-01 10:35:14.277' as datetime), 'dd'  ) as day

查詢結(jié)果如下圖所示：

② 時(shí)間格式代碼

hh：12 小時(shí)制的小時(shí)，帶前導(dǎo)零（例如：01-12）
HH：24 小時(shí)制的小時(shí)，帶前導(dǎo)零（例如：00-23）
mm：分鐘，帶前導(dǎo)零（例如：00-59）
ss：秒，帶前導(dǎo)零（例如：00-59）

select
 format(cast('2024-01-01 19:05:09.277' as datetime), 'hh') as Hour_12h
,format(cast('2024-01-01 19:05:09.277' as datetime), 'HH'  ) AS Hour_24h
,format(cast('2024-01-01 19:05:09.277' as datetime), 'mm' ) AS Minute
,format(cast('2024-01-01 19:05:09.277' as datetime), 'ss'  ) as Second

查詢結(jié)果如下圖所示：

③ 數(shù)字格式代碼

0：數(shù)字占位符，若位數(shù)不足則用零填充
#,##0.00：用逗號分隔千位數(shù)，保留兩位小數(shù)

--假設(shè) 某ID 固定長度是6位
SELECT FORMAT(1234, '000000') AS FormattedNumber;
--查詢結(jié)果：  '001234'

select format(12345.6789,'#,##0.00')
--查詢結(jié)果：   '12,345.68'

3. 應(yīng)用示例

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS FormattedDate;
-- 輸出："2023-10-18"

SELECT FORMAT(GETDATE(), 'hh:mm:ss tt') AS FormattedTime;
-- 輸出：02:28:30 PM

SELECT FORMAT(1234567.89, '#,##0.00') AS FormattedNumber;
-- 輸出："1,234,567.89"

注意事項(xiàng)：

1. 格式代碼注意區(qū)分大小寫

2. FORMAT() 函數(shù)在日期、時(shí)間和數(shù)字格式化方面非常強(qiáng)大，但它也可能會(huì)影響查詢性能。在處理大量數(shù)據(jù)時(shí)，建議使用其他更高效的方法來格式化數(shù)據(jù)，比如在應(yīng)用層或報(bào)表工具中進(jìn)行格式化。

附：日期計(jì)算

在表的實(shí)際計(jì)算中，常需要計(jì)算距離某個(gè)日期或現(xiàn)在日期往前或往后某段時(shí)間的日期。

-- 加(Mysql)
select date_add(now(),interval 1 day);
-- year:年，quarter:季，month:月，week:周，day:天，hour:小時(shí)，minuter:分鐘，second:秒，microsecond:毫秒
-- 加（sql server）
select dateadd(day,n,'2023-06-20')
-- 加（Hive）
select date_add(current_timestamp(),1) -- 加一天
select add_months('2022-07-16', n) -- Hive加n個(gè)月份
select date_format(date_sub(current_timestamp(),1),'yyyy-MM-dd') --指定前一天日期
select from_unixtime((unix_timestamp()-86400),'yyyy-MM') --指定前一天日期
-- Gbase指定前一天日期
select subdate(current_date,interval 1 day) 
-- 減
select date_sub(now(),interval 1 month);
select date_sub(date_format(current_timestamp(),'yyyy-MM-dd'),1) 
select date_sub(current_timestamp(),14) -- Hive指定前一天日期
-- 計(jì)算日期間隔
select datediff('2023-6-16','2023-6-1') -- mysql，Hive計(jì)算兩個(gè)日期之間的間隔（相差天數(shù)）
select datediff('d','2023-6-1','2023-6-16 12:20:00') --sql server計(jì)算兩個(gè)日期之間間隔天數(shù)
select months_between('2023-09-22','2022-07-16') -- Hive計(jì)算兩個(gè)日期之間的間隔
select timediff('12:00:00', '11:30:00') -- 兩個(gè)時(shí)間間隔
select timestampdiff(month,date_sub(now(),interval 60 day),now()); -- 相差月
-- timestampdiff第一個(gè)參數(shù)定義返回計(jì)算的結(jié)果。year:年，quarter:季，month:月，week:周，day:天，hour:小時(shí)，minuter:分鐘，second:秒，microsecond:毫秒
-- timestampdiff減去的日期帶有時(shí)間，會(huì)認(rèn)為是下一天（2023-07-20 08:00:00會(huì)按照2023-07-21計(jì)算）；datediff會(huì)直接截取時(shí)間（2023-07-20 18:00:00也會(huì)按照2023-07-20計(jì)算）
select timestampdiff(day,'2023-07-13',current_date()) -- timestampdiff可以實(shí)現(xiàn)各種字符串（例如2023-07-13,20230703）日期的加減

-- 當(dāng)月的第一天
select STR_TO_DATE(CONCAT(DATE_FORMAT(now(),'%Y-%m'), '-01'), '%Y-%m-%d')
-- sql server返回當(dāng)月1號
select CONVERT(varchar(10),DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0),120)
-- 當(dāng)月最后一天
select DATE_ADD(STR_TO_DATE(CONCAT(DATE_FORMAT(DATE_ADD(now(),INTERVAL 1 MONTH),'%Y-%m'), '-01'), '%Y-%m-%d'),INTERVAL -1 DAY);

總結(jié)

到此這篇關(guān)于SQL常見函數(shù)整理之Format將日期、時(shí)間和數(shù)字值格式化的文章就介紹到這了,更多相關(guān)SQL Format日期時(shí)間和數(shù)字值格式化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: