Improve company productivity with a Business Account.Sign Up

x
?
Solved

Concating time with date

Posted on 2011-03-25
2
Medium Priority
?
281 Views
Last Modified: 2012-06-21
Not sure how this happened. Today, at 10:17AM, clicked on a button on a page.

Code passes "date" ONLY to the stored proc. I'm using a telerik control and it wont let me add "time". So, i pass the date, and in the stored proc, i contact date to time...

In this example, "10:17AM" came out as "10:17PM". Not sure why and how but this is my sql below.
declare @updatedate as datetime
set @updatedate ='2011-03-25'

-- getdate() (time at that time) was  2011-03-25 10:17:15.000
select 
          DATEADD(day, 0, DATEDIFF(day, 0, @updatedate)) +  
             DATEADD(day, 0 - DATEDIFF(day, 0, getdate()), getdate())

--not sure how that turned out to be 21:10:15:000

Open in new window

0
Comment
Question by:Camillia
2 Comments
 
LVL 11

Accepted Solution

by:
Lara F earned 2000 total points
ID: 35220679
Not sure either why you have problem - check SQL server time zone??
..But using day  may give you problem if you are close to midnight

I would suggest to change it to

declare @updatedate as datetime
set @updatedate ='2011-03-25'
select dateadd(ms, datediff(ms,@updatedate, getdate()),@updatedate)

1-st parameter in dateadd is ms - milliseconds - change it to sec or minutes, depending on your app requirements
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35224278
it's not make sense to me for what you are trying to do.
if you want to add "Date & Time" of current time, then why not uses getdate() right away?

since if you pass in any date which is not "today", then why try to add time of getdate() ?

unless you will have another parameter @updatetime = '10:17am'
then you may use script as below

declare @UpdateDate as datetime
declare @update_date as varchar(10)
declare @update_time as varchar(11)

set @update_date = '2011-03-25'
set @update_time = '10:17am'
set @UpdateDate = (@update_date + ' ' + @update_time)

select @UpdateDate

Open in new window

0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

586 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question