Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2002
    Location
    Berlin
    Posts
    72

    Question Unanswered: Query question time calculation

    Hi,

    I get a no. of seconds (like '33450', varchar) for each day and I have a day field (like '19.10.2004', varchar).

    How can I easily convert it into a datetime-field (like 2004-10-19 09:17:50) ?

    Does anybody has an idea ?


  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    This will work if your date is always 10 char long. If not, you will have to inspect for the period for the beginning of the substring function. Once you have that, the dateadd is perfect for adding your seconds to the base date.


    <CODE>
    declare @strdate varchar(10), @sec int
    declare @datevar datetime
    select @strdate = '19.10.2004', @sec = 33450
    select @strdate = substring(@strdate,7,4)+'-'+substring(@strdate,4,2)+'-'+substring(@strdate,1,2)
    select @datevar = @strdate
    select @datevar = dateadd (ss,@sec,@datevar)
    select @datevar
    </CODE>

    <OUTPUT>
    ------------------------------------------------------
    2004-10-19 09:17:30.000
    (1 row(s) affected)
    </OUTPUT>

  3. #3
    Join Date
    Jan 2002
    Location
    Berlin
    Posts
    72
    datetime - field overflow by using your query ?

  4. #4
    Join Date
    Jan 2002
    Location
    Berlin
    Posts
    72
    Thx. tomh53

    just need to exchange your substring.

    Bye

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    The code ran fine on my box in Query Analyzer ... sql server 2000, sp3a on Win 2K sp 4.

    There are only 86400 seconds in a day.

    Post what you ran and exact output.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Tom's code works nicely (as posted) on my machine too.

    -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
  •