Solved

Concating time with date

Posted on 2011-03-25
2
270 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PHP installation issues 11 59
Help Required 3 94
SQL Error - Query 6 24
SQL Server Import/Error Wizard error 12 16
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
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.

776 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