• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3055
  • Last Modified:

Remove seconds from hh:mm:ss not working

Hi Guys, this is odd but I have a CSV which needs to be uploaded into a sql database that is designed so it only see's the time column format as hh:mm

Problem is that on converting the spreadsheet to CSV the formula bar is showing the time format as hh:mm:ss even though in the time column it just shows as hh:mm

This is causing errors with the upload in sql due to the two extra zero's being placed at the end. Changing cell format to text, general, custom hasn't worked any ideas?

  • 3
  • 3
1 Solution
Ross TurnerManagement Information Support AnalystCommented:
if this is a one off....

I would just upload it into a temporary table of you creation and just do the manipulations on it in sql  e.g datefield as a varchar... and convert it into the format / data type you require

Then you do a simple insert - select to move your data  

jovonnAuthor Commented:
Thanks, wish it was but we have a number of CSV files that are all in the same format with the time column issue at the moment and I am not a sql programmer. I was hoping there was a way in the CSV to remove?
Ross TurnerManagement Information Support AnalystCommented:
try this in excel
=TEXT(A1,"dd/mm/yyyy hh:mm")

it converts it to the type your after without the secs then you could copy and paste values over the top of your original

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

jovonnAuthor Commented:
Just tried that so


which when saved as a CSV file still gives me

hh:mm:ss in the formula bar? try it..

ie 12:15:00 I just need it to show 12:15
Patrick MatthewsCommented:

The formula approach RossTurner provided will work just fine.  In fact, I just tested it on my own computer, and it worked beautifully.

The trick, of course, is to not reopen the file in Excel.  Open it in Notepad instead.

When you open a CSV file in Excel, Excel will try to be helpful by converting anything that looks like a date/time value into a date/time value.  While that is usually what you want, it will not always be what you want.

In any event, I am puzzled as to why you are having difficulties.  SQL Server should be more than capable of handling date/time values with a seconds part.

Ross TurnerManagement Information Support AnalystCommented:
Now I did not know that cheers for the info Patrick :)
jovonnAuthor Commented:
Sorry for the late reply guys. That did work when I tested it again so thanks for the pointing me in the right direction.

Patrick, we're using an old SQL based system which I'm not sure how it has been coded. Uploading what would be a very simple CSV file produces the following error

2147217913 [Microsoft][odbc sql server driver][Sql server] The conversion of a char data type to a datetime data type  resulted in an out of range datatime

I've been told that the file should only contain hh:mm format which it does but then gives the above error. I assumed it was down to extra 00 being added.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now