Solved

Remove seconds from hh:mm:ss not working

Posted on 2013-06-07
7
2,278 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

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.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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 the scrolling table in Microsoft Excel using the INDEX function.

739 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