Question
Ramil TK · Aug 3

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
1
0 161
Discussion (17)1
Log in or sign up to continue

Hello,

1. If you mean to Concatenate in SQL than you can use the CONCAT function 

2. To get the current date you may use the $ZDATE function (cos) pass the 1st parameter +$H and it will be todays date.

3. To find the DOB based on a date : 
Set Age = $P($ZD(+$h,3),"-")-$P($ZD(Dob,3),"-")-($E($ZD(+$H,8)5,8)<$E($ZD(Dob,8)5,8))

3.  This might be easier to follow

 Set Age=$NUMBER($ZABS($h-Dob)/365,0)

DATEDIFF only compares the YEAR numbers. So:
    write  $system.SQL.DATEDIFF("yy",$h-250,+$h)   >>>> 1
because of comparing 2021 to 2022
but it definitely is less than 1 year
and it depends on the actual day of the year 

This is less accurate, Robert. on Leap years it will get "rounded" just a few days before birthday.
A workaround this is to divide by 365.25 to get more accurate age

1)I am trying to Concatenate the 2 columns together, like bill date is in one column and bill time is in another column. I want to combine this both column and need to display as date and time using query. 

2) I am trying to get all the above using sql query.

select ROUND((date(NOW()) - date(DOB)) / 365,0) age,date(NOW()) from user

Using the above query me solved the point no 2 and 3, but point no 1 still i don't get any solution.

If any correction or suggestion in above query please correct me.

@Yaron Munz @Robert Cemper @Eduard Lebedyuk 
 

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

Thank you for your replay and this one also working fine.

This is already using one solution in that date and time are in a separate columns.

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

Thank you, and one more question in this.

How we can format the date string like DD/MM/YYYY HH:MM:SS like this or any other format 

..
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

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.

Could you elaborate on that, please?

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)