composing a reference from fields located in mutiple tables

Consider a situation. There is a table of submitted 'documents'. They have some attributes. There are assignments to process the things, which have a date they were created. Finally there is a price list which specifies the price according to document features and date, so that the assignment to process a document created at different time will have a different cost. In other words, there is a relation
(assignment->document.attribute(s) + assignment.date) -> pricelist.price

Creating relations has the integrity advantages: it is not possible to create an assignment, which price is not defined in the pricelist; precludes the pricelist entry removal if it is referred by any assignments.

Should a view, which combines all the foreign fields into one virtual table, be created to make establishing the reference possible?

Not quite sure I understand the entire request. However, it seems you should be able to enforce referential integrity via Instead Of trigger.

Perhaps, you could give us some sample DDL and desired output to better describe your issue. We might be able to help further then.

|||Normally, you have all the tables interrelated. The reference (a foreign key) points to an object in another table specifying the "container" it belongs to. For instance, many books refer a single author.

Sometimes, you need to establish a complex reference consisting of multiple fields. For instance, a job refers to pricelist. The options in the print job specify a "service id", which has a unique price in the pricelist.

Suppose now that the pricelist can be updated. When job is created, it fixes the latest service cost in int field, the pricelist date. So the cost is uniquely identified by the job options (some fields) and the date. This is a complex key.

What I have faced is that nobody addresses the possibility of having the job attributes fixed in a separate table (say, documents to be processed always have the same settings). A job refers a document, from which the attributes are derived and coupled with the pricelist date identifies the job cost in the pricelist table. Effectively, the complex key is composed from fields located in different tables. A record contains only a part of complex key plus a reference to another entity, which has a rest of the key.

One way to create a relation would be to produce a view joining the key field tables. However, views are not enabled in diagrams. I suppose the reason is because the views are not allowed to participate in data relations.

Actually, I have decided that in my case I do not need to fix the job settings in the referred document, so I'll have all the key fields in one table. Yet, the topic is quite general to be interesting for me and others.

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

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