Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Proprietary date and time formats

    So I am having some fun deciphering some "date" and "time" values in a database...
    It's probably best if I just show you the values and you can judge for yourself, so here's something you can play with
    Code:
    DECLARE @ugh table (
       result datetime
     , sdate  int
     , stime  int
    )
    
    INSERT INTO @ugh (result, sdate, stime)
              SELECT '2007-07-30 16:58:17.000', '131532574', '272240896'
    UNION ALL SELECT '2009-01-22 07:55:33.000', '131662102', '121053440'
    UNION ALL SELECT '2007-07-11 14:31:22.000', '131532555', '236918272'
    UNION ALL SELECT '2009-06-18 12:33:33.000', '131663378', '203497728'
    UNION ALL SELECT '2009-11-19 15:52:33.000', '131664659', '255074560'
    UNION ALL SELECT '2009-09-22 07:19:04.000', '131664150', '118686720'
    UNION ALL SELECT '2008-07-02 14:56:23.000', '131598082', '238556928'
    UNION ALL SELECT '2008-06-05 08:21:33.000', '131597829', '135602432'
    UNION ALL SELECT '2009-04-27 10:36:27.000', '131662875', '170138368'
    UNION ALL SELECT '2008-03-04 13:01:55.000', '131597060', '218183424'
    The column "result" is the actual datetime value, "sdate" is the date portion and "stime", well, you guessed it, is the time portion.

    I have uncovered a scalar UDF that gives me the actual datetime based on the two integers but it is obviously a complete dog and so I need to re-write it for better perf (I'm thinking TVF?).

    Here's the [tidied up] formula for converting the values that I have found
    Code:
    DECLARE @year  int
          , @month int
          , @day   int
          , @h     int
          , @m     int
          , @s     int
    
    SELECT @year  = Floor(@date / 65536)
         , @month = Floor((@date - (@year * 65536)) / 256)
         , @day   = Floor(@date - (@year * 65536) - (@month * 256)
    
         , @h = Floor(@time / 16777215)
         , @m = Floor((@time - (@h * 16777215)) / 65535)
         , @s = Floor(((@time - (@h * 16777215)) - (@m * 65535)) / 255)
    
    SELECT Convert(varchar(4), @year)
         + '-'
         + Convert(varchar(2), @month)
         + '-'
         + Convert(varchar(2), @day)
         + ' '
         + Convert(varchar(2), @h)
         + ':'
         + Convert(varchar(2), @m)
         + ':'
         + Convert(varchar(2), @s)
    I hope that's enough information, if not just ask and I'll provide the necessary detail!

    Appreciate any help
    George
    Home | Blog

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I assume a straight conversion to inline TVF you can do with your eyes shut right? So you are looking for some funky awesomeness instead?
    BTW - the outputted date will fail if your language is set to British which leads me on to the obvious question - are you working (phhhhtaaaaa) US English?

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm actually a bit confused about the TVF conversion - I am making wild assumptions about it improving performance by converting it which are founded on hear-say rather than proof


    The outputted date is yyyy-mm-dd? Okay, well it should be but obviously the use of varchar means that it might not always have a two figure month or day value...
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I am even considering creating a table that maps actual datetime date values to their integer equivalents (and same for time) and then I can just perform simple joins...

    But that seems so inelegant and over the top.
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Or perhaps leaving it as is and converting the values I need to put in the WHERE clause from datetimes to this proprietary format..?

    This will be fine for the dates (that's fine for the WHERE clause) and should certainly help in the initial perf issues

    But I *need* to display these rubbishy integers as proper datetimes in my end resultset...
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No - an inline TVF will be much faster.

    Code:
    SET LANGUAGE US_English
    
    SELECT  CAST('2010-12-31 12:00:00' AS DATETIME)
    
    SET LANGUAGE British
    
    SELECT  CAST('2010-12-31 12:00:00' AS DATETIME)
    ISO format is '2010-12-31T12:00:00'

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    To convert from datetime to this silly format is actually really easy
    Code:
    SELECT sdate
         , DatePart(yy, result) * Power(2, 16)
         + DatePart(mm, result) * Power(2, 8)
         + DatePart(dd, result) As calc_date
         , stime
         , DatePart(hh, result) * Power(2, 24)
         + DatePart(mi, result) * Power(2, 16)
         + DatePart(ss, result) * Power(2, 8) As calc_time
    FROM   @ugh
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Forgot about the T, good shout.
    Also works fine without the T if you remove the hyphens (-)...
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think this is probably the most straight forward algorithm there could be:
    Code:
    SELECT  result
          , da_date     = DATEADD(d, sdate % (65536 * (sdate/ 65536) + (sdate % (65536 * (sdate/ 65536)) / 256) * 256) - 1, DATEADD(m, sdate % (sdate/ 65536) / 256 - 1, DATEADD(yy, sdate / 65536 - YEAR(0), 0)))
    FROM    @ugh

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I like what you've done. I find it easier to follow like this though - going to see if there's any perf difference between the two though
    Code:
    SELECT result
         , DateAdd(dd, the_day - 1, DateAdd(mm, the_month - 1, DateAdd(yy, the_year - Year(0), 0))) As awesome
    FROM   (
            SELECT result
                 , sdate
                 , the_year
                 , the_month
                 , sdate - (the_year * 65536) - (the_month * 256) As the_day
            FROM   (
                    SELECT result
                         , sdate
                         , the_year
                         , (sdate - the_year * 65536) / 256 As the_month
                    FROM   (
                            SELECT result
                                 , sdate
                                 , sdate / 65536 As the_year
                            FROM   @ugh
                           ) As years
                   ) As months
           ) As days
    George
    Home | Blog

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yep, same execution plan and what not.
    George
    Home | Blog

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    SCORE! Just been told they don't care about reporting on the time portion [for now]...
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by gvee View Post
    I like what you've done.
    Of course you do - it is awesome geniusness. Not so keen on your version though - it is so damned "readable".

    The time would be easy - just repeat the logic.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'll create a TVF for time, but won't utilise it for this task.

    Cheers for your help flumpy, hopefully this will get me the desired results in an acceptable time.
    George
    Home | Blog

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pootle flump
    it is awesome geniusness
    That it is my friend, that it is.
    George
    Home | Blog

Posting Permissions

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