CODEWITHSUNDEEP
sqlexcel
thompsonrapier
thompsonrapier

Reputation: 165

Excel column does not properly show the date format

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

enter image description here

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:

  1. Select the columns.
  2. Select Data.
  3. Select Text to Columns
  4. 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;

Upvotes: 0

Views: 152

Answers (1)

ttaaoossuu
ttaaoossuu

Reputation: 7884

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.

Upvotes: 0

Related Questions