Sql Server 常用语句

AND DateDiff(dd, convert(varchar(50), '2018-11-28') , DataDate) >= 0
AND DateDiff(dd, convert(varchar(50), '2018-11-28') , DataDate) <= 0

SELECT Ceiling(rand() * 1000)

select Convert(decimal(18, 3), '5.26415')
select Convert(Varchar,GetDate(),120)
select Convert(Varchar,GetDate(),23)

dbcc dropcleanbuffers
dbcc freeproccache
set statistics io on
go
--Sql 语句
set statistics io off
go

--自连接
select * from OutcomeRecords as a
where a.DateOutcome in
( select Max(DateOutcome) from OutcomeRecords as b where a.PatientID = b.PatientID and b.DelMark = '0' );

select Floor(DateDiff(day, '1991-09-10', GetDate()) / 365.25);

根据日期获取年龄

set  quoted_identifier on; 
set  ansi_nulls  on; 
go 
--{以下使用函数方法}
create  function [dbo].[GetAgeFun] ( @birthday DateTime )
returns Int
as
begin 
    declare @Age Int ,
            @year Int ,
            @month Int ,
            @Day Int;
    set @Age = 0;
    set @year = 0;
    set @month = 0;
    set @Day = 0;
    set @year = DatePart(year, GetDate()) - DatePart(year, @birthday);
    set @month = DatePart(month, GetDate()) - DatePart(month, @birthday);
    set @Day = DatePart(day, GetDate()) - DatePart(day, @birthday);
    if ( @month > 0 )
       set @Age = @year;
    if ( @month < 0 )
       set @Age = @year - 1;
    if ( @month = 0 )
       begin
             if ( @Day >= 0 )
                set @Age = @year;
             else
                set @Age = @year - 1;
       end;
    return(@Age);
end;
go

View Code

【Row_Number 排序】

SELECT * FROM (SELECT Row_Number() over (order by id) as RowNumber,* FROM dbo.students) a
where a.RowNumber between 2 and 3