Solved

Concating time with date

Posted on 2011-03-25
2
268 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:
Larissa T earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now