?
Solved

Concating time with date

Posted on 2011-03-25
2
Medium Priority
?
276 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 11

Accepted Solution

by:
Larissa T 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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

764 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