Need to convert nvarchar in correct datetime format SQL

Question

5.00/5 (1 vote)

See more:

Hello there,

Need your help again.
I am curre tly trying to convert an nvarchar value in correct datetime format. There is a column in SQL Local_time it is nvarchar and it has values like 1160119104041, if i break this down it will be like '1' 'yy' 'mm' 'dd' 'hh' 'mm' ss'.
What i want to display is like 2016-01-01 10:40:41.

Note that in nvarchar value an extra 1 is available i want to remove it when displaying the correct datetime.

I would be really thankful for your help and looking forward to it.

What I have tried:

Searched the web and google but i have found solutions like how yo convert nvarchar to datetime but not with formatting

Posted 20-Oct-16 22:05pm

Faran Saleem

Updated 21-Oct-16 0:12am

#realJSOP

v2

Add a Solution

2 solutions

Solution 2

0) What kind of idiot designs a SQL database to store a datetime as a nvarchar?

1) Which "1" is the one you want to strip? At the beginning, or at the end? Do you even know what the actual purpose of the extra "1" is (why it's there)?

2) The way your nvarchar string is shown, there's no way to parse the string into a datetime with any kind of confidence that the year is accurate because it's not a 4-digit year.

Posted 21-Oct-16 0:10am

#realJSOP

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)

Suvendu Shekhar Giri · Accepted Answer · 2016-10-20T22:18:00

You can use LEFT() and RIGHT() to do so.
Example:

SQL

declare @test varchar(30)
set @test='1160119104041'

select '20'+left(right(@test,LEN(@test)-1),2)+'-'+left(right(@test,LEN(@test)-3),2)+'-'+left(right(@test,LEN(@test)-5),2)+' '+left(right(@test,LEN(@test)-7),2)+':'+left(right(@test,LEN(@test)-9),2)+':'+left(right(@test,LEN(@test)-11),2)

You may want to cast it to DATETIME using CAST(expressio AS DATETIME).

Note: this is just a way. You may still look for better options or suggestions.
You may also need to think, what if year is 98 and you want it to be 1998!

Hope, it helps :)

Update
You can create a function like -

SQL

CREATE FUNCTION GetDateFromVarchar
(
	@InputValue VARCHAR(30)
)
RETURNS DATETIME
AS
BEGIN
	DECLARE @ReturnValue VARCHAR(30)

	SELECT @ReturnValue= '20'+LEFT(RIGHT(@InputValue,LEN(@InputValue)-1),2)+'-'+LEFT(RIGHT(@InputValue,LEN(@InputValue)-3),2)+'-'+LEFT(RIGHT(@InputValue,LEN(@InputValue)-5),2)+' '+LEFT(RIGHT(@InputValue,LEN(@InputValue)-7),2)+':'+LEFT(RIGHT(@InputValue,LEN(@InputValue)-9),2)+':'+LEFT(RIGHT(@InputValue,LEN(@InputValue)-11),2)

	RETURN CAST(@ReturnValue AS DATETIME)

END
GO

Note: It not advised to copy paste code. Check and modify according to your need. I haven't tested this yet but should work.