Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004
    Posts
    1

    Unanswered: Convert datetime to seconds

    Hello. I have a datetime field that stores data that looks like:
    1899-12-30 00:01:28.000

    Unfortunately this column represents a Call Duration (in seconds). For example, in the above data the call lasted 1 minute and 28 seconds.

    I can't seem to figure out how to either
    1) return only the last eight characters of this field (in excel it would be the equivelent of MID, RIGHT, LEFT). From here I was thinking that I could use CONVERT?
    OR
    2) convert from date/time to int which represents total seconds.

    Any help is greatly appreciated!
    -Kristina

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Where in foo-floggy did you get the 1899-12-30 ?!?! I've seen lots of strange values pop up in applications, but that's a new one to me! There are two different approaches to your problems (one for each problem, of course). They are:
    Code:
    DECLARE @d DATETIME
    SET @d = '1899-12-30 00:01:28.000'
    
    SELECT DateDiff(second, '00:00', Convert(VARCHAR(30), @d, 14))
    ,  Right(Convert(VARCHAR(12), @d, 14), 8)
    -PatP

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    taking the hint that
    in excel it would be the equivelent of MID, RIGHT, LEFT
    you might be using a MS product what are you accessing this database with. I'd be tempted to stick this into a formatting funtion. In Access SQL you can put the function into the SQL to create a derived field in the query, you may be able to do the same or similar depending on the server you are using.

    However it does sound as if the data model is either incorrectly specified or populated. What is it that is setting the day/date component. If that is consistent then you may be OK as you are. However if the vendor decides to change the baseline method between versions (its hardly unkown for suppliers to be so creative) then you could have a problem.

    Personally I'd want to tie down the input so that it was only Hours/minutes/seconds, which I if couldn't get a stable method of storing that in the DB I'd probably resort to storing it as a long integer, and writing an encoding / decoding function to het back to the DD:MM:MMS value you expect to see.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pat, 1899-12-30 is the base date for datetime values supplied without a date component in either sql server or access

    kristina, go with option 2)

    time durations should never be stored as datetimes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    1899-12-30
    Of the two values I've seen used as a base date, that one hasn't ever been a choice. What version are you using?

    -PatP

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    me? i'm using 08.00.0760
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Interesting. The two choices that I've seen are 1900-1-0 and 1904-1-0, which compute as 1899-12-31 and 1903-12-31. If I try to enter 1899-12-30 as a date, neither MS-Excel nor MS-Access recognize it as such, and they treat it as raw text.

    Maybe it is something in the international versions? I'll have to try it on a UK machine if I have a chance tomorrow.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •