0

I'm writing stored procedure to compare dates but it's not working properly. How can I make it so it compares only the dates but not the time? What I'm trying to do is compare the times and if the Id is null than insert a new entry with the same name but new time. I'm keeping multiple entries with same name but different test time. 

ALTER PROCEDURE [dbo].[UL_TestData]
(
@Name varchar(30),  
@Test_Time smalldatetime,   
@ID INT output
)
AS
Declare  @UpdateTime smalldatetime
SELECT @ID=ID FROM Info_User WHERE Name=@Name AND  UpdateTime= @Test_Time
IF(@ID IS NULL)
 BEGIN
INSERT INTO Info_User (Name, UpdateTime) VALUES (@Name, @UpdateTime)    
 END
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2994144
  • 177
  • 1
  • 1
  • 11

2 Answers2

1

there are a lot of solutions to this depending on what type of DBMS, however here is one:

SELECT @ID=ID FROM Info_User WHERE Name=@Name AND floor(cast(@UpdateTime as float))= floor(cast(@Test_Time as float))

this works because smalldatetime's date is stored a whole numbers, where the time is stored as decimals.

attila
  • 2,219
  • 1
  • 11
  • 15
  • Can I use this to also compare which date is the last updated one? Meaning if 1/12/2014 is greater than 3/12/2013 than do not update the time? – user2994144 Mar 12 '14 at 23:13
  • Yes. But you do not need to convert the small datetime to compare the values. – attila Mar 12 '14 at 23:22
0

I would cast the dates to a plain date which makes this solution independent of implementation details

select @ID=ID 
  from info_user
 where Name = @Name
   and cast (UpdateTime as Date) = Cast(@TestTime as Date)

However, I would either add the date part of the UpdateTime as an additional (calculated) column or split the information into a date and a time part. This makes it much easier to query entries by the plain date.

As a rule of thumb: The type of columns (in general: the table layout) greatly depends on the type of query you usually run against your data.

Edit: As attila pointed out, the date datatype only exists in version 2008 and up

alzaimar
  • 4,572
  • 1
  • 16
  • 30