[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3072
  • Last Modified:

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
0
mcdermon
Asked:
mcdermon
  • 4
  • 3
  • 2
  • +1
1 Solution
 
OtanaCommented:
Could you show us your code please?
0
 
ShogunWadeCommented:
yep,  we will need to see your code to help resolve this one
0
 
mcdermonAuthor Commented:
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
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
OtanaCommented:
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?
0
 
ShogunWadeCommented:
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.
0
 
mcdermonAuthor Commented:
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?
0
 
Jan FranekCommented:
I think, that problem is, that your @CloseTime contains also date part. Some front-end applications (for example Access) will incorrectly set this date part to date, that is used as "zero" in this application, not the date used as "zero" in MS SQL.

To concatenate date and time without influence of date part of @CloseTime I recommend this:

select @CloseDateTime = dateadd( hour, datepart( hour, @CloseTime ), dateadd( minute, datepart( minute, @CloseTime ), dateadd( second, datepart( second, @CloseTime ), @CloseDate )))

If your @CloseDate can also contain time part, you can remove it using: convert( datetime, convert( varchar(10), @CloseDate, 101 )), so the most "waterproof" code will be:

select @CloseDateTime = dateadd( hour, datepart( hour, @CloseTime ), dateadd( minute, datepart( minute, @CloseTime ), dateadd( second, datepart( second, @CloseTime ), convert( datetime, convert( varchar(10), @CloseDate, 101 )))))
0
 
mcdermonAuthor Commented:
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
0
 
OtanaCommented:
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.
0
 
Jan FranekCommented:
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 )
0
 
mcdermonAuthor Commented:
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
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now