|
|
Posted by Tony Rogerson on 05/05/06 10:27
> All our settledate columns are datetime, or more precisely aba_date,
> which is a datetime with a rule bound to that screams blue murder if
> you try to use anything else than 00:00:00.000 for the time porttion.
>
> And, no, our customers do not settle at midnight.
Presumeably you also have a computed column that strips away the time
component so only the date gets passed back to the client?
To quote celko, using datetime to hold a pure date is really a kludge ;),
seriously though, even though you have a check constraint to prevent times
over than midnight, it shouldn't have a time at all and any query result
should not contain a time component, it ensinuates that settment date is not
a date but actually a date with a time, so to the application developer or
third party vendor accessing your webservice etc... it might well appear
that trades should be settled by midnight.
All the trading systems I've worked with ALL use the integer data type to
hold pure dates.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns97B9F36F1FE35Yazorman@127.0.0.1...
> Tony Rogerson (tonyrogerson@sqlserverfaq.com) writes:
>> Yes but hang on a minute, what if you don't want a time, holding the
>> time component is misleading that indicates the data value may contain a
>> time between 00:00:00 and 23:59:59, being 00:00:00 means the trade was
>> done at midnight which may well not be true, consider settment date
>> where the trade needs to settle on a particular day, not by midnight but
>> sometime on that day.
>>
>> In SQL Server because we don't have a DATE data type we need to store
>> dates as an integer in the form yyyymmdd which is unfortunate but the
>> reality.
>
> Only if you like to throw out babies and bathtubs simultaneously.
>
> All our settledate columns are datetime, or more precisely aba_date,
> which is a datetime with a rule bound to that screams blue murder if
> you try to use anything else than 00:00:00.000 for the time porttion.
>
> And, no, our customers do not settle at midnight.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|