0
763
Question Jude Mukkadayil · Aug 9, 2023

Display date in format DDMMYYYY

#Caché #InterSystems IRIS #Key Question #ObjectScript

Hi,

Can anyone please help me to display date field in the format DDMMYYYY which I need single digit date needs to have leading 0 like 01022000.

I tried to-char but leading 0 is not there. 
 

I am writing object script with the help of sql query for generating extracts.

can anyone please help on this.

thanks

jude

Product version: IRIS 2020.1
Discussion (19)3
Log in or sign up to continue

Comments

Ben Spead · Aug 9, 2023

Something like this?

USER>write $replace($ZDate($h,4,,4),"/","")
09082023
0
Ben Spead  Aug 9, 2023 to Ben Spead

Or, to make it work for any international locale:

USER>write $replace($ZDate($h,4,,4),##class(%SYS.NLS.Format).GetFormatItem("DateSeparator"),"")
09082023
0
Jude Mukkadayil  Aug 10, 2023 to Ben Spead

Hi Ben, This is not working fully. I mean to say MM is displaying with 0 but DD is not displaying with 0.

For instance, it displays as 1082000 and it should be 01082000.

Thanks

Jude

0
Ben Spead  Aug 10, 2023 to Jude Mukkadayil

can you please show your actual variable being set and then your command and its execution?  as you can see from my example above, I was able to get DD for a single digit day with 09 yesterday.  

0
Julius Kavay · Aug 9, 2023

My favorit is

write$tr($zd($h,15),"/.")
0
Ben Spead  Aug 9, 2023 to Julius Kavay

Please see details on the yearopt parameter for $zdate (https://docs.intersystems.com/iris20232/csp/docbook/DocBook.UI.Page.cls…) ... depending on the locale and on the year in question, if you don't specify yearopt you risk have a 2 digit year rather than a 4 digit year (default for my locale is yearopt = 0 which would show 20th century years as 2 digits).  You probably want to specify a yearopt of 4 to force 4 digits all the time.

0
Julius Kavay  Aug 9, 2023 to Ben Spead

Using a two digit year in 2023 is the same as wishing to get a Y2K problem again. Probably it will be now the Y2.1K issue.

0
Ben Spead  Aug 9, 2023 to Julius Kavay

agreed - which is why I always recommend forcing 4 digit years using yearopt with $zdate()  :)   Keep in mind that $zdate() preceeded Y2K which is why is has behavior that defaults to 2 years in certain circumstances (to keep the API calls backwards compatible).  

0
Pietro Di Leo · Aug 9, 2023

You can use: 

w$ZSTRIP($ZDATE($NOW(),1,,),"*","/"),!
0
Pietro Di Leo  Aug 9, 2023 to Ben Spead

You're right, this is the correct version

w $ZSTRIP($ZDATE($NOW(),4,,),"*","/"),!
0
Ben Spead  Aug 9, 2023 to Pietro Di Leo

Yup - that works.  Great use of $ZStrip!

0
John Peck · Aug 9, 2023

What a night mare.

W $zd($h,15) =09/08/2023

w $zd($h,1)=08/09/2023

How do you know the which country the end user is in

08092023 is very bland

The Only international  standard is used by the air lines

Imagine a ticket with such a date 08092023 this would be fraught with mis understandoing

w $zd($h,2,,4) = 08 Aug 2023 this is recognised the whole world over

hdate=$zdh(%d,2)

however
set Date=$tr($zd(%d,1),"/","") or
set Date=$tr($zd(%d,15),"/","")

depending on your calendar

0
Matt Gage · Aug 9, 2023

A little longer, but far more flexible, I've always liked

write $translate("DdMmCcYy", "CcYy-Mm-Dd",  $zdate($now(), 3))

Just provide the format you want in the first argument.

0
Ramil TK · Aug 10, 2023

me is using this line to get this

w $ZSTRIP($ZD($NOW(),1,,),"*","/"),!

0
Vitaliy Serdtsev · Aug 10, 2023

Can I see how you call TO_CHAR? I have a leading zero displayed without problems:

<FONT COLOR="#0000ff">SELECT </FONT><FONT COLOR="#000000">%internal(</FONT><FONT COLOR="#808000">to_date</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'01022000'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'DDMMYYYY'</FONT><FONT COLOR="#000000">)) -- 01022000 -> 58105
,</FONT><FONT COLOR="#808000">TO_CHAR</FONT><FONT COLOR="#000000">(58105,</FONT><FONT COLOR="#008080">'DDMMYYYY'</FONT><FONT COLOR="#000000">) -- 58105 -> 01022000</FONT>

TO_CHAR: Convert Dates to Formatted Date Strings

0
Jude Mukkadayil  Aug 10, 2023 to Vitaliy Serdtsev

This is the way how I use to_char in sql statement

TO_CHAR(58105,'DDMMYYYY')

I am extracting the data to excel file and I can see the column is assigned as a General and not a number. does it make any difference ?

Thanks

0
Vitaliy Serdtsev  Aug 11, 2023 to Jude Mukkadayil

It's clearer now, this is the tricks of Excel, which convert the string 01022000 to the number 1022000. In your case, try changing the cell type from General to Text.

PS: when generating Excel reports, I use the Date cell type (short format DD.MM.YYYY), and from Caché/IRIS I return the %Date type. The components that I use already know how to translate the internal representation of the date 01.02.2000 from the Caché/IRIS format (58105) to Excel (36557) and vice versa.

0