﻿ Working With Date and Time | InterSystems Developer Community | Databases
Question
Ramil TK · 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
1
0 253
Discussion (17)1

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)``

Even easier with DATEDIFF:

``set age = \$SYSTEM.SQL.Functions.DATEDIFF("year", dob, \$h)``

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.

Divide by 365.25 to account for leap years.

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_date) Age 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()
{
d ..%KillExtent()

&sql(insert into dc.test(DOB,DOBTime)
select {d '2020-01-01'},{t '23:59:59'}
union all
select {d '2022-12-31'},{t '10:10:10'})

s st=##class(%SQL.Statement).%New()
s st.%SelectMode=2

s sql=5
s sql(1)="select *"
s sql(2)=",%odbcout(DOB)||' '||%odbcout(DOBTime) DOBString"
s sql(3)=",CAST(%odbcout(DOB)||' '||%odbcout(DOBTime) as DATETIME) DOBDateTime"
s sql(4)=",UNIX_TIMESTAMP(DOB||','||DOBTime) DOBUnixTimeStamp"
s sql(5)="from dc.test"
d st.%ExecDirect(.st,.sql).%Display()
}

}```

Result:

```USER>d ##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

```..
s sql=0
s sql(\$i(sql))="select *"
s sql(\$i(sql))=",%external(DOB)||' '||%external(DOBTime) DOBString"
s sql(\$i(sql))=",TO_CHAR(DOB||','||DOBTime,'MM/DD/YYYY HH24:MI:SS') DOBString2"
s sql(\$i(sql))="from dc.test"
d st.%ExecDirect(.st,.sql).%Display()
..
```

Result:

```USER>d ##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()
{
s 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")

f i=1:1:\$o(v(""),-1) {
s 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

w d1," ",d2," (age1 = ",age1,", age2 = ",age2,", age3 = ",age3,")",!
}
}

}```

Result:

```USER>d ##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)```