Highlighted
Respected Contributor.
Respected Contributor.
1205 views

lr_db_getvalue parsing the birthday from YYYY-mm-dd to "dd/mm/YYYY 12:00:00 AM"

Jump to solution

Hello.

I am using VuGen to connect to a DB2 AIX database and run a query with  "lr_db_executeSQLStatement" the results are stored in a dataset   "DatasetName=MyDataset", then I disconnect from the DB2 and start extracting the data from the dataset with " lr_db_getvalue" as described next:

 lr_db_getvalue("StepName=GetValue",
    "DatasetName=MyDataset", 
    "Column=BIRTH_DT", 
    "OutParam=MyOutputParam5",
    "Row={next}",     
    LAST );

The problem is, the Birthday informaiton is been printed in log and output file as "dd/mm/YYYY 12:00:00 AM" instead of "YYYY/mm/dd" format and without timestamp, if I run the same query in TOAD, AIX or DataStudio the output is "1960-02-10" but for some reason seems VuGen is changing the format to "2/10/1960 12:00:00 AM".

 

Action.c(118): Notify: Parameter Substitution: parameter "next" = "1"
Action.c(118): Get db value "GetValue" started
Action.c(118): Notify: Saving Parameter "MyOutputParam5 = 2/10/1960 12:00:00 AM".
Action.c(118): Get db value was successful

 

How do I instruct in the "lr_db_getvalue" to use the format the birthday is originally stored in the database?

I also printed the whole dataset and is the same result

lr_db_dataset_action("StepName=PrintDataset", 
  "DatasetName=MyDataset", 
  "Action=PRINT", 
  LAST );  

NAME                  LAST_NAME                 BIRTHDAY  

XXXXXXX            XXXXXXXXXXX             2/10/1960 12:00:00 AM 

 

Josue A. Chavez D.
0 Likes
1 Solution

Accepted Solutions
Highlighted
Respected Contributor.
Respected Contributor.

Re: lr_db_getvalue parsing the birthday from YYYY-mm-dd to "dd/mm/YYYY 12:00:00 AM"

Jump to solution

This is the answer, but is more DB2 commands to force VuGen and change a Date format to a String and that way avoid LR to change the format.

Even when in the database is already in the format required, for some reason VuGen extracts it as different format, so I solved this with the help of a DB2 developer, he changed the query itself to force VuGen to change the "DATE" format to a "STRING" and let it know what format needs the string.

select varchar_format(max(d.BIRTH_DT),'YYYY-MM-DD') as BIRTH_DT

from......XXXXX

lr_db_dataset_action("StepName=PrintDataset", 
  "DatasetName=MyDataset", 
  "Action=PRINT", 
  LAST );  

 

 

Results

BIRTH_DT

1960-02-10 

1967-05-22 

1949-08-24   

 

 

Josue A. Chavez D.

View solution in original post

0 Likes
1 Reply
Highlighted
Respected Contributor.
Respected Contributor.

Re: lr_db_getvalue parsing the birthday from YYYY-mm-dd to "dd/mm/YYYY 12:00:00 AM"

Jump to solution

This is the answer, but is more DB2 commands to force VuGen and change a Date format to a String and that way avoid LR to change the format.

Even when in the database is already in the format required, for some reason VuGen extracts it as different format, so I solved this with the help of a DB2 developer, he changed the query itself to force VuGen to change the "DATE" format to a "STRING" and let it know what format needs the string.

select varchar_format(max(d.BIRTH_DT),'YYYY-MM-DD') as BIRTH_DT

from......XXXXX

lr_db_dataset_action("StepName=PrintDataset", 
  "DatasetName=MyDataset", 
  "Action=PRINT", 
  LAST );  

 

 

Results

BIRTH_DT

1960-02-10 

1967-05-22 

1949-08-24   

 

 

Josue A. Chavez D.

View solution in original post

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.