Re: Problem with using BETWEEN for date range...

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@xxxxxxxxxxxxx> wrote in message
news:Xns97B9F36F1FE35Yazorman@xxxxxxxxxxxx
Tony Rogerson (tonyrogerson@xxxxxxxxxxxxxxxx) 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@xxxxxxxxxxxxx

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


.

Relevant Pages

  • Re: between dates with no time consideration
    ... Columnist, SQL Server Professional ... just the range doesnt declare @start as datetime ... "Tom Moreau" wrote in message ... > "Tom Moreau" wrote in message ...
    (microsoft.public.sqlserver.programming)
  • Re: DateTime, variants and double
    ... SQL Server stores them as paired integers - see BOL ... The table has a field of type DateTime which for various reasons I ... SELECT CAST AS FLOAT) ... This email account is my spam trap so I ...
    (microsoft.public.data.ado)
  • Re: Convert help needed desperately
    ... overflow error converting expression to data type datetime. ... Is my sql server hosed???? ... > the timestamp data type. ...
    (microsoft.public.sqlserver.programming)
  • Re: trouble porting a trivially simple function - with declared variables
    ... CREATE FUNCTION my_max_market_date RETURNS datetime ... DECLARE @mmmd AS datetime; ... SQL Server comes with an extensive Online documentation, ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Problem with using BETWEEN for date range...
    ... time component is misleading that indicates the data value may contain a ... In SQL Server because we don't have a DATE data type we need to store ... And, no, our customers do not settle at midnight. ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)