Here are some SQL Server Date Time Useful functions.
- DateCreate - Creates Date through Year, Month and Day
- DateToStrShort - Accepts Date and returns as '1-Jan-2009'
- DateGetDaysInMonth - Accepts Month and Year and returns number of days in month
- DateGetTimeOnly - Accepts date and returns time as '10:33 PM'
- DateGetDateOnly - Accepts Date and convert it into Date only i.e. removes the Time Part
- DateGetMonthFirstDate - Gets the first date of Month
- DateGetMonthLastDate - Gets the last date of Month
- DateGetMonthName - Gets the Month Name
Review the functions below or download the SQL file.
-- =============================================
-- Author: Arsalan Tamiz
-- Description: This function accepts Year, Month and Day
-- and returns Date
-- =============================================
CREATE FUNCTION [DateCreate]
(
@Year int,
@Month int,
@Day int
)
RETURNS datetime
AS
BEGIN
-- Declare the return variable here
DECLARE @Result datetime;
-- Add the T-SQL statements to compute the return value here
SET @Result = CAST (
CAST(@Year AS varchar(10)) + '-' +
CAST(@Month AS varchar(2)) + '-' +
CAST(@Day AS varchar(2))
AS datetime
);
-- Return the result of the function
RETURN @Result;
END
-- =============================================
-- Author: Arsalan Tamiz
-- Description: This function converts the given
-- date in to string. Useful function
-- for displaying date as '10-Jan-2009' for example
-- =============================================
CREATE FUNCTION [DateToStrShort]
(
-- Add the parameters for the function here
@DateToConvert datetime
)
RETURNS varchar(50)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result varchar(50)
SET @Result = CAST(Day(@DateToConvert) AS varchar(50)) + '-' +
Left(DateName(m, @DateToConvert), 3) + '-' +
CAST(Year(@DateToConvert) AS varchar(50))
-- Return the result of the function
RETURN @Result;
END
-- =============================================
-- Author: unknown (I copied it from internet)
-- Description: Returns the Days in a Month
-- parameters = Year and Month
-- =============================================
CREATE FUNCTION [DateGetDaysInMonth] (@Year int, @Month int)
RETURNS INT
AS
BEGIN
RETURN CASE WHEN @Month IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN @Month IN (4, 6, 9, 11) THEN 30
ELSE CASE WHEN (@Year % 4 = 0 AND
@Year % 100 != 0) OR
(@Year % 400 = 0)
THEN 29
ELSE 28
END
END
END
-- =============================================
-- Author: Arsalan Tamiz
-- Description: Gets the Time from Date
-- =============================================
CREATE FUNCTION [DateGetTimeOnly]
(
-- Add the parameters for the function here
@DateToFormat datetime
)
RETURNS varchar(100)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result varchar(100);
DECLARE @H varchar(3);
DECLARE @Hour int;
DECLARE @M varchar(3);
SET @Hour = DatePart(hh, @DateToFormat);
SET @M = DateName(mi, @DateToFormat);
If @M = '0' SET @M = '00';
-- Add the T-SQL statements to compute the return value here
SET @H = CAST(CASE WHEN @Hour > 12 THEN @Hour - 12 ELSE CASE WHEN @Hour = 0 THEN 12 ELSE @Hour END END AS varchar);
SET @Result = @H + ':' + @M + ' ' + CASE WHEN @Hour >= 12 THEN 'PM' ELSE 'AM' END;
-- Return the result of the function
RETURN @Result;
END
-- =============================================
-- Author: Arsalan Tamiz
-- Description: Returns Date only from given Date
-- That is removes the Time Part
-- so that we can compare two dates
-- =============================================
CREATE FUNCTION [DateGetDateOnly]
(
-- Add the parameters for the function here
@DateToConvert datetime
)
RETURNS datetime
AS
BEGIN
-- Declare the return variable here
DECLARE @Result datetime;
-- Add the T-SQL statements to compute the return value here
SET @Result = CAST(CONVERT(varchar(100), @DateToConvert, 112) AS datetime);
-- Return the result of the function
RETURN @Result;
END
-- =============================================
-- Author: Arsalan Tamiz
-- Description: Gets the first date of month
-- =============================================
CREATE FUNCTION [DateGetMonthFirstDate]
(
-- Add the parameters for the function here
@Month int
)
RETURNS datetime
AS
BEGIN
-- Declare the return variable here
DECLARE @Result datetime;
DECLARE @Year int;
SET @Year = Year(GetDate());
SET @Result = CAST (
CAST(@Year AS varchar(10)) + '-' +
CAST(@Month AS varchar(2)) + '-1' AS datetime
);
-- Return the result of the function
RETURN @Result;
END
-- =============================================
-- Author: Arsalan Tamiz
-- Description: Returns last date of month
-- ***********************************************
-- IMPORTANT: This function depends on [dbo].[DateGetDaysInMonth]() function
-- which can be found above
-- ***********************************************
-- =============================================
CREATE FUNCTION [DateGetMonthLastDate]
(
-- Add the parameters for the function here
@Month int
)
RETURNS datetime
AS
BEGIN
-- Declare the return variable here
DECLARE @Result datetime;
DECLARE @Year int;
SET @Year = Year(GetDate());
SET @Result = CAST (
CAST(@Year AS varchar(10)) + '-' +
CAST(@Month AS varchar(2)) + '-' +
CAST([dbo].[DateGetDaysInMonth](@Year, @Month) AS varchar(2)) +
AS datetime
);
-- Return the result of the function
RETURN @Result;
END
-- =============================================
-- Author: Arsalan Tamiz
-- Description: This function Retuns the name of month
-- =============================================
CREATE FUNCTION [DateGetMonthName]
(
-- Add the parameters for the function here
@Month int
)
RETURNS varchar(100)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result varchar(100)
DECLARE @d datetime;
SET @d = CAST('2009-' + CAST(@Month AS varchar(2)) + '-1' AS datetime);
-- Add the T-SQL statements to compute the return value here
SET @Result = DateName(mm, @d);
-- Return the result of the function
RETURN @Result;
END
0 comments:
Post a Comment