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?


Thanks
LVL 1
jovonnAsked:
Who is Participating?
 
Ross TurnerConnect With a Mentor Management 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

excel
0
 
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  

http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/
0
 
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?
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
jovonnAuthor Commented:
Just tried that so

=text(A1,"hh:mm")

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
0
 
Patrick MatthewsCommented:
jovonn,

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.

Patrick
0
 
Ross TurnerManagement Information Support AnalystCommented:
Now I did not know that cheers for the info Patrick :)
0
 
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.
0
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.

All Courses

From novice to tech pro — start learning today.