0

I'm facing a challenge in comparing datetime values in SQL server 2008.

I want to select only those rows from the table 'Plane_Allocation_Table' where the column 'To_Date' which is of type DATETIME is less than today's date and time (which I obtained using GETDATE()). And update two tables depending on the Values the above query returns.

However, the queries I tried don't return any rows.

The last stored procedure I worte looks like :

-- Procedure to Update Total_Allocation in Hanger_Details and Validity of Booking in Plane_Allocation_Details :

CREATE PROCEDURE [dbo].[Update_Total_Allocation_APMS]
AS
DECLARE @now DATETIME
SELECT @now = GETDATE()
UPDATE Hanger_Details
SET Total_Allocation = Total_Allocation - 1
WHERE Hanger_Number IN
(
    SELECT Hanger_Number
    FROM Plane_Allocation_Details
    WHERE To_Date <= @now AND Validity = 'Valid'
)
UPDATE Plane_Allocation_Details
SET Validity = 'Not Valid'
WHERE To_Date <= @now

The comparison of 'To_Date' and '@now' isn't working. Is there any solution to comparing datetime values from a table in a stored procedure?

PS : Please note that I want to make the comparison on the basis of date as well as time. For instance, if the time datetime is '2014-12-20 10:00:00.000' now, and the column value is '2014-12-20 09:59:00.000' then the row must be returned.

Edit1 : I've corrected the typo there. (=< to <=)

kev
  • 58
  • 6
  • Except the possible typo. I would think you are doing it perfectly fine. Would you like to double check everything else again? Maybe check the type of To_Date again? – stripathi Dec 02 '14 at 05:39
  • 1
    What is the data type of To_Date – Veera Dec 02 '14 at 05:42
  • It willl help if you can post a simple CREATE-INSERT-SELECT script that demonstrates your issue. e.g. `CREATE TABLE #1 (To_Date datetime); INSERT #1 VALUES ('2014-12-20 10:00'); SELECT * FROM #1 WHERE To_Date <= GETDATE();` – Anthony Faull Dec 02 '14 at 05:59
  • @AnthonyFaull : What you posted is exactly my issue. The select statement isn't working. – kev Dec 02 '14 at 06:35
  • A simple sample script : `CREATE TABLE sample_table (To_Date datetime); INSERT INTO sample_table VALUES ('2014-12-20 10:00:00.000'); SELECT * FROM sample_table WHERE To_Date <= GETDATE();` – kev Dec 02 '14 at 06:39

1 Answers1

0

You can cast the datetime values to date-only values.

e.g.

DECLARE @today date
SELECT @today = CONVERT(date, GETDATE())

UPDATE Plane_Allocation_Details
SET Validity = 'Not Valid'
WHERE CONVERT(date, To_Date) <= @today
Anthony Faull
  • 17,549
  • 5
  • 55
  • 73
  • Thank you, this works. But, I actually need the comparison to be on the basis of current time as well. – kev Dec 02 '14 at 06:33