OnDemand User Group

Support Forums => MP Server => Topic started by: danielito on January 19, 2023, 09:43:05 AM

Title: New Date Format CMOD 10.5
Post by: danielito on January 19, 2023, 09:43:05 AM

Hi
I have now a mix with the ols and new Date In the cmod Tables ( Segments ) and I don't get the right output out of my Script.

If I do something like this

   while read line
   do
      SQLCMDFIL="${var1}/${SQLDB}_${prjload}_cmdliste.sql"
      SQLOUT="${var1}/${SQLDB}_${prjload}_outliste_${app}"
      SQLUNLD="${var1}/${SQLDB}_${prjload}_unlo_${app}"
   #
   # SELECT 'table_1' AS table_name, COUNT(*) FROM table_1 UNION
      cat ${SQLHEAD} >${SQLCMDFIL}
      echo -e "spool ${SQLOUT}"  >> ${SQLCMDFIL}
      echo -e "select '#',Form,'#',Date,'#',LB1,'#',LB2,'#',LB3,'#' from "${SQLDB}"."${line}" where code = 99 ;" >>${SQLCMDFIL}
      echo -e "spool off"  >>${SQLCMDFIL}
      echo -e "exit;" >>${SQLCMDFIL}
      sqlplus -s ${Psqluser}/${Psqluserpwd} @${SQLCMDFIL}
      cat ${SQLOUT}.lst >> ${var2}/${AGI}_${AGIA}_report
   done < ${var1}/table.txt
   
   Then I have this Output in my File
   
   # FORM1 #                                                                     
       30-JUN-00 12.00.00 AM                                                       #   
         8576 # 777 #   00000000 #

With the  old arsdate format the output was like this

# FORM1 # 30.07.2000 # 8576 # 777 #  00000000 #

Is it possible to get the same output with the new Date Format ??

thanks
Dani

Title: Re: New Date Format CMOD 10.5
Post by: Justin Derrick on January 19, 2023, 02:51:55 PM

Hi Dani.

Querying mixed date formats isn't going to be easy - it's best left to CMOD by using the arsdoc query command.

If you're going to insist on running the queries in SQL, check out the wiki article on date formats:
  https://cmod.wiki/index.php?title=Date_and_Time_formats_in_Content_Manager_OnDemand

You're going to need to determine which tables have old-style date formats and new-style date formats, then convert as appropriate.  There appears to be a built-in function in CMOD called ODDAT that handles the special cases around the different date formats, but I'm not sure that's available to you in DB2.

Let us know how it goes!

-JD.

Title: Re: New Date Format CMOD 10.5
Post by: danielito on February 07, 2023, 09:38:24 AM

Hi JD
I was able to try it again today.
Alessandro was pointing me in the right direction last week and he got me a hint .... ;D

That's what I did for sql oracle to get the right output .... Date in Oracle > 30.06.00 00:00:00.000000000

  while read line
   do
      SQLCMDFIL="${var1}/${SQLDB}_${prjload}_cmdliste.sql"
      SQLOUT="${var1}/${SQLDB}_${prjload}_outliste_${app}"
      SQLUNLD="${var1}/${SQLDB}_${prjload}_unlo_${app}"
   #
   # SELECT 'table_1' AS table_name, COUNT(*) FROM table_1 UNION
      cat ${SQLHEAD} >${SQLCMDFIL}
      echo -e "spool ${SQLOUT}"  >> ${SQLCMDFIL}
      echo -e "select '#',Form,'#',to_char( Date, 'DD.MM.YYYY' ),'#',LB1,'#',LB2,'#',LB3,'#' from "${SQLDB}"."${line}" where code = 99 ;" >>${SQLCMDFIL}
      echo -e "spool off"  >>${SQLCMDFIL}
      echo -e "exit;" >>${SQLCMDFIL}
      sqlplus -s ${Psqluser}/${Psqluserpwd} @${SQLCMDFIL}
      cat ${SQLOUT}.lst >> ${var2}/${AGI}_${AGIA}_report
   done < ${var1}/table.txt

and the result is

# FORM1 # 30.06.2000 #  8576 # 777 #  00000000 # ..... all on the same line

have a nice day
Dani