Solved

Remove seconds from hh:mm:ss not working

Posted on 2013-06-07
7
1,915 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 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

23 Experts available now in Live!

Get 1:1 Help Now