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.googlegroups.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.googlegroups.com...
>|||Thanks a tonne, mate.|||> 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)
Nevermind how sick and twisted that is, but just for fun clarification,
that's not portable or future-proof, correct? It would seem to me that if
MS ever changes the underlying way that dates are stored, this would break.
Right?
--
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||Mike
> MS ever changes the underlying way that dates are stored, this would
> break.
What does make think so? As as I know ,dates are stored in the same way in
SQL Server 2005 too.

> that's not portable or future-proof, correct?
It is just mathematics, that's all
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:OvEFlUD8FHA.2576@.TK2MSFTNGP12.phx.gbl...
> Nevermind how sick and twisted that is, but just for fun clarification,
> that's not portable or future-proof, correct? It would seem to me that if
> MS ever changes the underlying way that dates are stored, this would
> break. Right?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||>> break.
> What does make think so? As as I know ,dates are stored in the same way in
> SQL Server 2005 too.
>
> It is just mathematics, that's all
I dunno, it just sounds dangerous. Sort of like the same way that C
programmers use bizarre pointer arithmetic to iterate over an array instead
of just iterating over the array like a normal human would.
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||"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')))|||C programmers are not normal humans.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eUkQKgD8FHA.1140@.tk2msftngp13.phx.gbl...
> I dunno, it just sounds dangerous. Sort of like the same way that C
> programmers use bizarre pointer arithmetic to iterate over an array
> instead of just iterating over the array like a normal human would.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>