SQL Server Date from Within a Clarion Application

or

SQL Server UTC/GMT Date

One thing to consider when writing a CW program accessing an MS-SQL database is the use of the TODAY() function to get the current date. This will get the date from the local PC, rather than from the Server where the SQL Data is maintained. This can be problematic where a user had messed with the PC’s date. What I do is to use a global variable ( ServerToday – a CW LONG with the date in Clarion Standard Date format ) which I update at certain critical points within my application. Then, in lieu of the TODAY() call, I use my ServerToday variable.

So, how do I get the value from the server?

Simple. I coded a small procedure that I call from certain critical points. The procedure uses a “dummy table” to update the ServerToday variable. I do not expect anyone to be running a process around Midnight Server time, but just in case someone does not close their program one day and starts using it again the next, we need to get the ServerToday updated before any critical processing takes place.

I named my proc  GetServerDate and it is a small source proc. The table it uses is named TempF1 with a field F1 defined a a CSTRING(50) This proc simply sets my variable using the code shown below…

GetServerDate PROCEDURE
iOpenedit BYTE
   CODE
! sets ServerToday with the SQL Server date in Clarion format
   if not STATUS(TempF1)
       open(TempF1)
       iOpenedit = TRUE
   end
   clear(TempF1)
   tempF1{prop:sql} = 'select datediff( dd, 18001228 ,GetDate() ); '  
  ! or You could use....  tempF1{prop:sql} = 'select datediff( dd, -36163 ,GetDate() ); '  
   next(tempF1)
   if not errorcode()
       ServerToday = tempF1.F1
   end
   if iOpenedit
       close(TempF1)
   end
   RETURN

Some explanation: The line with the {prop:sql} calls the MS-SQL function GetDate( )  Then we have the SQL engine massage that result using the MS-SQL function DateDiff to get the date as the number of days since 1800-12-28  The result is an integer = CW LONG and will equivalent to the CW TODAY() result that would be returned by a program running on the server.


UTC Date

Perhaps you have an app that connects to your database across different time zones. In such cases, you will probably not want the time at the server, but the UTC/GMT date/time so you can make allowances for the program’s running location, and the server’s location.  Luckily, since SQL Server2000 we have an inbuilt SQL function that helps us here. This is the GetUTCdate() function. So instead of just the simple GetServerdate shown above, we can create a separate func to get the Clarion standard LONG date for the UTC time. See source code below…

GetUTCDate PROCEDURE
iOpenedit BYTE
RetVal LONG
CODE
! Returns the UTC date as a Clarion LONG
 if not STATUS(TempF1)
 open(TempF1)
 iOpenedit = TRUE
 end
 clear(TempF1)
 RetVal = 0
 tempF1{prop:sql} = 'select datediff( dd, 18001228 ,GetUTCDate() ); '
 next(tempF1)
 if not errorcode()
 RetVal = tempF1.F1
 end
 if iOpenedit
 close(TempF1)
 end
 RETURN RetVal


  John Griffiths