Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Update Time on DateTime Field in SQL Server

Posted on 2006-07-13
9
Medium Priority
?
9,550 Views
Last Modified: 2012-06-27
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
Comment
Question by:MeetVirginia
9 Comments
 
LVL 25

Expert Comment

by:Mr_Peerapol
ID: 17099985
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
 
LVL 41

Expert Comment

by:ralmada
ID: 17099993
Hi MeetVirginia,

you can use this

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

0
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 17099998
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 11

Accepted Solution

by:
Ved Prakash Agrawal earned 2000 total points
ID: 17100011

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

where your condition here
0
 
LVL 9

Expert Comment

by:udayshankar
ID: 17100017
did you try this? datepart is hh

DATEADD ( datepart , number, date )
0
 

Author Comment

by:MeetVirginia
ID: 17101152
I'm working on this stuff.  I'll let you all know how it goes.  Thank you!!!
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17115340
while building the query int asp.net

u could use this

datetime.ToString("MM/dd/yyyy hh:mm:ss");
0
 

Author Comment

by:MeetVirginia
ID: 17166910
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

886 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