Solved

Remove seconds from hh:mm:ss not working

Posted on 2013-06-07
7
1,866 Views
Last Modified: 2013-06-14
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
0
Comment
Question by:jovonn
  • 3
  • 3
7 Comments
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39229421
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
 
LVL 1

Author Comment

by:jovonn
ID: 39229456
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
 
LVL 7

Accepted Solution

by:
Ross Turner earned 250 total points
ID: 39229493
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Author Comment

by:jovonn
ID: 39229592
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39229712
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
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39229739
Now I did not know that cheers for the info Patrick :)
0
 
LVL 1

Author Comment

by:jovonn
ID: 39247787
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now