From Team Developer SqlWindows Wiki
Jump to: navigation, search

DateTime datatype


Pointer2.png Assign values to DateTime variables Pointer.png

A DateTime variable can be assigned to a specific fixed date/time in these ways:

Construct a Date/Time using SalDateConstruct

   ! Set birthdate to 2nd of May 1970, at 12 midnight
   Set dtBirthDate  = SalDateConstruct( 1970, 05, 02, 0, 0, 0 )

Specify the value in the TD DateTime format structure
(dont use any quotes, only the YYYY-MM-DD-HH.MM.SS.MMMMMM value)

   ! Set birthdate to 2nd of May 1970, at 12 midnight
   Set dtBirthDate = 1970-05-02-

Pointer2.png How to strip elements from a DateTime Pointer.png

The next function is able to strip certain parts from a DateTime.
So with this it is possible to construct a new DateTime value with only the wanted elements.
Milliseconds are always stripped.

Function: PALDateConstruct
      Date/Time: pdtDateTime
      Boolean: pbYearMonthDays
      Boolean: pbHoursMinutes
      Boolean: pbSeconds
   Local variables
      Number: nYear
      Number: nMonth
      Number: nDay
      Number: nHour
      Number: nMinute
      Number: nSecond
      If pbYearMonthDays
         Set nYear   = SalDateYear( pdtDateTime )
         Set nMonth  = SalDateMonth( pdtDateTime )
         Set nDay    = SalDateDay( pdtDateTime )
      If pbHoursMinutes
         Set nHour   = SalDateHour( pdtDateTime )
         Set nMinute = SalDateMinute( pdtDateTime )
      If pbSeconds
         Set nSecond = SalDateSecond( pdtDateTime )
      Return SalDateConstruct( nYear, nMonth, nDay, nHour, nMinute, nSecond )

So to strip of the seconds from the current DateTime

   dtTodayNoSeconds = PALDateConstruct( SalDateCurrent( ), TRUE, TRUE, FALSE )

Pointer2.png DateTime calculations Pointer.png

It is possible to use simple calculations on DateTime variables which are quite useful.
The calculations take the month length into account. Also leap years.

Add or subtract

You can use + and - to add or subtract values to DateTime variables.
You can add/subtract in days. So adding 1 to a DateTime will add one day.

   Set dtDateToday = SalDateCurrent( )
   Set dtDateEnd = dtDateToday + 1

Direct value assignments having a calculation is also possible:

   Set dtOneYearlater = 1970-05-02- + 365

But also fractions of one day can be used. For instance, subtract one and a half day (=1 days and 12 hours):

   Set dtDateToday = SalDateCurrent( )
   Set dtDateStart = dtDateToday - 1.5

Write the fractions so it is clear what you are actually calculating.
A day has 24 hours, 60 minutes per hour and 60 seconds per minute.
For instance, add one hour to a date:

   Set dtDateToday = SalDateCurrent( )
   Set dtDateEnd = dtDateToday + ( 1/24 )

This way you can add and subtract up to seconds when using the correct fractions.

Calculate difference between dates

When subtracting one DateTime variable from another, the result is the difference between them in days.
It is fractional, so the result can be on seconds precision.

   ! Set birthdate to 2nd of May 1970
   Set dtBirthDate  = SalDateConstruct( 1970, 05, 02, 0, 0, 0 )
   ! Calculate the days between birth and now
   Set nDaysBetween = SalDateCurrent( ) - dtBirthDate
   ! Calculate how many years old, divide by 365
   Set nYearsBetween = nDaysBetween / 365

Compare DateTime

Using < > and = you can compare dates

    If dtCheckDate > dtBirthDate

Beware that DateTime variables can have hours/minutes and seconds.
So comparing Today with a date will fail in this case:

    If dtCheckDate > SalDateCurrent( )

Even if dtCheckdate is today, because SalDateCurrent returns a dateTime having hour/minute/seconds part.
To compare like this, you have to strip the DateTime to get a date. (See other article here in this section)