Question
· Aug 3, 2022

Working With Date and Time

Kindly help me on the below points

1)Concatenate the Date column and Time column.

2)Get the current date only without time.

3)Find the current age using DOB column.

Product version: IRIS 2022.1
Discussion (17)1
Log in or sign up to continue

Instead of two separate fields, it would be easier to use the %TimeStamp (or %PosixTime) type, where the date and time are paired at once.
For this type of data, you can make your own indexes for different parts and/or combinations of them: a separate date, a separate time, a separate year, a separate year and month, etc.
You can also use these separate parts in the query (in SELECT and WHERE).

select datediff('yy',DOB,current_dateAge from yourtable

A simple example of combining:

Class dc.test Extends %Persistent
{

Property DOB As %Date;

Property DOBTime As %Time;

ClassMethod Test()
{
  ..%KillExtent()
  
  &sql(insert into dc.test(DOB,DOBTime)
    select {'2020-01-01'},{'23:59:59'}
    union all
    select {'2022-12-31'},{'10:10:10'})
  
  st=##class(%SQL.Statement).%New()
  st.%SelectMode=2
  
  sql=5
  sql(1)="select *"
  sql(2)=",%odbcout(DOB)||' '||%odbcout(DOBTime) DOBString"
  sql(3)=",CAST(%odbcout(DOB)||' '||%odbcout(DOBTime) as DATETIME) DOBDateTime"
  sql(4)=",UNIX_TIMESTAMP(DOB||','||DOBTime) DOBUnixTimeStamp"
  sql(5)="from dc.test"
  st.%ExecDirect(.st,.sql).%Display()
}

}

Result:

USER>##class(dc.test).Test()

ID
(INTEGER)
DOB
(DATE)
DOBTime
(TIME)
DOBString
(VARCHAR)
DOBDateTime
(TIMESTAMP)
DOBUnixTimeStamp
(NUMERIC)
1 01.01.2020 23:59:59 2020-01-01 23:59:59 2020-01-01 23:59:59 1577923199.00
2 31.12.2022 10:10:10 2022-12-31 10:10:10 2022-12-31 10:10:10 1672481410.00
2 Rows(s) Affected
..
sql=0
sql($i(sql))="select *"
sql($i(sql))=",%external(DOB)||' '||%external(DOBTime) DOBString"
sql($i(sql))=",TO_CHAR(DOB||','||DOBTime,'MM/DD/YYYY HH24:MI:SS') DOBString2"
sql($i(sql))="from dc.test"
st.%ExecDirect(.st,.sql).%Display()
..

Result:

USER>##class(dc.test).Test()

ID
(INTEGER)
DOB
(DATE)
DOBTime
(TIME)
DOBString
(VARCHAR)
DOBString2
(VARCHAR)
1 01.01.2020 23:59:59 01.01.2020 23:59:59 01/01/2020 23:59:59
2 31.12.2022 10:10:10 31.12.2022 10:10:10 12/31/2022 10:10:10

I like @Yaron Munz version better

Class dc.test Abstract ]
{

ClassMethod Test()
{
  v=0,
    v($i(v))=$lb("2021-11-27","2022-08-04"),
    v($i(v))=$lb("2020-02-28","2023-02-27"),
    v($i(v))=$lb("2020-02-28","2023-03-01"),
    v($i(v))=$lb("2017-03-01","2020-02-28")
    
  i=1:1:$o(v(""),-1) {
    d1 $li(v(i),1),
      d2 $li(v(i),2),
      age1 $e(d2,1,4)-$e(d1,1,4)-($e(d1,6,10)]$e(d2,6,10)),
      age2 $number($zabs($zdh(d2,3)-$zdh(d1,3))/365.25,0),
      age3 $zdh(d2,3)-$zdh(d1,3)\365.25
      
    d1," ",d2," (age1 = ",age1,", age2 = ",age2,", age3 = ",age3,")",!
  }
}

}

Result:

USER>##class(dc.test).Test()
2021-11-27 2022-08-04 (age1 = 0, age2 = 1, age3 = 0)
2020-02-28 2023-02-27 (age1 = 2, age2 = 3, age3 = 2)
2020-02-28 2023-03-01 (age1 = 3, age2 = 3, age3 = 3)
2017-03-01 2020-02-28 (age1 = 2, age2 = 3, age3 = 2)