CODEWITHSUNDEEP
sql-server
prinkpan
prinkpan

Reputation: 2247

SQL convert date stored as varchar to Excel(numeric) format

I used BCP to import data from excel to the database. This caused dates in excel to come up as numbers in the database. However, there were already some records in the database with dd/mm/yyyy & dd/mm/yyyy hh:mm:ss format. My sample data is below:

+---------------------+
|    PcptClassDate    |
+---------------------+
| 01/04/2018 00:00:00 |
| 02/04/2018 00:00:00 |
| 07/04/2018 00:00:00 |
| 28/09/2018          |
| 29/09/2018          |
| 30/03/2018 00:00:00 |
| 30/08/2018          |
| 31/03/2018 00:00:00 |
| 31/08/2018          |
| 43227               |
| 43232               |
| 43233               |
| 43239               |
+---------------------+

I have catered for numeric dates in my code by converting it as follows: DateTime.FromOADate(pcptClassDate) Hence, I now want to convert all dates in this column to a standard Excel(numeric) format. Is there a query I can use to do this or do I need to write a code for that? My database version is "Microsoft SQL Azure (RTM) - 12.0.2000.8 Apr 3 2019 13:27:00"

Upvotes: 0

Views: 664

Answers (1)

Thom A
Thom A

Reputation: 95949

This seems like a really silly thing to do, however, if you really do want to display all your dates as a numerical value, using Excel's numbering, then you could do:

SELECT PcptClassDate,
       CASE WHEN TRY_CONVERT(int,PcptClassDate) IS NOT NULL THEN PcptClassDate
            ELSE DATEDIFF(DAY,'19000101',TRY_CONVERT(date,PcptClassDate,103)) + 2
       END AS ExcelDateNum
FROM (VALUES ('01/04/2018 00:00:00'),
             ('02/04/2018 00:00:00'),
             ('07/04/2018 00:00:00'),
             ('28/09/2018'),
             ('29/09/2018'),
             ('30/03/2018 00:00:00'),
             ('30/08/2018'),
             ('31/03/2018 00:00:00'),
             ('31/08/2018'),
             ('43227'),
             ('43232'),
             ('43233'),
             ('43239'))V(PcptClassDate);

Like I said in the comments though, this seems like a really bad idea; as the "date" 43371 is meaningless to most people.

Upvotes: 2

Related Questions