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
>

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. :-)

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.|||"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. :-)

comparision of sql server & mysql

whats the difference betwwen mysql and sql server2000 in terms of using
storedprocedures,stored functions and views.> whats the difference betwwen mysql and sql server2000 in terms of using
> storedprocedures,stored functions and views.

What version of MySQL?

--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||MYSql 5.0|||> MYSql 5.0

Well, both MySQL 5 and SQL Server 2000 have views/procedures/functions.

SQL Server 2000 has Check Constraints, which MySQL doesn't.

From SQL Server 2000, it's quite easy to extend the server with
stuff that's accessible from TSQL (like COM objects and so on)
in a programming language that supports it.

MySQL uses the standard PSM (Persistent Stored Module) Syntax for
it's procedures and functions, while SQL Server uses T-SQL, a Microsoft
extension to standard SQL.

Is there anything specific that you want to know?

--
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com