SQL Server各種時間業務処理

1.SQLは、乱数文字列をストリーム番号として生成する

SELECT CONVERT(varchar(100), GETDATE(), 112)
  --      
 SELECT 'JS'+replace(CONVERT(varchar(10), GETDATE(), 112),'-','')
    +replace(CONVERT(varchar(10), GETDATE(), 108),':','')
    --      
    select char(65+ceiling(rand()*25))+RIGHT(Char(65),30)
    select CEILING(rand()*10)
    --            
    select UPPER( CHAR(65+CEILING(rand()*25)))+RIGHT(rand(),5)
    select UPPER( CHAR(65+CEILING(rand()*25)))+RIGHT(rand()*10,1)
    --  +      +  +       
     SELECT 'LS'+replace(CONVERT(varchar(10), GETDATE(), 112),'-','')
    +replace(CONVERT(varchar(10), GETDATE(), 108),':','')+UPPER( CHAR(65+CEILING(rand()*25)))+RIGHT(rand(),5)
    --  +      +           +     3 
    SELECT 'LS'+replace(CONVERT(varchar(10), GETDATE(), 112),'-','')
    +replace(CONVERT(varchar(10), GETDATE(), 108),':','')
    +UPPER(CHAR(65+CEILING(RAND()*25))
    +CHAR( 65+CEILING(RAND()*25))
    +CHAR( 65+CEILING(RAND()*25))
    +CHAR( 65+CEILING(RAND()*25))
    )+RIGHT(rand(),3)

 
2.SQLは、今週の先月の開始時間と終了時間を取得します.

            DECLARE @ThisWeekStartTime NVARCHAR(100),@ThisWeekEndTime NVARCHAR(100),--  
            @LastWeekStartTime NVARCHAR(100),@LastWeekEndTime NVARCHAR(100),--  
            @ThisMonthStartTime NVARCHAR(100),@ThisMonthEndTime  NVARCHAR(100),--  
            @LastMonthSartTime NVARCHAR(100),@LastMonthEndTime NVARCHAR(100),--  
            @LastestHalfYearStartTime NVARCHAR(100),@LastestHalfYearEndTime NVARCHAR(100),--   
            @LastestOneYearStartTime NVARCHAR(100),@LastestOneYearEndTime NVARCHAR(100)--   
            
    SELECT @ThisWeekStartTime= CONVERT(nvarchar(10), DATEADD(wk, DATEDIFF(wk,0,DATEADD(dd, -1, getdate()) ), 0),121)--      
    SELECT @ThisWeekEndTime= CONVERT(nvarchar(10), DATEADD(wk, DATEDIFF(wk,0,DATEADD(dd, -1, getdate()) ), 6),121)--      
    
    SELECT @LastWeekStartTime=  CONVERT(nvarchar(10),DATEADD(wk, DATEDIFF(wk,0,DATEADD(dd, -7, getdate()) ), 0),121)--      
    SELECT @LastWeekEndTime= CONVERT(nvarchar(10), DATEADD(wk, DATEDIFF(wk,0,DATEADD(dd, -7, getdate()) ), 6),121)--      
     
    SELECT @ThisMonthStartTime=CONVERT(nvarchar(10),dateadd(dd,-day(getdate())+1,getdate()),121)--      
    SELECT @ThisMonthEndTime=CONVERT(nvarchar(10),dateadd(dd,-day(getdate()),dateadd(m,1,getdate())),121)--      
    
    SELECT @LastMonthSartTime=CONVERT(nvarchar(10),dateadd(dd,-day(dateadd(month,-1,getdate()))+1,dateadd(month,-1,getdate())),121)--      
    SELECT @LastMonthEndTime= CONVERT(nvarchar(10), dateadd(dd,-day(getdate()),getdate()),121) --      
 
    SELECT @LastestHalfYearStartTime= CONVERT(nvarchar(10),  dateadd(dd,-day(dateadd(month,-6,getdate()))+1,dateadd(month,-6,getdate())) ,121)--       
    SELECT @LastestHalfYearEndTime=CONVERT(nvarchar(10), dateadd(dd,-day(getdate()),getdate()),121)--       
    
    SELECT @LastestOneYearStartTime= CONVERT(nvarchar(10), dateadd(dd,-day(dateadd(month,-12,getdate()))+1,dateadd(month,-12,getdate())) ,121)--       
    SELECT @LastestOneYearEndTime=CONVERT(nvarchar(10), dateadd(dd,-day(getdate()),getdate()),121)--       

--     select getdate()
--          （     ）select DATEADD(week,DATEDIFF(week,0,getdate()),0)
--    ：select dateadd(week,-1,DATEADD(week,DATEDIFF(week,0,getdate()),0))
--     ：select dateadd(week,-2,DATEADD(week,DATEDIFF(week,0,getdate()),0))
--      ：select dateadd(week,-3,DATEADD(week,DATEDIFF(week,0,getdate()),0))

3.開始時間と終了時間をつなぎ合わせる

SELECT CONVERT(nvarchar(10),dateadd(dd,-day(dateadd(month,-1,getdate()))+1,dateadd(month,-1,getdate())),121)+'～'+
  CONVERT(nvarchar(10), dateadd(dd,-day(getdate()),getdate()),121)--      ～           


 SELECT  @LastMonthTime=CONVERT(nvarchar(10),dateadd(dd,-day(dateadd(month,-1,getdate()))+1,dateadd(month,-1,getdate())),121)+'～'+
  CONVERT(nvarchar(10), dateadd(dd,-day(getdate()),getdate()),121)--      ～           

--     
 SELECT  convert(VarChar(7), '2018-11-31 23:59:59',120)

select  CONVERT(VarChar(7), GETDATE(), 120) 


--        COUNT     case  when then end  
  select d.Name,
     count(case  when d.Name='A' then 'num'  end)as  AA,
     count(case  when d.Name='B' then 'num'  end)as  BB,
     count(case  when d.Name='C' then 'num'  end)as  CC,
     count(case  when d.Name='D' then 'num'  end)as  DD,
     count(case  when d.Name='E' then 'num'  end)as EE,
     count(case  when d.Name='F' then 'num'  end)as  FF,
     count(case  when d.Name='G' then 'num'  end)as  GG
     from  Brands d  
     group by d.Name

 

--      
  Select CONVERT(varchar(100), GETDATE(), 23)--2018-11-22
  Select CONVERT(varchar(100), '2018-11-31 23:59:59', 23)--2018-11-22
 Select CONVERT(varchar(10),'2018-11-01 23:59:59',120)+'-'+CONVERT(varchar(10),'2018-11-31 23:59:59',120) 

 
 

    --  Update  
    update A  SET  A.SettleState='   ',A.SettleDate=getdate() FROM  OrderDetail A
    WHERE A.SettleState='   '