|
|
Posted by Tony Rogerson on 05/04/06 18:03
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.
> You might also want to learn ISO standards and use 'yyyy--mm-dd' for
> date values.
Rich coming from the guy who doesn't use the standard format himself.
2006-05-03 00:00:00.000 should be written 2006-05-03T00:00:00.000
OR
20060503 if you are only specifying the date.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1146749926.099160.51660@i40g2000cwc.googlegroups.com...
>>> So once again - can someone explain why this query returns no records
>>> [sic] when begindate and enddate are specified as 5/3/2006 and there is
>>> clearly trade data with that tradetime? <<
>
> Once again, because there is a DATETIME column which has a time on it.
> If you do not give the time, it defautls to 00:00:00.000 Hrs. This is
> a single point in time. What you wanted to use was
>
> WHERE tradedate BETWEEN '2006-05-03 00:00:00.000' AND '2006-05-03
> 23:59:59.99'
>
> You might also want to learn ISO standards and use 'yyyy--mm-dd' for
> date values.
>
> And do not forget to add constraints to temporal columns in your base
> tables.
>
Navigation:
[Reply to this message]
|