Composing a date from date parts

I have three date parts namely,
Year
Month
Date/Day
as integer values stored in one column each in a table in a SQL Server
2000 database. I need a function to serialize/compose/create a datetime
type out of them so I could use that in a query (pseudosyntax) as
below:
SELECT CreateDate(iYear, iMonth, iDay) As TheDateIWant FROM ThatTable
and then later on, I probably want to use an aggregation/computation on
that like, the MAX function, may be:
SELECT MAX(CreateDate(iYear, iMonth, iDay)) As TheDateIWant FROM
ThatTable
Thanks!
DECLARE @.Year int
DECLARE @.Month int
DECLARE @.Day int
SET @.Year = 2005
SET @.Month = 02
SET @.Day = 27
SELECT
CAST(
CAST(@.Year AS char(4))
+RIGHT('0' + CAST(@.Month AS varchar(2)), 2)
+RIGHT('0' + CAST(@.Day AS varchar(2)), 2)
AS datetime)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<aroraamit81@.gmail.com> wrote in message
news:1132746277.481717.168850@.g49g2000cwa.googlegr oups.com...
>I have three date parts namely,
> Year
> Month
> Date/Day
> as integer values stored in one column each in a table in a SQL Server
> 2000 database. I need a function to serialize/compose/create a datetime
> type out of them so I could use that in a query (pseudosyntax) as
> below:
>
> SELECT CreateDate(iYear, iMonth, iDay) As TheDateIWant FROM ThatTable
>
> and then later on, I probably want to use an aggregation/computation on
> that like, the MAX function, may be:
>
> SELECT MAX(CreateDate(iYear, iMonth, iDay)) As TheDateIWant FROM
> ThatTable
>
> Thanks!
>
|||Here is another solution. This will work only with 4 digit year.:
declare @.year int, @.month int, @.day int
set @.year = 2005
set @.month = 11
set @.day = 23
select cast(convert(char(8),@.year * 10000 + @.month * 100 + @.day) as datetime)
"aroraamit81@.gmail.com" wrote:

> I have three date parts namely,
> Year
> Month
> Date/Day
> as integer values stored in one column each in a table in a SQL Server
> 2000 database. I need a function to serialize/compose/create a datetime
> type out of them so I could use that in a query (pseudosyntax) as
> below:
>
> SELECT CreateDate(iYear, iMonth, iDay) As TheDateIWant FROM ThatTable
>
> and then later on, I probably want to use an aggregation/computation on
> that like, the MAX function, may be:
>
> SELECT MAX(CreateDate(iYear, iMonth, iDay)) As TheDateIWant FROM
> ThatTable
>
> Thanks!
>
|||Or for fun
DECLARE @.y INT,@.m INT,@.d INT
SET @.y=2005
SET @.m=2
SET @.d=27
select cast(rtrim(@.y*10000+@.m*100+@.d) as datetime)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uK5YPUC8FHA.3880@.TK2MSFTNGP12.phx.gbl...
> DECLARE @.Year int
> DECLARE @.Month int
> DECLARE @.Day int
> SET @.Year = 2005
> SET @.Month = 02
> SET @.Day = 27
> SELECT
> CAST(
> CAST(@.Year AS char(4))
> +RIGHT('0' + CAST(@.Month AS varchar(2)), 2)
> +RIGHT('0' + CAST(@.Day AS varchar(2)), 2)
> AS datetime)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <aroraamit81@.gmail.com> wrote in message
> news:1132746277.481717.168850@.g49g2000cwa.googlegr oups.com...
>
|||Thanks a tonne, mate.
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uK5YPUC8FHA.3880@.TK2MSFTNGP12.phx.gbl...
> DECLARE @.Year int
> DECLARE @.Month int
> DECLARE @.Day int
> SET @.Year = 2005
> SET @.Month = 02
> SET @.Day = 27
> SELECT
> CAST(
> CAST(@.Year AS char(4))
> +RIGHT('0' + CAST(@.Month AS varchar(2)), 2)
> +RIGHT('0' + CAST(@.Day AS varchar(2)), 2)
> AS datetime)
>
...or
select dateadd(d, @.day-1, dateadd(m, @.month-1, dateadd(yyyy, @.year % 1900,
'19000101')))
|||Very nice Uri Dimant
Madhivanan
|||On Wed, 23 Nov 2005 09:15:27 -0500, Raymond D'Anjou wrote:

>"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:uK5YPUC8FHA.3880@.TK2MSFTNGP12.phx.gbl...
>...or
>select dateadd(d, @.day-1, dateadd(m, @.month-1, dateadd(yyyy, @.year % 1900,
>'19000101')))
>
Oh boy.
We're all barely recovered from the shocks and horrors of Y2K, and now
you are already laying foundation for a huge Y3K8 problem.
:-)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:jg9co1p6aj6ho6e5ijar3nbi79dtmk06rl@.4ax.com...
> Oh boy.
> We're all barely recovered from the shocks and horrors of Y2K, and now
> you are already laying foundation for a huge Y3K8 problem.
> :-)
> Best, Hugo
People wrote code in the 80s without any thought of the year 2000.
At least my code is good for another 1795 years.
Hopefully, I won't be around to see the problems. :-)