Update Time on DateTime Field in SQL Server

I'm working on an ASP.NET application that will update times from a table.  I have a stored procedure to select the data that formats the date as a time value only, for example 16:00.  In the SQL table the value is stored as "11/22/2005 4:00:00 PM".

My problem is that I need to update the time, but keep the same date, in my ASP.NET application.  For example, change "11/22/2005 4:00:00 PM" to "11/22/2005 5:30:00 PM."  I'm having problems passing the values to the table in an update statement.

I hope I'm making sense.  Any help is greatly appreciated as this is very urgent!
MeetVirginiaAsked:
Who is Participating?
 
Ved Prakash AgrawalConnect With a Mentor Database Consultant/Performance ArchitectCommented:

UPDATE TableName
SET DateTimeField = convert(varchar(10),DateTimeField ,120) +' ' +  convert(varchar(8),@timeValue,108)

where your condition here
0
 
Mr_PeerapolCommented:
Your update statement string shoud look like:

'UPDATE your_table SET time_field = CONVERT(DATETIME, CONVERT(VARCHAR(10), time_field, 120) + '' ' + @time_from_asp + ''', 120) WHERE record_id = ' + @id_from_asp
0
 
ralmadaCommented:
Hi MeetVirginia,

you can use this

update yourtable
set yourfield = yourfield + '1:30'

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
RichardCorrieCommented:
try:

create procedure UpdatetoTime
@NewTime as datetime
as
declare
@strNewTime char(5)
set @strNewTime = convert(char(5),@Newtime,114)
update
yourtable
set yourdatefield = cast(convert(varchar(12), yourdatefield, 106) + ' ' + @strNewTime as datetime)
go

/Richard
0
 
udayshankarCommented:
did you try this? datepart is hh

DATEADD ( datepart , number, date )
0
 
MeetVirginiaAuthor Commented:
I'm working on this stuff.  I'll let you all know how it goes.  Thank you!!!
0
 
Gautham JanardhanCommented:
while building the query int asp.net

u could use this

datetime.ToString("MM/dd/yyyy hh:mm:ss");
0
 
MeetVirginiaAuthor Commented:
I'm sorry you guys, I accidently accepted the wrong answer.  While I appreciate all the responses, and they were very helpful during a critical time, the response from ved17nov worked easiest for me.  I have posted the error in Customer Support to have the points allocated to the proper person.
0
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.

All Courses

From novice to tech pro — start learning today.