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

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@xxxxxxxxxxxxx> wrote in message
news:1146749926.099160.51660@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.



.

Relevant Pages

  • Re: Cannot update identity column
    ... course to learn something about SQL Server! ... Using IDENTITY (the proper term for an "autonumber" column). ... a DATETIME column to represent the date ... The short term fix is to use DBCC CHECKIDENT to change the identity ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Server Upgrade wizard
    ... The data transfer fails on the datetime column. ... When get paid, I laugh all the ... I need to copy my data to SQL server BEFORE ... the two methods of transferring and upsizing data to SQL server? ...
    (microsoft.public.access.externaldata)
  • Re: converting SQLTable to Excel Sheet
    ... open it the datetime column and int columns are not getting recognised. ... and Excel is not likely to understand that. ... I don't think you should export to an .xls file. ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ...
    (comp.databases.ms-sqlserver)
  • Re: datetime
    ... Due the the unprecision of the datetime column, which is only in a 3 ms ... precision range (dependend on the way SQl Server evaluates the time ...
    (comp.databases.ms-sqlserver)
  • Re: datetime
    ... > Due the the unprecision of the datetime column, which is only in a 3 ms ... > precision range (dependend on the way SQl Server evaluates the time ...
    (comp.databases.ms-sqlserver)
Loading