How to update a time part of datetime data..?

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

C#

ASP.NET

, +

In my database i have a table with this type of data

C#

1	Tejas	2011-09-19 15:20:08.200
2	Ankit	2011-09-19 15:21:22.547
3	Jayesh	2011-09-19 15:22:22.300
4	Chirag	2011-09-19 15:28:22.102
5	Sagar	2011-09-19 15:32:15.540

Now My Question is that i want to update a time part of the date stored in the above data like if

C#

2011-09-19 15:20:08.200

is make to

C#

2011-09-19 00:00:00.000

means the date part will remain same but the time will be changed.

i want to do it for all with only one query if it is possible

Posted 18-Sep-11 23:57pm

Tejas Vaishnav

Updated 19-Sep-11 20:57pm

v3

Add a Solution

5 solutions

Solution 3

SQL

update theTable
   set theDate
     = dateadd(hh, 8, testDate)
where testDate = dateadd(day, datediff(day, 0, testDate), 0)

or

SQL

UPDATE yourtable SET yourcolumn=concat(date(yourcolumn), ' 12:00:00') WHERE Id=yourid;

Posted 19-Sep-11 0:06am

Anuja Pawar Indore

Comments

Tejas Vaishnav 19-Sep-11 6:25am

Not Working it will result in

1900-01-01 20:00:00.000

or UPDATE yourtable SET yourcolumn=concat(date(yourcolumn), ' 12:00:00') WHERE Id=yourid; is not working in sqlserver

Anuja Pawar Indore 19-Sep-11 6:41am

DECLARE @theDate as datetime
DECLARE @testDate as datetime
Set @testDate='2011-09-19 15:20:08.200'
select @theDate=dateadd(hh, 8, @testDate)
print @theDate

O/P----Sep 19 2011 11:20PM

Anuja Pawar Indore 19-Sep-11 6:45am

Tejas pls refer this article, hope will help you

Anuja Pawar Indore 19-Sep-11 6:57am

http://www.codeproject.com/KB/database/SQL2005_time_zones.aspx

Solution 2

Hi,

I tried it just now in c#.My line to change that as your requirement is

C#

TextBox1.Text = TextBox1.Text.Substring(0, 10) + " 12:00:00.000 ";

Here Textbox1 means your database value.

You can try it in directly in sql also with some changes.

I hope this your correct answer.

Posted 19-Sep-11 0:05am

Muralikrishna8811

Solution 1

some solutions are given here: http://www.bigresource.com/Tracker/Track-ms_sql-KjNEjh0W/[^]

Posted 19-Sep-11 0:04am

Herman<T>.Instance

Solution 4

Consider using something like this:
DateTime dt = DateTime.Now - DateTime.Now.TimeOfDay;

Sorry maybe I didn't get ur point. U need that in MS SQL stored procedures or views?

Posted 19-Sep-11 0:32am

Babak Mahmoudi

Updated 19-Sep-11 0:34am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tejas Vaishnav · Accepted Answer · 2011-09-19T20:33:00

This is the Solution what i got for updating the time part of the date time column

SQL

DROP TABLE TestTable
CREATE TABLE TestTable(
	ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	FirstName NVARCHAR (50) NULL,
	Testdate datetime NULL
)
Truncate TABLE TestTable

INSERT INTO TestTable VALUES ('Tejas',GETDATE())
INSERT INTO TestTable VALUES ('Ankit',GETDATE())
INSERT INTO TestTable VALUES ('Jayesh',GETDATE())
INSERT INTO TestTable VALUES ('Chirag',GETDATE())
INSERT INTO TestTable VALUES ('Sagar',GETDATE())
INSERT INTO TestTable VALUES ('Ronak',GETDATE())
INSERT INTO TestTable VALUES ('Jayesh',GETDATE())
INSERT INTO TestTable VALUES ('Ashish',GETDATE())
INSERT INTO TestTable VALUES ('Rumit',GETDATE())
INSERT INTO TestTable VALUES ('Vijiyant',GETDATE())

-- Selecting all the record from the Table
SELECT * FROM TestTable
--out put
--1	Tejas	 2011-09-20 12:22:48.637
--2	Ankit	 2011-09-20 12:22:48.637
--3	Jayesh	 2011-09-20 12:22:48.637
--4	Chirag	 2011-09-20 12:22:48.637
--5	Sagar	 2011-09-20 12:22:48.637
--6	Ronak	 2011-09-20 12:22:48.637
--7	Jayesh	 2011-09-20 12:22:48.637
--8	Ashish	 2011-09-20 12:22:48.637
--9	Rumit	 2011-09-20 12:22:48.637
--10	Vijiyant 2011-09-20 12:22:48.637

--Logic to Update a Time Part of the Datetime column
DECLARE @ID INT;
DECLARE @ROW INT;
SET @ROW = (SELECT COUNT(1) FROM TestTable)
SET @ID = 0
WHILE (@ID < @ROW)
BEGIN
SET @ID += 1;
UPDATE TestTable SET Testdate = (select CONVERT(datetime, convert(varchar(10),(SELECT Testdate FROM TestTable WHERE ID = @ID) ,101)) ) WHERE ID = @ID
END
-- Selecting all the record from the Table
SELECT * FROM TestTable

--out put
--1	 Tejas	 2011-09-20 00:00:00.000
--2	 Ankit	 2011-09-20 00:00:00.000
--3	 Jayesh	 2011-09-20 00:00:00.000
--4	 Chirag	 2011-09-20 00:00:00.000
--5	 Sagar	 2011-09-20 00:00:00.000
--6	 Ronak	 2011-09-20 00:00:00.000
--7	 Jayesh	 2011-09-20 00:00:00.000
--8	 Ashish	 2011-09-20 00:00:00.000
--9	 Rumit	 2011-09-20 00:00:00.000
--10   Vijiyant  2011-09-20 00:00:00.000