Click here to Skip to main content
15,305,430 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a stored procedure :-
SQL
CREATE procedure St_Proc_GetTimeEntryID            
@userID int,            
@timeEntryID int output            
as begin             
    set nocount on;      
    SET @timeEntryID=0      
    DECLARE @TEMP INT       
    SET @TEMP=0        
    SELECT @TEMP=ProductionTimeEntryID            
    FROM    production            
    WHERE   ProductionTimeEntryID =             
        (SELECT MAX(ProductionTimeEntryID)             
            FROM production            
            where UserID=412             
            and (CalendarDate <= (select GETDATE()))            
            and IsTaskCompleted=1 )      
            BEGIN      
                SET @timeEntryID=@TEMP      
            END      
END
Here CalendarDate is column which containing Date As 06/26/201212:00PM format . I want to compare the date part only with system date part (06/26/2012 = 06/24/2012) in my subquery which is
SQL
(SELECT MAX(ProductionTimeEntryID)             
            FROM production            
            where UserID=412             
            and (CalendarDate <= (select GETDATE()))            
            and IsTaskCompleted=1 )
Please guide me what modification i ll do to get the result.
Posted

There are many, many ways to do this. One easy way is to do
SQL
SELECT CONVERT(Date, GetDate())


This drops the time off.

You could also do
SQL
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

which sets the time to 00:00:00.
   
SQL
alter procedure St_Proc_GetTimeEntryID            
@userID int,            
@timeEntryID int output            
as begin             
set nocount on;      
SET @timeEntryID=0      
DECLARE @TEMP INT       
SET @TEMP=0        
SELECT @TEMP=ProductionTimeEntryID            
FROM    production            
WHERE   ProductionTimeEntryID =             
(SELECT MAX(ProductionTimeEntryID)             
 FROM production            
  where UserID=412             
    and ( CONVERT(VARCHAR(8), CalendarDate, 1) )  = CONVERT(VARCHAR(8), GETDATE(), 1)            
    and IsTaskCompleted=1 )      
    BEGIN      
    SET @timeEntryID=@TEMP      
    END      
    END
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900