Sql Server 日期

一、常用日期格式

select CONVERT(varchar(100), getdate(), 20): 2021-08-05 11:41:38
select CONVERT(varchar(100), getdate(), 21): 2021-08-05 11:41:46.210
select CONVERT(varchar(100), getdate(), 22): 08/05/21 11:42:37 AM
select CONVERT(varchar(100), getdate(), 23): 2021-08-05
select CONVERT(varchar(100), getdate(), 24): 11:43:02
select CONVERT(varchar(100), getdate(), 25): 2021-08-05 11:43:06.597
select CONVERT(varchar(100), getdate(), 112):20210805

二、日期处理

1、当年第一天或最后一天

select @BeginDate=(Case when ISNULL(@BeginDate,'')='' then DATEADD(Year, DATEDIFF(Year,0,getdate()), 0) else @BeginDate End)
select @EndDate=(Case when ISNULL(@EndDate,'')='' then DATEADD(DAY,-1,DATEADD(Year,1,DATEADD(Year,DATEDIFF(Year,0,GETDATE()),0)))  else @EndDate End)

2、取一段时间同期数据

Declare @BeginDate date,@EndDate date,@InNum int
Declare @YearStr int,@Num int

select @BeginDate=(Case when @BeginDate is null
                        then DATEADD(Year, DATEDIFF(Year,0,getdate()), 0) 
                        else @BeginDate End),  
       @EndDate=(Case when @EndDate is null
                      then DATEADD(DAY,-1,DATEADD(Year,1,DATEADD(Year,DATEDIFF(Year,0,GETDATE()),0)))  
                      else @EndDate End)  
select @YearStr=YEAR(@BeginDate),
       @Num=(case when ISNULL(@InNum,0)=0  then 2 else @InNum end)
       

if object_id('tempdb..#tempTable_DateTimSection') is not null 
Begin
    drop table #tempTable_DateTimSection
End

select M.*,CONVERT(varchar(6), BeginDate, 112)BeginDateStr,CONVERT(varchar(6), EndDate, 112)EndDateStr
into #tempTable_DateTimSection
from(select a.number,
            DATEADD(YEAR,a.number*(-1),@BeginDate)BeginDate,
            DATEADD(YEAR,a.number*(-1),@EndDate)EndDate 
     from master..spt_values a 
     where a.type='p' and a.number<@Num )M
where 1=1 
order by M.number

select * from #tempTable_DateTimSection