Jerry Nixon @Work: No DateSerial in SQL? Well, here's the function you need.

Jerry Nixon on Windows

Tuesday, April 22, 2008

No DateSerial in SQL? Well, here's the function you need.

Yes, it is too bad there is no DateSerial in t-SQL. It's a nice function in Visual Basic. Three arguments (month, day, year) return as a combined date. Still, here's the function you need to have the equiv of DateSerial in SQL (I wrote this for SQL 2005, but the syntax should be the same for 2000).

DROP FUNCTION fn_DateSerial
GO

CREATE FUNCTION fn_DateSerial
(
@Month int,
@Day int,
@Year int
)
RETURNS DateTime
AS
BEGIN
return convert(datetime,
convert(varchar, @Month) + '/' +
convert(varchar, @Day) + '/' +
convert(varchar, @Year))
END
GO

set nocount on

select dbo.fn_DateSerial(1, 1, 2010) as Date
select dbo.fn_DateSerial(2, 10, 2020) as Date
select dbo.fn_DateSerial(3, 20, 2030) as Date
select dbo.fn_DateSerial(4, 30, 2040) as Date
select dbo.fn_DateSerial(null, 30, 2040) as Date
select dbo.fn_DateSerial(4, null, 2040) as Date
select dbo.fn_DateSerial(4, 30, null) as Date

/*

output

Date
-----------------------
2010-01-01 00:00:00.000

Date
-----------------------
2020-02-10 00:00:00.000

Date
-----------------------
2030-03-20 00:00:00.000

Date
-----------------------
2040-04-30 00:00:00.000

Date
-----------------------
NULL

Date
-----------------------
NULL

Date
-----------------------
NULL

*/