Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9564
  • Last Modified:

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!
0
MeetVirginia
Asked:
MeetVirginia
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:

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

where your condition here
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now