March 31, 2004 at 9:14 am
Hi, I have a table with a smalldatetime column, but I only need to insert the date, this is to compare in my program only for date and not for date and time.
Let say I have an order table and I will need to store the day when the order was placed, I can't use getdate() cause it will store the date and time. How can I store only the date?
Also, once I have the orderdate with only the date, how can I retrieve for example all the orders placed today?
Right now I am doing this way:
SELECT orderid, ordernumber, orderdate,...
FROM Orders
WHERE datediff(day, orderDate, getdate()) = 0
I just don't want the date to be a parameter of the SP, maybe the PC that calls the SP hasn't the correct date set.
thanks in advance
Kindest Regards,
@puy Inc
March 31, 2004 at 11:09 am
1. You CANNOT save just the date. DATETIME datatype is just that - both the date and time. It is really eight bytes; four bytes are the number of days before or after the default date and the other four bytes are the number of 'ticks' (.333 milliseconds) after midnight.
2. The best thing to do (for your requirement) is to store the date with the default time of 00:00:00 or store it with whatever time is used and then use the front-end to display it how you want.
Here's an example of what you CAN do:
INSERT INTO mytable (mydate)
VALUES '2004-03-31'
SELECT mydate
FROM mytable
returns: 2004-03-31 00:00:00.000
SELECT CONVERT(VARCHAR(10), mydate, 121) AS [My Date]
FROM mytable
returns: 2004-03-31
See the BOL (Books OnLine) for more information about DATETIME/SMALLDATETIME and CONVERT.
And you can do this in a where:
WHERE CONVERT(VARCHAR(10), mydate, 121) = CONVERT(VARCHAR(10), GETDATE(), 121)
-SQLBill
March 31, 2004 at 1:58 pm
By the way, search this site...there's lots of information about DATETIME/SMALLDATETIME. The question you asked, probably gets asked once a week or so.
-SQLBill
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply