NVARCHAR to datetime

Question

1.00/5 (1 vote)

See more:

Hi everyone,

I have a field in my DB table which stores date in YYMMDD format, but the data type of field is NVARCHAR (60). I need to display this field as YYYY-MM-DD format. Please help me on this.

What I have tried:

I have tried below syntax but result is showing as same.

CONVERT(nvarchar, [EFT Trans_ Date], 120)

Posted 18-Mar-19 20:58pm

ShanifHassan

Updated 19-Mar-19 1:18am

Add a Solution

Comments

RedDk 19-Mar-19 14:46pm

Here's my two cents worth of TSQL advice. First of all TSQL is just another language used by developers to address hardware through software and SQLServer is one flavor of SQL in the "ching t'ien" of flavors. Noone knows what you have in mind for your software, the task, the underlying math you're using, what the meaning of "translation" is to you so anything they say about what you've qualified as an underlying problem FORMAT, specifically what's stored as NVARCHAR (perhaps knowing there's DATETIME ...) and how CONVERT isn't working for you ... really doesn't matter.

Your DB can store date as anything you like. Plain and simple. As far as all these pundits sounding off beneath here with do do ... use the BOL and start by running sample code found by typing in search parameters related to the thing the message text makes most outstanding. That's typically some verb.

2 solutions

Solution 2

I completely agree with OG. Note, that converting string in format YYMMDD to proper date is not easy job, because a century (age) part is missing. For example:
"880521" may become into: 18880521 or 19880521 or 20880521

Starting from MS SQL Server 2012, you can use: DATEFROMPARTS (Transact-SQL) - SQL Server | Microsoft Docs[^]. Note: you have to pass year, month and day as integer values. So, you have to split string YYMMDD into parts and convert[^] them to proper integer values.

Spliting text is another difficulty, because it depends on SQL server version.
Up to 2012, you need to use SUSTRING()[^] function.
Starting from 2016, you can use STRING_SPLIT (Transact-SQL) - SQL Server | Microsoft Docs[^]

BTW: you can create user-defined function[^], which gets a YYMMDD text and returns a proper date time (based on your century/age logic).

Posted 19-Mar-19 1:18am

Maciej Los

Updated 19-Mar-19 1:19am

v2

Comments

Richard Deeming 21-Mar-19 11:59am

NB: STRING_SPLIT won't help here, as there's no delimiter to split on. :)

Maciej Los 21-Mar-19 14:35pm

:laugh:
Thank you, Richard.

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)

OriginalGriff · Accepted Answer · 2019-03-18T21:39:00

Simple: change your database design.
Never store dates (or anything other than "pure" string data like names and addresses) in NVARCHAR or VARCHAR columns always store data in appropriate datatypes. For integer values, that is INT, for floating point it's FLOAT or DECIMAL. For dates, that is DATE, DATETIME, or DATETIME2.

If your don't, will are making your job a whole load harder as well as leaving yourself open for bad data to enter which becomes increasingly harder to fix as time goes on.

So change your DB design, and migrate your NVARCHAR columns to the correct datatype. IT may seem like a lot of pointless work, but it will save you a huge amount of time and heartache later.