Importing/formatting MYSQL time date into SQL as char

Question

Importing/formatting MYSQL time date into SQL as char

TryingToLearn

SSCrazy

Points: 2645
More actions
June 6, 2013 at 9:37 am

#278682

i have created a view in MYSQL which is imported into SQL Server. The originating MYSQL table column definition i have an issue with is:
`duration` int(11) NOT NULL default '0', i cannot change this.
the data field looks like this : '27'
one view definition i have created uses
SELECT sec_to_time(`asteriskcdrdb`.`cdr`.`duration`) AS `duration`,
which converts the time '00:00:27' which is the format i need. when i go to import this into SQL Server, i get: Error converting data type DBTYPE_DBTIME to time.
so i use
cast(`asteriskcdrdb`.`cdr`.`duration` as char(8) charset utf8) AS `duration`
which works but does not properly format the field.
The query i use to bring the data over is:
SELECT * FROM OPENQUERY(PHONEDB,'SELECT calls_key, calldate, time, duration, duration_in_decimal, ext, trunk, calltype, io, areacode, phonenum FROM asteriskcdrdb.Conshy_Calls')
I need to get DURATION over to SQL in a char(8) format as 00:00:00.....i have played with all sorts of MYSQL function but I can't seem to convert time format to char.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287024 More actions · Answer 1

Let me first say that I don't know MySQL.

That being said, your field name "duration" as an integer makes me stop and think. I presume that you're storing the duration of a task or event. If that's true, I think it's highly likely that you'll eventually want to add up the total time for something - a day, week, specific event, etc. If my presumptions are correct, I'd like to discourage you from trying to convert it into a string field as you bring it over.

The datetime data type stores a point in time, not a duration of time. This would cause problems with any duration greater than 24 hours.

The string data types (char, varchar, etc.) store strings, which will cause problems when you want to sum them up for any reason.

If you want to store durations, I'd strongly recommend doing so in a numeric data type. Then you can sum, average, min, max, etc. easily without jumping through a bunch of hoops. The conversion would occur in the presentation of the data.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

TryingToLearn SSCrazy Points: 2645 More actions · Answer 2

Thanks, Unfortunately this import works with 20+ other imports so they need to be the same.

i was ale to tweak the import code with

cast(duration as char(8) charset utf8) as duration,

and that worked.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Ed Wagner (6/6/2013)
Let me first say that I don't know MySQL.
That being said, your field name "duration" as an integer makes me stop and think. I presume that you're storing the duration of a task or event. If that's true, I think it's highly likely that you'll eventually want to add up the total time for something - a day, week, specific event, etc. If my presumptions are correct, I'd like to discourage you from trying to convert it into a string field as you bring it over.
The datetime data type stores a point in time, not a duration of time. This would cause problems with any duration greater than 24 hours.
The string data types (char, varchar, etc.) store strings, which will cause problems when you want to sum them up for any reason.
If you want to store durations, I'd strongly recommend doing so in a numeric data type. Then you can sum, average, min, max, etc. easily without jumping through a bunch of hoops. The conversion would occur in the presentation of the data.

Agreed. I've found that the closest approximation is FLOAT if you store datetime durations as something numeric.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)