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
https://test.community.intersystems.com/post/working-date-and-time
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) >>>> 1because 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
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 yourtableThank 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:
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
Result:
Could you elaborate on that, please?
See my old article Indexing of non-atomic attributes, chapter "Date (time, etc.)"
Thanks!
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: