Dates [examples]

Enable century in dates
SET CENTURY
set century on
use patrons index dates
list all for date = ctod("01/01/2003")

https://www.lianja.com/doc/index.php/SET_CENTURY


Determine input and output date formats
SET DATE
set date german
use patrons index dates
seek ctod("01.01.98")
set century on
seek ctod("01.01.1998")

https://www.lianja.com/doc/index.php/SET_DATE


Specify a one hundred-year epoch to allow for two digit years
SET EPOCH
set century off
set epoch to 1995

https://www.lianja.com/doc/index.php/SET_EPOCH


Change time display to 12 or 24 hours
SET HOURS
set hours to 24

https://www.lianja.com/doc/index.php/SET_HOURS


Change the separator character in dates
SET MARK
set mark to "."
? date()
05.05.2000

https://www.lianja.com/doc/index.php/SET_MARK


Specify whether the display of datetime values includes seconds
SET SECONDS
set seconds off
? datetime()
01/23/2004 01:18 PM
set seconds on
? datetime()
01/23/2004 01:18:22 PM

https://www.lianja.com/doc/index.php/SET_SECONDS


Return the character day of week
CDOW
store cdow(date()) to dayofweek
? dayofweek
Sunday
dayofweek = cdow(date())
? dayofweek
Sunday
dayofweek = cdow(datetime())
? dayofweek
Sunday

https://www.lianja.com/doc/index.php/CDOW()


Return 12-hour clock time
AMPM
? ampm()
07:30:24 pm

https://www.lianja.com/doc/index.php/AMPM()


Return the current system time in a specified format
TIMEOFDAY

 

// hh:mm:ss
> ? timeofday()
14:43:26
> ? timeofday(0)
14:43:26
// xxx (milliseconds)
> ? timeofday(1)
482
// hh:mm:ss:xxx (24hr)
> ? timeofday(2)
14:44:41.012
// ss:xxx since midnight
> ? timeofday(3)
53088.540
// ss:xxx since TIMEOFDAY() last called
> ? timeofday(4)
5.248
// YYYYMMDDhh:mm:ss
> ? timeofday(5)
2015110614:44:58
// YYYYMMDD
> ? timeofday(6)
20151106
// ss:xxx since TIMEOFDAY() last called with text postfix
> ? timeofday(7)
5.248 seconds

https://www.lianja.com/doc/index.php/TIMEOFDAY()


Return a character string including a formatted date or datetime string
STRFTIME
echo strftime("%c",ttoc(datetime()))
echo strftime("%U",dtoc(date()))

https://www.lianja.com/doc/index.php/STRFTIME()


Convert character to datetime
CTOT
mdate = ctot("01/21/2004 01:44:44 PM")
? mdate
01/21/2004 01:44:44 PM
? type("mdate")
T

https://www.lianja.com/doc/index.php/CTOT()


String to datetime conversion
STOT
? stot("20121003101509")
10/03/2012 10:15:09 AM

https://www.lianja.com/doc/index.php/STOT()


Difference between two time strings
ELAPTIME
? elaptime("10:10:10","11:11:11")
01:01:01

https://www.lianja.com/doc/index.php/ELAPTIME()


Return the numeric hours from a specified datetime
HOUR
? hour({10/10/2004 10:15:43 AM})
        10
m_Hour = hour(datetime())
? type("m_Hour")
N

https://www.lianja.com/doc/index.php/HOUR()


Extract number of hours from a time string
HOURS
? hours("10:00:00")
        10

https://www.lianja.com/doc/index.php/HOURS()


Return the numeric minutes from a specified datetime
MINUTE
? minute({10/10/2004 10:15:43 AM})
        15
m_Min = minute(datetime())
? type("m_Min")
N

https://www.lianja.com/doc/index.php/MINUTE()


Extract minutes from a specified time string
MINUTES
? minutes("10:21:00")
        21

https://www.lianja.com/doc/index.php/MINUTES()


Return the numeric seconds from a specified datetime
SEC
? sec({10/10/2004 10:15:43 AM})
        43
m_Sec = sec(datetime())
? type("m_Sec")
N

https://www.lianja.com/doc/index.php/SEC()


Extract seconds from a time string
SECONDS
? seconds("10:13:21")
     38601
// From Lianja v2.0
? seconds()
 36834.470

https://www.lianja.com/doc/index.php/SECONDS()


Number of seconds since midnight
SECS
? secs("10:10:10")
     36610
 
// Another Example
use accounts
replace seconds with secs(time())
? seconds
     39306
? tstring(seconds)
10:55:00

https://www.lianja.com/doc/index.php/SECS()


Current system time
TIME
? time()
17:47:24
? time(1)
17:47:31.00

https://www.lianja.com/doc/index.php/TIME()


Current system date and time
TIMESTAMP
update_time = timestamp()

https://www.lianja.com/doc/index.php/TIMESTAMP()


Convert seconds to a time-string
TSTRING
? tstring(36610)
10:10:10

https://www.lianja.com/doc/index.php/TSTRING()


Convert a datetime expression to a string value in an optionally specified format
TTOC
set date american
? ttoc({^2004-03-29 10:15:43 AM})
03/29/2004 10:15:43 AM
? ttoc({^2004-03-29 10:15:43 AM},1)
20040329101543
? ttoc({^2004-03-29 10:15:43 AM},2)
10:15:43 AM
? ttoc({^2004-03-29 10:15:43 AM},3)
20040329 10:15:43
? ttoc({^2004-03-29 10:15:43 AM},4)
2004-03-29T10:15:43.000Z

https://www.lianja.com/doc/index.php/TTOC()


Convert datetime to date
TTOD
set date american
set century on
mdate = ttod({^2004-03-29 10:15:43 AM})
? mdate
03/29/2004
? type("mdate")
D

https://www.lianja.com/doc/index.php/TTOD()


Convert date to datetime
DTOT
set date american
set century on
mdate = dtot({03/29/2010})
? mdate
03/29/2010 10:15:43 AM
? type("mdate")
T

https://www.lianja.com/doc/index.php/DTOT()


Check the validity of a time-string
VALIDTIME
store "00:00" to m_time
@5,5 get m_time picture "99:99";
  valid validtime(m->m_time+":00");
  error "Invalid time.  Press any key.";
  help "Enter the start time."
read

https://www.lianja.com/doc/index.php/VALIDTIME()


Week number for the specified date or datetime, from 1 to 53
WEEK
? week(datetime())
? week(date(),2))
? week(date(),0,2)
 
// Week starts on Sunday (default), first week has four days in current year
? week({01/01/2004},2)
        53
// Week starts on Monday, first week has four days in current year
? week({01/01/2004},2,2)
         1

https://www.lianja.com/doc/index.php/WEEK()


Return the character month from a specified date or datetime
CMONTH
? cmonth(datetime())
October
? cmonth(date())
October
store cmonth(date()) to month
? month
October
? type("month")
C

https://www.lianja.com/doc/index.php/CMONTH()


Convert character to date
CTOD
mdate = ctod("11/10/2003")
? mdate
11/10/2003
? type("mdate")
D

https://www.lianja.com/doc/index.php/CTOD()


Current system date or a date from specified year, month and day values
DATE
? date()
04/04/2004
mdate = date()
? mdate
04/04/2004
? type("mdate")
D
? date(4,4,4)
04/04/2004

https://www.lianja.com/doc/index.php/DATE()


Current system date and time or a datetime from specified date and time values
DATETIME
? datetime()
04/04/2004 11:54:45 AM
mdatetime = datetime()
? mdatetime
04/04/2004 11:54:45 AM
? type("mdatetime")
T
? datetime(4,4,4,4,4,4)
04/04/2004 04:04:04 AM
? datetime(4,4,4,14)
04/04/2004 02:00:00 PM

https://www.lianja.com/doc/index.php/DATETIME()


Day of the month from a specified date or datetime
DAY
? day({01/01/2004})
         1
store day({01/01/2004}) to m_Day
? m_Day
         1
m_Day = day(date())
? type("m_Day")
N
 
? day({10/10/2004 10:15:43 AM})
        10

https://www.lianja.com/doc/index.php/DAY()


Determine whether the specified year is a leap year
ISLEAPYEAR
? isleapyear(2012)
.T.

https://www.lianja.com/doc/index.php/ISLEAPYEAR()


Number of leap years between 01/01/0001 and the specified date
LEAPYEARS
? leapyears(date())
       487
? leapyears(datetime())
       487
? leapyears(val(sys(1)))
       487

https://www.lianja.com/doc/index.php/LEAPYEARS()


Number of whole days in a period expressed in seconds
DAYS
? days(300033)
         3
m_secs=357812
? "The period was "+alltrim(str(days(m->m_secs)))+;
  " days and "+tstring(m->m_secs) +" hours"
The period was 4 days and 03:23:32 hours

https://www.lianja.com/doc/index.php/DAYS()


Return a date or datetime as a character string
DMY
? dmy({04/04/2005})
4 April 2005
 
? dmy({04/04/2005 06:12:45 PM})
4 April 2005

https://www.lianja.com/doc/index.php/DMY()


Numeric day of the week from a specified date
DOW
set date american
? dow({02/29/2004})
         1
? dow({02/29/2004},2)
         7
store dow({02/29/2004}) to dayofweek
? dayofweek
         1
dayofweek = dow({02/29/2004 11:35:27 A.M.})
? dayofweek

https://www.lianja.com/doc/index.php/DOW()


Date to character conversion
DTOC
? dtoc({02/29/2004})
02/29/2004
? dtoc({02/29/2004 10:34:21 A.M.})
02/29/2004
? dtoc({02/29/2004},1)
20040229
store dtoc({02/29/2004}) to m_date
? m_date
02/29/2004
? type("m_date")
C

https://www.lianja.com/doc/index.php/DTOC()


Convert a date to DD-Mmm-YYYY format
DTOM
? dtom({10/10/2000})
10-Oct-2000

https://www.lianja.com/doc/index.php/DTOM()


Date to string conversion
DTOS
? dtos({02/29/2004})
20040229
? dtos({02/29/2004 11:18:54 PM})
20040229
store dtos({02/29/2004})to m_date
? m_date
20040229
? type("m_date")
C

https://www.lianja.com/doc/index.php/DTOS()


Convert Lianja dates to OpenVMS VAXdates
DTOV
? dtov({02/02/2000}+7)
9-FEB-2000 00:00:00.00

https://www.lianja.com/doc/index.php/DTOV()


Current EPOCH setting
EPOCH
? epoch()
      1900
set epoch to 1995
? epoch()
      1995

https://www.lianja.com/doc/index.php/EPOCH()


Return a date that is a specified number of months before or after a particular date or datetime
GOMONTH
?gomonth({04/14/2004}, 4)
08/14/2004
 
? gomonth({01/21/2004 03:18:33 PM},-12)
01/21/2003

https://www.lianja.com/doc/index.php/GOMONTH()


Return a date as a character string
MDY
set century on
? mdy({04/04/1990})
April 4, 1990

https://www.lianja.com/doc/index.php/MDY()


Return month from a specified date or datetime
MONTH
? month(date())
        10
? month(datetime())
        10

https://www.lianja.com/doc/index.php/MONTH()


Convert a date string in the format DD-Mmm-YYYY to a date data type
MTOD
set century on
? mtod("10-Oct-2008")
10/10/2008

https://www.lianja.com/doc/index.php/MTOD()


Return year quarter for the specified date or datetime
QUARTER
? quarter({01/22/2004})
         1
? quarter({^20040822 12:34:29 PM})
         3
? quarter({01/22/2004},2)
         4

https://www.lianja.com/doc/index.php/QUARTER()


String to date conversion
STOD
mCdate = dtos(date())
? mCdate
20130722
? type("mCdate")
C
mDdate = stod(mCdate)
? mDdate
07/22/2013
? type("mDdate")
D

https://www.lianja.com/doc/index.php/STOD()


Convert OpenVMS dates to Lianja dates
VTOD
? vtod("17-MAY-1990")+7
05/24/1990

https://www.lianja.com/doc/index.php/VTOD()


Extract year from date or datetime
YEAR
? date()
04/04/2004
? year(date())
      2004
? year(datetime())
      2004

https://www.lianja.com/doc/index.php/YEAR()


Check whether an expression is a date
DATE
> ? is_date(date())                                                             
.T.                                                                             
> ? is_date({12/12/2010})                                                       
.T.

https://www.lianja.com/doc/index.php/IS_DATE()


Advertisements

Date

Q:
in WebView (report) it’s displayed ok (e.g. “German”)…
A:
Dates in the Web Client are displayed in ANSI format so to be independent of the locale of the user.
The format is:
yyyy-mm-dd or yyyy-mm-dd hh:ii:ss
WebView report is generated server side using a dynamic server page which is why it can display an alternative date format to ANSI.


Q:
if I type:

Code:
a = timeofday(3)
b = timeofday(3)
c = b-a
? c...

I got the “string” 40534.37340526.850..
A:
Timeofday(4) returns the difference – the elapsed time between calling timeofday().
Timeofday() returns a character value.

Code:
a = timeofday(3)
? type("a")
C
? vartype(a)
C

Q:
What will be the date format of a Lianja table if record is inserted through Lianja ODBC ?
What is the default the century setting ?
A:
Lianja ODBC uses ISO format dates: YYYY-MM-DD; Century is ON.


Q:
I tried to update date field in my Lianja table from VFP 9.0 sp2 through Lianja ODBC and my date setting was YMD. The command is

Code:
? sqlexec(lnConnHandle, "update mytable set dated = {2015/05/14 09:29:02}")

The command is successfully executed but when I fetched data from Lianja table, it showed NULL is saved in my table.
If I save {2015-05-14 09:29:02} then the datetime value is stored perfectly. Is there a way to set date mark ‘-‘ to ‘/’ for Lianja ODBC ?
A:
Dates are set to ISO internally, so you would need to convert the value you are sending, e.g.
ctot(strtran(“2015/05/14 09:29:02″,”/”,”-“))


Q:
know how the syntax for dates with Visual FoxPro to ODBC lianja.
I’m trying several but do not work:

Code:
ffin=date()
myselect="SELECT * FROM FACTDIG WHERE FCHFACTURA<={?ffin}"
myselect="SELECT * FROM FACTDIG WHERE FCHFACTURA<=?ffin"
myselect="SELECT * FROM FACTDIG WHERE FCHFACTURA<={ts '01/29/2015 00:00:00'}"
if lnConnHandle > 0
sqlexec(lnConnHandle,myselect,"FACTDIG")
else 
messagebox("Sin Conexion","Error")
RETURN .F.
endif

A:

Code:
// TYPE("employees_calendar.eventstart") = "T" 
lc = SQLCONNECT("Lianja_Southwind")
m_datetime = TTOC(DATETIME(),1)
? SQLEXEC(lc,"select * from employees_calendar where eventstart < stot(?m_datetime)")
SQLDISCONNECT(lc)

// TYPE("orders.orderdate") = "D"
lc = SQLCONNECT("Lianja_Southwind")
m_date = DTOS(DATE())
? SQLEXEC(lc,"select * from orders where orderdate < stod(?m_date)")
SQLDISCONNECT(lc)

this is ok:

Code:
Xfini = DTOS(FINI)
Xffin = DTOS(FFIN)
myselect="SELECT * FROM FACTDIG WHERE FCHFACTURA>= stod(?XFINI) AND FCHFACTURA<= stod(?XFFIN) ORDER BY FACTDIG.FCHFACTURA"


Date

Here’s an example that returns DD/MM/YY – I’m calling it from the click of a commandbutton in a canvas section. My function is just in the section custom library here.

Code:
function formatDate(d) {
  var dd = d.getDate();
  if ( dd < 10 ) dd = '0' + dd;
   var mm = d.getMonth()+1;
  if ( mm < 10 ) mm = '0' + mm;
  var yy = d.getFullYear() % 100;
  if ( yy < 10 ) yy = '0' + yy;
return dd+'/'+mm+'/'+yy;

}
////////////////////////////////////////////////////////////////
// Event delegate for 'click' event
function page1_section1_field1_click()
{
        var currentdate = new Date();
        messagebox(formatDate(currentdate));
}


Lianja dates start at 1/1/1900.



The TTOC() function is used to format datetimes.
I have enhanced it in v1.3Beta6 to handle ISO 8601. See below.

Screen Shot 2014-11-25 at 1.24.45 PM