Un po’ di tempo fa avevo trovato un bel post, sul sempre interessante blog di Jeff Smith su sqlteam, riguardante le funzioni “essenziali” per maneggiare i tipi DateTime in attesa di SQL 2008 e dei nuovi tipi Date e Time. Visto che da allora me le porto sempre dietro su ogni database, riporto lo script di generazione delle stesse.
/****** Object: UserDefinedFunction [dbo].[UF_Date] Script Date: 09/01/2007 19:29:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[UF_Date](@Year int, @Month int, @Day int)
— returns a datetime value for the specified year, month and day
returns datetime
as
begin
return dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)
end
GO
/****** Object: UserDefinedFunction [dbo].[UF_DateOnly] Script Date: 09/01/2007 19:29:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[UF_DateOnly](@DateTime DateTime)
— Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.
returns datetime
as
begin
return dateadd(dd,0, datediff(dd,0,@DateTime))
end
GO
/****** Object: UserDefinedFunction [dbo].[UF_DateTime] Script Date: 09/01/2007 19:29:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[UF_DateTime](@Year int, @Month int, @Day int, @Hour int, @Minute int, @Second int)
— returns a dateTime value for the date and time specified.
returns datetime
as
begin
return dbo.Date(@Year,@Month,@Day) + dbo.Time(@Hour, @Minute,@Second)
end
GO
/****** Object: UserDefinedFunction [dbo].[UF_Time] Script Date: 09/01/2007 19:29:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[UF_Time](@Hour int, @Minute int, @Second int)
— Returns a datetime value for the specified time at the “base” date (1/1/1900)
returns datetime
as
begin
return dateadd(ss,(@Hour*3600)+(@Minute*60)+@Second,0)
end
GO
/****** Object: UserDefinedFunction [dbo].[UF_TimeOnly] Script Date: 09/01/2007 19:29:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[UF_TimeOnly](@DateTime DateTime)
— returns only the time portion of a DateTime, at the “base” date (1/1/1900)
returns datetime
as
begin
return @DateTime - dbo.DateOnly(@DateTime)
end
GO
Fonte: Essential SQL Server Date, Time and DateTime Functions
SQL Server, function, datetime, date, time
