Click here to Skip to main content
14,028,443 members
Rate this:
 
Please Sign up or sign in to 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
Updated 19-Mar-19 1:18am
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.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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.
   
Comments
Maciej Los 19-Mar-19 6:56am
   
Oh, no! Another 5!
MadMyche 19-Mar-19 7:03am
   
Ditto
Rate this: bad
 
good
Please Sign up or sign in to vote.

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).
   
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.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web03 | 2.8.190419.4 | Last Updated 19 Mar 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100