SQL DDL Help. Problem with dates

[Brain]

Hi guys. I've just entered this SQL to insert data into another table.

INSERT INTO OldOrders
SELECT *
FROM Orders
WHERE OrderID IN
(Select Orders.OrderID From Orders INNER JOIN OrdersDetails ON Orders.OrderID=OrdersDetails.OrderID
WHERE (Orders.Date Between '01/01/2008' And '01/02/2009') );

However the problem is with the date or the last line.
What I want is to Insert data from 01/01/2008 to 01/01/2009 from Orders table to OldOrders. However instead of entering a select few it enters all data from the orders table.
Help will be appreciated.

 

[allan57]

1) I believe the inner join between the tables is incorrect ie not needed, as you are transfering records from a live table to history table.

2) You should not name your field 'Date', as this is a reserved word in VBA and could cause you problems in the future.

Try the following code and see if that helps any:

INSERT INTO OldOrders 
SELECT * 
FROM Orders
WHERE Orders.Date Between '01/01/2008' And '01/01/2009';

 

[namliam]

1)
Date is a reserved word, you shouldnt use it as a column.
If you want to you have to put [] around Date.

2)
For dates use ## around the date instead of ''

3)
Not really applicable here, but with dates make sure to use US formats MM/DD/YYYY

4)
Why do the subselect at all when you could simply use:
Select Orders.*
From Orders INNER JOIN OrdersDetails ON Orders.OrderID=OrdersDetails.OrderID
WHERE (Orders.Date Between #01/01/2008# And #01/02/2009#)
?

Good luck !

 

[Brain]

I have tried both ways but I am still getting random results. Rather than the two I want I get 8 records once, and the other two times 3. I've changed the word date to order date and the dates are now in american format. This is the last thing I did:

INSERT INTO OldOrders
SELECT *
FROM Orders
WHERE Orders.OrderDate Between #01/12/2008# And #31/12/2008#;

And this is the result: I want the first two records only as they are between the dates specified but I'm getting a whole load of records.

http://arsenalcolumn.files.wordpress.com/2009/01/rela3.jpg

 

[namliam]

the dates are now in american format. This is the last thing I did:

... #01/12/2008# And #31/12/2008#;

This is NOT US format, US format is MM/DD/YYYY so that should read:

#12/01/2008# And #12/31/2008#;

Furthermore, judging from the formatting the OrderDate is NOT a date field, but a text field (it is left alligned where as date by default would be right alligned)

Having dates as text is BAD! Change the field to a date/time field! and the dates to the US format and your problems will disappear.

 

[Brain]

Okay Thanks very much I will try it.
UPDATE: IT WORKED!!!!!!!! Thanks.
I may have a few more problems so watch this space(!)