Announcement

Collapse
No announcement yet.

SQL Server CE DateTime to Powertime function

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL Server CE DateTime to Powertime function

    Does anyone have a slick conversion routine from a database DATETIME to a PowerBasic POWERTIME?

    I think they are both Quads. I tried simple assignment from one variable to the other and also MEMORY COPY using VARPTR .

    The hard way is something like...
    Code:
          SqlStr = "SELECT DATEPART(YYYY,EventDate) as EventYear," +_
                          "DATEPART(MM,EventDate) as EventMonth," +_
                          "DATEPART(DD,EventDate) as EventDay,EventDate,CardHolder " +_
                   "FROM Log " +_
                   "WHERE CardNumber = 1272822"
    to read the Year, Month and Day into the recordset, then...
    Code:
             vEventYear = pRecordSet.Collect("EventYear")
             vEventMonth = pRecordSet.Collect("EventMonth")
             vEventDay = pRecordSet.Collect("EventDay")
    to get the Year, Month and Day into variants, then...
    Code:
             pDate.NewDate(VARIANT#(vEventYear),VARIANT#(vEventMonth),VARIANT#(vEventDay))
    to get it into a POWERTIME object.

    I just thought there must be a easier way.

  • #2
    I am also seeing that DATETIME is based off Jan 1, 1900 and POWERTIME is based off Jan 1, 1601. So, not a straight conversion anyway.

    Comment


    • #3
      SQLServer uses a completely different schema to PB for storing a DateTime:

      "SQL Server uses 8 bytes to store a datetime value—the first 4 for the date and the second 4 for the time. SQL Server can interpret both sets of 4 bytes as integers. For the date portion, the value SQL Server stores is the number of days before or after a base date of January 1, 1900. A negative number represents a date earlier than January 1, 1900.
      It stores the second integer for the time as the number of clock ticks after midnight. A second contains 300 ticks, so a tick equals 3.3 milliseconds (ms)."


      Comment


      • #4
        Working with a common julian date might be useful (only year,month,day was used.)
        Julian& = SQLServer(EventDate) + plus/minus difference ' 1/1/1900 epoch 0 of SQL Server
        A day is so many nanoseconds/100 in my PowerTime demo.

        These seem on subject
        https://forum.powerbasic.com/forum/user-to-user-discussions/powerbasic-for-windows/761511-subtract-days-from-a-date

        https://forum.powerbasic.com/forum/user-to-user-discussions/third-party-addons/sqltools/750686-adding-days-to-date


        https://stackoverflow.com/questions/...-to-yyyy-mm-dd


        Looks like you may already have the solution and putting it into a macro might make it easier to use.
        https://duckduckgo.com instead of google

        Comment


        • #5
          Maybe too late to help Frank, but someone may want it -
          Code:
          #compile exe
          #dim all
          'SQL Server DT is 8 bytes -
          '  4 for days since January 1, 1900. (signed)
          '  4 for 300 ticks per each second after 00:00:00.000
          'Quad can stand-in for iPowerTime, FileTime type, and SQL server DT.
          '
          'to put in DLL change to SUB with 2 parameters, see note in FUNCTION help.
          '-------------------------------------------------------------------------------
          function SQLServerDT_To_FileTime(byref SQLDT as quad) as quad
            local FTDay, FTTime as quad
                    'days in SQL plus days from 1601 times 100nS in one day
            FTDay =  (hi(long, SQLDT) + 109206) * 864000000000&&
                    'SQL ticks times ratio 100nS to ticks
            FTTime = lo(dword, SQLDT) * 33334&&
                    'days and time both in 100nS, so add them for return
            function = FTDay + FTTime
          end function
          '- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
          function FileTime_To_SQLServerDT(byref FileDT as quad) as quad
            local SQLDay, SQLTime as quad
                   'divide filetime by number of 100nS in one day, minus days to 1900
            SQLDay = (FileDT \ 864000000000&&) - 109206&&
                    'mod filetime by number of 100nS in one day to get time portion
                    'then divide by 100nS to 300 tick ratio
            SQLTime = (FileDT mod 864000000000&&) \ 33334&&
            function = mak(quad, SQLTime, SQLDay)
          end function
          '_/\__/\__/\__/\__/\__/\__/\__/\__/\__/\__/\__/\__/\__/\__/\__/\__/\__/\__/\__/\
          'the part you want is above
          'from here down for developement and test
          'test code is console, suggested functions will work in either PBWin or PBCC
          type SYSTEMTIME
              wYear         as word
              wMonth        as word
              wDayOfWeek    as word
              wDay          as word
              wHour         as word
              wMinute       as word
              wSecond       as word
              wMilliseconds as word
          end type
          declare function SystemTimeToFileTime lib "Kernel32.dll" _
              alias "SystemTimeToFileTime" (lpSystemTime as SYSTEMTIME, _
              lpFileTime as quad) as long
          declare function FileTimeToSystemTime lib "Kernel32.dll" _
              alias "FileTimeToSystemTime" (lpFileTime as quad, _
              lpSystemTime as SYSTEMTIME) as long
          function pbmain () as long
            Days_FileToSQL
            Time_FileAndSQL
            TestSQL_FileTime_Conv
            waitkey$
          end function
          sub Days_FileToSQL()
            local Sys1900 as systemtime
            local File1900 as quad
            Sys1900.wYear  = 1900
            Sys1900.wMonth = 1
            Sys1900.wDay   = 1
            SystemTimeToFileTime(Sys1900, File1900)
            File1900 \= (10000000 * 24 * 60 * 60) '
            ? str$(10000000 * 24 * 60 * 60) ; " 100nS/Day"
            ? lo(long, File1900); " Days between epochs"  ' 109207 days between epochs
          end sub
            '864000000000 1 day as 100nSec
            '25920000     1 day as 300 ticks per sec
          sub Time_FileAndSQL()
            ? str$(864000000000 \ 25920000); " ratio of 100uS units to 300 tick units
            ?
          end sub
          sub TestSQL_FileTime_Conv()
            local SQLDay, SQLTime as long
            local SQLDT, FileDT as quad
            local SysDT as systemtime
            SQLDay = 33 'should be 2 feb 1900
            SQLTime = (13 * 60 * 60 * 300) + (15 * 60 * 300) + (24 * 300) '13:15:24
            SQLDT = mak(quad, SQLTime, SQLDay)
            FileDT = SQLServerDT_To_FileTime(SQLDT) 'being tested
            FileTimeToSystemTime(FileDT, SysDT)
            ? dec$(SysDT.wYear,   4) + "-" ; _
              dec$(SysDT.wMonth,  2) + "-" ; _
              dec$(SysDT.wDay,    2) + ", " ; _
              dec$(SysDT.wHour,   2) + ":" ; _
              dec$(SysDT.wMinute, 2) + ":" ; _
              dec$(SysDT.wSecond, 2)
          '-  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
            SysDT.wYear   = 2019
            SysDT.wMonth  = 1
            SysDT.wDay    = 11
            SysDT.wHour   = 9
            SysDT.wMinute = 9
            SysDT.wSecond = 9
            SystemTimeToFileTime(SysDT, FileDT)
            SQLDT = FileTime_To_SQLServerDT(FileDT) 'being tested
            FileDT = SQLServerDT_To_FileTime(SQLDT) 'now back for display
            FileTimeToSystemTime(FileDT, SysDT)
            ? dec$(SysDT.wYear,   4) + "-" ; _
              dec$(SysDT.wMonth,  2) + "-" ; _
              dec$(SysDT.wDay,    2) + ", " ; _
              dec$(SysDT.wHour,   2) + ":" ; _
              dec$(SysDT.wMinute, 2) + ":" ; _
              dec$(SysDT.wSecond, 2)
          end sub
            '
          Cheers,
          Dale

          Comment

          Working...
          X