Excel column does not properly show the date format

Question

I am trying to solve this problem where my excel column does not show the date format properly.

The first three rows are different from the next three rows as I have "double clicked" on the column itself. I am extracting all this data using a batch file where the data is then extracted from my database then changed to a .csv which I can view in excel. I have searched online for solutions but I was told to do this steps:

Select the columns.
Select Data.
Select Text to Columns
Select delimited and finish.

However I want to skip all these steps and immediately print out the date when I launch my batch file. Is there any solutions?

Update:

When using this code, it will print out the date nicely in excel.

select format(createddate,'yyyy-mm-dd hh:mm:ss') from testdb.dbo.company1;

However, without the format, it just screw it up, just like the last 3 data.

Update

I am using this code which return the time only.

select * from testdb.dbo.company1 where datepart(month, CreatedDate) = datepart(month, getdate()) and datepart(day, CreatedDate) < datepart(day, getdate())
and datepart(year, CreatedDate) = datepart(year, getdate())

However this above code returns data just like the last three rows [Image]

I need to put that code and this together.

select format(createddate,'yyyy-mm-dd hh:mm:ss') from testdb.dbo.company1;

What do you mean? I am getting only 00:25.0 when I am expecting to get 01/06/2017 00:25.0 — thompsonrapier
If I'm getting this correctly, you are trying to view/import a CSV file in Excel. What does it look like if you open it in a text viewer? — ttaaoossuuuu
When you open a CSV file in Excel, Excel will try to interpret the values. If it can determine that the value is a date/time, it will display it as a date/time. So, using the format function helps. If that works, why don't you just use that? If you want to learn more about why Excel does not display the date/times correctly for the other three, you need to open the CSV file in something OTHER than Excel and look at the values as they are stored in the CSV. Edit your question and post that data as you can see it in Notepad. — teylyn

ttaaoossuuuu ttaaoossuuuu · Accepted Answer · 2017-06-23T08:20:46

I did some research and it seems that the issue is hard-wired into excel. Upon import from CSV it will try to guess the data format automatically and if your data contains milliseconds it will apply this crappy mm.ss.0 format.

You can mitigate this by stripping milliseconds from your data:

SELECT CONVERT(DATETIME2(0), createddate) FROM ...

Just a reminder, you might have a look into how Excel actually treats and stores dates.

Excel column does not properly show the date format

1 Answers