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

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

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

1. 用法

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

2. 基本語法

FORMAT(value, format)

value ：要格式化的日期、時間或數(shù)字值。
format ：指定要應用的格式的字符串?？梢允褂貌煌母袷酱a來定義所需的格式，格式代碼的具體取決于值的類型。

① 日期格式代碼

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

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é)果如下圖所示：

② 時間格式代碼

hh：12 小時制的小時，帶前導零（例如：01-12）
HH：24 小時制的小時，帶前導零（例如：00-23）
mm：分鐘，帶前導零（例如：00-59）
ss：秒，帶前導零（例如：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ù)

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

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

3. 應用示例

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"

注意事項：

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

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

附：日期計算

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

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

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