Prev: Capturing SQL Version
Next: Getting the opposite of a join table
From: JimP on 16 Jul 2008 17:26
Is there a TSQL function similar to the MS-Access
"DateSerial(Year,Month,Day)" function, that will convert values for a year,
month and day to a date?


From: Aaron Bertrand [SQL Server MVP] on 16 Jul 2008 17:39
There is nothing built in, but you can create one easily.

USE tempdb;
GO

CREATE FUNCTION dbo.DateSerial
(
@Year SMALLINT,
@Month TINYINT,
@Day TINYINT
)
RETURNS SMALLDATETIME
AS
BEGIN
RETURN
(
SELECT CASE ISDATE(d) WHEN 1 THEN d ELSE NULL END FROM
(
SELECT d = RTRIM(@Year) + RIGHT('0' + RTRIM(@Month), 2)
+ RIGHT('0' + RTRIM(@Day), 2)
) AS s
);
END
GO

SELECT dbo.DateSerial(2008, 4, 21);

SELECT dbo.DateSerial(2008, 4, 31);
GO

DROP FUNCTION dbo.DateSerial;
GO


On 7/16/08 5:26 PM, in article p46dnaQiRbMI-uPVnZ2dnUVZ_oTinZ2d(a)comcast.com,
"JimP" <jpockmire(a)pockmiresolutions.com> wrote:

> Is there a TSQL function similar to the MS-Access
> "DateSerial(Year,Month,Day)" function, that will convert values for a year,
> month and day to a date?
>
>

From: Vern Rabe on 16 Jul 2008 18:01
Aaron:

What do the RTRIMs do?

Vern Rabe

"Aaron Bertrand [SQL Server MVP]" wrote:

> There is nothing built in, but you can create one easily.
>
> USE tempdb;
> GO
>
> CREATE FUNCTION dbo.DateSerial
> (
> @Year SMALLINT,
> @Month TINYINT,
> @Day TINYINT
> )
> RETURNS SMALLDATETIME
> AS
> BEGIN
> RETURN
> (
> SELECT CASE ISDATE(d) WHEN 1 THEN d ELSE NULL END FROM
> (
> SELECT d = RTRIM(@Year) + RIGHT('0' + RTRIM(@Month), 2)
> + RIGHT('0' + RTRIM(@Day), 2)
> ) AS s
> );
> END
> GO
>
> SELECT dbo.DateSerial(2008, 4, 21);
>
> SELECT dbo.DateSerial(2008, 4, 31);
> GO
>
> DROP FUNCTION dbo.DateSerial;
> GO
>
>
> On 7/16/08 5:26 PM, in article p46dnaQiRbMI-uPVnZ2dnUVZ_oTinZ2d(a)comcast.com,
> "JimP" <jpockmire(a)pockmiresolutions.com> wrote:
>
> > Is there a TSQL function similar to the MS-Access
> > "DateSerial(Year,Month,Day)" function, that will convert values for a year,
> > month and day to a date?
> >
> >
>
>
From: Aaron Bertrand [SQL Server MVP] on 16 Jul 2008 18:29
> What do the RTRIMs do?

Just my lazy shorthand for CONVERT(VARCHAR(12), @value)


From: Madhivanan on 17 Jul 2008 09:42
On Jul 17, 2:39 am, "Aaron Bertrand [SQL Server MVP]"
<ten....(a)dnartreb.noraa> wrote:
> There is nothing built in, but you can create one easily.
>
> USE tempdb;
> GO
>
> CREATE FUNCTION dbo.DateSerial
> (
>     @Year  SMALLINT,
>     @Month TINYINT,
>     @Day   TINYINT
> )
> RETURNS SMALLDATETIME
> AS
> BEGIN
>     RETURN
>     (
>         SELECT CASE ISDATE(d) WHEN 1 THEN d ELSE NULL END FROM
>         (
>             SELECT d = RTRIM(@Year) + RIGHT('0' + RTRIM(@Month), 2)
>             + RIGHT('0' + RTRIM(@Day), 2)
>         ) AS s
>     );
> END
> GO
>
> SELECT dbo.DateSerial(2008, 4, 21);
>
> SELECT dbo.DateSerial(2008, 4, 31);
> GO
>
> DROP FUNCTION dbo.DateSerial;
> GO
>
> On 7/16/08 5:26 PM, in article p46dnaQiRbMI-uPVnZ2dnUVZ_oTin...(a)comcast.com,
>
>
>
> "JimP" <jpockm...(a)pockmiresolutions.com> wrote:
> > Is there a TSQL function similar to the MS-Access
> > "DateSerial(Year,Month,Day)" function, that will convert values for a year,
> > month and day to a date?- Hide quoted text -
>
> - Show quoted text -

Another method

Declare @Year SMALLINT,
@Month TINYINT,
@Day TINYINT
select @year=2007, @month=12, @day=4
select dateadd(month,@month-1,dateadd(year,@year-1900,0))+@day-1
 |  Next  |  Last
Pages: 1 2
Prev: Capturing SQL Version
Next: Getting the opposite of a join table