asked on

Date Time Concatenation

I have a stored procedure in which I pull two values from a table. These are both datetime fields one being the date and the other being the time. In my stored procedure I would like to compare the result of concatenating these two fields with todays date. My problem is that each time I concatenate the date and time it deletes two days from the result e.g.

I am looking for 07/05/2005 12:00:00

but it returns

05/05/2005 12:00:00

It makes no sense. Please help!! Thanks,

mcdermon

Otana

Could you show us your code please?

ShogunWade

yep, we will need to see your code to help resolve this one

mcdermon

ASKER

Thanks for answering, this is the code:-

ALTER PROCEDURE sp_Test

      (
            @STR1 bigint,
            @STR2 varchar(20),
            @AppSuccess int OUTPUT,
            @testDate DateTime OUTPUT
      )

AS
BEGIN

DECLARE @CloseDate as DateTime
DECLARE @CloseTime as DateTime
SELECT @CloseDate = (SELECT CONVERT (DateTime,[Closing Date],103) FROM Table_Name WHERE ([Number1] = @STR2))
SELECT @CloseTime = (SELECT CONVERT (DateTime,[Closing Time],108) FROM Table_Name WHERE ([Number1] = @STR2))

IF @CloseDate + ' ' + @CloseTime<=GetDate()
      BEGIN
      UPDATE
            Table1
      SET
            Status = 'No',Date_Completed = GetDate()
      WHERE (STR1 = @STR1) AND (STR2 = @STR2)
      SELECT @AppSuccess = 1
      SELECT @TestDate = @CloseDate+@CloseTime
      END
ELSE
      BEGIN
      UPDATE
            Table1
      SET
            Status = 'Yes',Date_Completed = GetDate()
      WHERE (STR1= @STR1) AND (STR2 = @STR2)
      SELECT @AppSuccess = 0
      SELECT @TestDate = @CloseDate+@CloseTime
      END

END

mcdermon

Otana

I can't see anything wrong with it. Are you sure you have the correct date in your table? And that your conditions are right?

ShogunWade

Asside from the rather verbose way you are doing this, I cant see an issue either.

personally i would add date and time together like this:

SELECT @Close=[Closing Date] + [Closing Time] FROM FROM Table_Name WHERE [Number1] = @STR2

or better still, either store date and time together in one field, or use a view to give you what you want.

mcdermon

ASKER

I know for certain that the values in the tables are right. I am not sure what to do now. This could not be more important. Any other ideas?

ASKER CERTIFIED SOLUTION

Jan Franek

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mcdermon

ASKER

Shogunwade,
You'll have to forgive me. I did have it like that originally but I have been trying so many ways to try and get the correct result that it is now a bit all over the place. I can't combine the two into one field as this is done by another system, which I cannot change. I am just flummoxed by the fact that it is two days every time. Does this have any significance?

mcdermon

Otana

Try pasting your code in query analyzer, and setting values for your variables at the top. Then put a select after every line where you assign a value. When you run your code, you can check where something goes wrong.

Jan Franek

Another way is to explicitly remove unwanted parts:

select @CloseDateTime = convert( datetime, convert( varchar(10), @CloseDate, 101 ) + ' ' + convert( varchar(10), @CloseTime, 108 ))

However - if you use only one front-end application and the difference is always the same, you can just add 2 days offset:

dateadd( day, 2, @CloseDate + ' ' + @CloseTime )

mcdermon

ASKER

Thankyou so much. I used this:-
select @CloseDateTime = dateadd( hour, datepart( hour, @CloseTime ), dateadd( minute, datepart( minute, @CloseTime ), dateadd( second, datepart( second, @CloseTime ), convert( datetime, convert( varchar(10), @CloseDate, 101 )))))

and it worked a treat. I am not saying I understand completely why this makes a difference, but thankyou anyway.

mcdermon