Help: SQL select with dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help: SQL select with dates

Author

Topic

GreyKing
Starting Member

1 Post

Posted - 2002-03-23 : 20:55:14

I have a table full of sales records, and among the fields are year, month, and day the represents the date of the sale record.

How can I create an SELECT statement that will only returned the records between specified dates?

SELECT * FROM records
WHERE year > 1999 AND month > 3 AND day > 12 AND
year < 2001 AND month < 6 AND day < 15

Won't work because dates like 2000/2/31 and 2000/7/28 would be considered invalid....

Please help... thanks ahead of time...

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-03-23 : 22:38:59

SELECT * FROM records
WHERE CAST(CAST(Month as char(2))+'/'+CAST(Day as char(2))+'/'+CAST(Year as char(4))as datetime) BETWEEN @begin_date AND @end_date

I would suggest, if possible, to create a datetime or smalldate column in your table and update the column with the sale record date. Then it will make it easier to perform datetime functions.

If you have any questions, please let me know.

Jeremy

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2002-03-25 : 11:42:57

>> I have a table full of sales records [sic], and among the fields [sic]are year, month, and day the represents the date of the sale record [sic]. <<

You still think you are writing Cobol -- files, records and fields are not relational terms or concepts.

1) Your date is split over three columns (NOT fields!! VERY important). A column is a scalar value, not part of a scalar value; most people put too much in a column (i.e. hat size and shoe size as one code), but you put too little!

2) SQL has temporal datatypes which should be used for temporal data; Cobol and other programming languages do not. Your table ought to look something like this.

CREATE TABLE Sales
(sales_receipt_nbr INTEGER NOT NULL PRIMARY KEY,
...
sale_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
CHECK (sale_date =
CAST(CEILING(CAST (sale_date AS FLOAT)) AS DATETIME)),
...);

The CHECK() will assure that the sale_date is set to 00:00:00.00000 in the time part of the temporal value.

In the future, please post DDL.

--CELKO--
Joe Celko, SQL Guru

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources