简书链接:SQLServer时间计算查询昨天3点后的数据格式转换等
文章字数：369,阅读全文大约需要1分钟
select DATEADD(day, DATEDIFF(day, '1900-01-01', GETDATE()) - 1, '1900-01-01') + CAST('03:00:00' AS datetime)
现在是2023 12月20
输出后
image.png

今天3点
select DATEADD(day, DATEDIFF(day, '1900-01-01', GETDATE()), '1900-01-01') + CAST('03:00:00' AS datetime)
首先计算出昨天的日期（减去1天），然后在那个日期上加上时间’03:00:00’，以此作为查询的起始时间。
MySQL，使用CURDATE()和ADDDATE()函数

使用USA格式（mm/dd/yyyy）的字符串转换为datetime：

SELECT CONVERT(datetime, '12/22/2023', 101)
使用ISO格式（yyyy-mm-dd）的字符串转换为datetime：

SELECT CONVERT(datetime, '2023-12-22', 102)
使用ISO8601格式（yyyy-mm-ddThh:mi:ss.mmm）的字符串转换为datetime：
sql
SELECT CONVERT(datetime, '2023-12-22T12:00:00.000', 126)

select cast('2023-12-22 09:43:04' as datetime)

小于8 取昨天8 ，否则取今天8

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE @currentDateTime DATETIME = GETDATE();
DECLARE @targetDateTime DATETIME;

IF DATEPART(HOUR, @currentDateTime) BETWEEN 0 AND 7
BEGIN
    SET @targetDateTime = DATEADD(DAY, -1, @currentDateTime);
    SET @targetDateTime = DATEADD(HOUR, 8, @targetDateTime);
END
ELSE
BEGIN
    SET @targetDateTime = DATEADD(HOUR, 8, @currentDateTime);
END

SELECT @targetDateTime AS TargetDateTime;

直接在sql条件里面写

1
2
xxdate.InsertTime>=   DATEADD(day, DATEDIFF(day, '1900-01-01', GETDATE())-( CASE 
            WHEN  DATEPART(HOUR, GETDATE())  BETWEEN 0 AND 7 THEN 0 else  1 end ), '1900-01-01') + CAST('08:00:00' AS datetime)

当天变2023/12/28
101不行，因为是反的，102 可以进行替换,
SELECT REPLACE(CONVERT(varchar, GETDATE(), 102), '.', '/') AS FormattedDate;

Style 100: ‘mon dd yyyy hh:miAM (or PM)’

1
SELECT CONVERT(varchar, GETDATE(), 100) AS FormattedDate;

Style 101: ‘mm/dd/yyyy’

1
SELECT CONVERT(varchar, GETDATE(), 101) AS FormattedDate;

Style 102: ‘yyyy.mm.dd’

1
SELECT CONVERT(varchar, GETDATE(), 102) AS FormattedDate;

Style 103: ‘dd mon yyyy hh:mi:ss:mmmAM (or PM)’

1
SELECT CONVERT(varchar, GETDATE(), 103) AS FormattedDate;

Style 104: ‘hh:mi:ss:mmm(24h)’

1
SELECT CONVERT(varchar, GETDATE(), 104) AS FormattedTime;

Style 105: ‘dd-mm-yy’

1
SELECT CONVERT(varchar, GETDATE(), 105) AS FormattedDate;

Style 106: ‘dd mon yy’

1
SELECT CONVERT(varchar, GETDATE(), 106) AS FormattedDate;

Style 107: ‘hh:mi:ss’

1
SELECT CONVERT(varchar, GETDATE(), 107) AS FormattedTime;

Style 108: ‘hh:mi:ss.mmm’

1
SELECT CONVERT(varchar, GETDATE(), 108) AS FormattedTime;

Style 109: ‘mon dd yyyy HH:mi:ss:mmm(24h)’

SELECT CONVERT(varchar, GETDATE(), 109) AS FormattedDateTime;