Sql query for copying the data of nvarchar datatype time to datetime format time

Question

0.00/5 (No votes)

See more:

in my table one column called starttime in varchar(50) datatype format..I added one column called starttime1 of datetime.how to copy data from starttime to starttime1..
can you please help me in writing query.
thanks in advance..

What I have tried:

SQL

alter table website_Tracker alter column Starttime datetime

update  Website_Tracker set starttime1=CONVERT(datetime,starttime,101)


update Website_Tracker
set starttime1 = convert (datetime,Starttime)


UPDATE Website_Tracker
SET starttime1 = CONVERT(NVARCHAR(50),CONVERT(SMALLDATETIME, starttime,103))

Posted 11-Feb-16 21:15pm

Rakhesh baradwaj

Updated 12-Feb-16 2:27am

Maciej Los

v2

Add a Solution

Comments

Jörgen Andersson 12-Feb-16 5:10am

What's the format of the dates in the "starttime" column?

Maciej Los 12-Feb-16 8:28am

What database engine: sql server, MySQL, Oracle, PostgreSQL, other?

2 solutions

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)

Garbel Nervadof · Answer 1 · 2016-02-11T23:13:00

Hi Rakhesh,
Can you post some value of starttime? If the format value is same as datetime format in your SQL Server, there should be no problem with that.

I was trying to reproduce your case with this script, but it works for me.

SQL

DECLARE @tempTable TABLE (
	sDate VARCHAR(50) NULL,
	dtmDate DATETIME NULL
)

INSERT INTO @tempTable(sDate, dtmDate)
SELECT '2016-02-12 17:06:32.273', null

SELECT * FROM @tempTable

UPDATE @tempTable SET dtmDate = sDate

SELECT * FROM @tempTable

Cheers. :)

Richard Deeming · Answer 2 · 2016-02-12T03:51:00

If you're using Microsoft SQL Server 2012 or later, use TRY_PARSE[^]:

SQL

UPDATE Website_Tracker
SET StartTime1 = TRY_PARSE(StartTime As datetime USING 'en-US')

You should also consider using the datetime2[^] data type, rather than the older datetime type. datetime requires 8 bytes, and only supports dates from 1753 onwards. datetime2(0) would only use 6 bytes, and supports the same range of dates as the .NET DateTime type.

SQL

UPDATE Website_Tracker
SET StartTime1 = TRY_PARSE(StartTime As datetime2(0) USING 'en-US')

If you're using a different DBMS, or a version of SQL Server earlier than 2012, then you'll need to tell us what you're using. :)

Sql query for copying the data of nvarchar datatype time to datetime format time

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0