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

x
?
Solved

Concating time with date

Posted on 2011-03-25
2
Medium Priority
?
279 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

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 ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

873 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