Solved

Date conversion in sql server 2012

Posted on 2016-11-18
6
45 Views
Last Modified: 2016-12-27
Hello: I need to convert date stored in a sql server table/column as 20161116 13:49:43.81
to dd/mm/yyyy that can be sorted in Excel. The date column is nvarchar(23), null and encompass in a select statement.
Any feedback appreciated.  

Thanks,

Rm103
0
Comment
Question by:rm103
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 41893186
You should be able to export to Excel as is. Did you even tried?
0
 

Author Comment

by:rm103
ID: 41893235
yes
0
 
LVL 52

Expert Comment

by:_agx_
ID: 41893300
What was the result?

>> The date column is nvarchar(23),
Dates should be stored as dates, not strings.
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 65

Expert Comment

by:Jim Horn
ID: 41893344
This works on my 2008R2 box to convert to a valid datetime
Declare @dt nvarchar(23) = '20161116 13:49:43.81'
SELECT CAST(LEFT(@dt, 8) + ' ' + RIGHT(@dt, 11) as datetime) 

Open in new window

To convert to a varchar formatted as dd/mm/yyyy in SQL Server
Declare @dt nvarchar(23) = '20161116 13:49:43.81'
SELECT convert(varchar, CAST(LEFT(@dt, 8) as DATE) ,103)

Open in new window

See SQL Server Date Styles (formats) using CONVERT() for handy conversion functions.

.. or you can just pass the value as a date and let Excel format it however you wish.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 41893454
Since it's already a string, I would leave it a string, no need to risk a conversion to date:

SELECT
    date_column AS original_date_column,
    STUFF(LEFT(date_column, 4), 1, 0, SUBSTRING(date_column, 7, 2) + '/' + SUBSTRING(date_column, 5, 2) + '/') AS date_ddmmyyyy
FROM (
    VALUES('20161116 13:49:43.813')
) AS test_data(date_column)
0
 

Author Closing Comment

by:rm103
ID: 41893811
This worked perfectly.

Thanks!!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question