Change date format of variable contents

[sriblon]

I am importing a file into a datawindow, and my first row contains column headings. Some of these headings are dates and I need to change the format before using the dates as the text of datawindow column headings. I have the following code, but the date is not displaying correctly.
In my test file, the dates used are: 6-Jun-2009,13-Jun-2009,20-Jun-2009,27-Jun-2009,4-Jul-2009
The results I see are: 1/1/00, 6/13/20, 6/20/20, 6/27/20, 1/1/00

// Get all dates
ll_weekNum = 1
Do while ll_weekNum <=5
ls_columnName = "week" + String(ll_weekNum)
ls_date = dw_file_viewer.getitemstring(1,ls_columnName)
choose case ll_weekNum
case 1
ls_week1 = ls_date
case 2
ls_week2 = ls_date
case 3
ls_week3 = ls_date
case 4
ls_week4 = ls_date
case else
ls_week5 = ls_date
end choose
ll_weekNum ++
Loop

// Convert Strings to dates
RegistryGet("HKEY_CURRENT_USER\Control Panel\International", "sShortDate", ls_shortdate) //storing user's date setting
RegistrySet("HKEY_CURRENT_USER\Control Panel\International", "sShortDate", "d-Mmm-yyyy")
id_week1 = Date(ls_week1)
id_week2 = Date(ls_week2)
id_week3 = Date(ls_week3)
id_week4 = Date(ls_week4)
id_week5 = Date(ls_week5)

// Convert dates to correct format
RegistrySet("HKEY_CURRENT_USER\Control Panel\International", "sShortDate", ls_shortdate)

// Convert dates to strings

ls_week1 = String(id_week1)
ls_week2 = String(id_week2)
ls_week3 = String(id_week3)
ls_week4 = String(id_week4)
ls_week5 = String(id_week5)

// Update column headings with new dates
ll_weekNum = 1
Do while ll_weekNum <=5
choose case ll_weekNum
case 1
ls_modify = "week1_t.text =~' " + ls_week1 + "~' "
case 2
ls_modify = "week2_t.text =~' " + ls_week2 + "~' "
case 3
ls_modify = "week3_t.text =~' " + ls_week3 + "~' "
case 4
ls_modify = "week4_t.text =~' " + ls_week4 + "~' "
case else
ls_modify = "week5_t.text =~' " + ls_week5 + "~' "
end choose
dw_file_viewer.Modify(ls_Modify)
ll_weekNum ++
Loop

 

[thekl0wn]

You could manually parse out the date, to get the day/month/year string variables, and then make a simple user function to return the month number. Then use the Date( ) function like: Date( Long( ls_year ), Long( ls_month ), Long( ls_day ) )

uf_month_number( as_month )

Long ll_month

as_month = Upper( Left( as_month, 3 ) )

CHOOSE CASE as_month
CASE 'JAN', '1', '01'
ll_month = 1
CASE 'FEB', '2', '02'
ll_month
...
END CHOOSE

RETURN ll_month

 

[mbalent]

Are you expecting the dates to be in 'mm/dd/yyyy' format when you are done? If so put that format in the 'string' function you use to convert the date values to strings. (ie, ls_week1 = String(id_week1, "mm/dd/yyyy")

Matt

"Nature forges everything on the anvil of time

 

[sriblon]

Thank you for your responses. I believe I will have to use the solution that suggests parsing the string and creating the function to return the month. When I try ls_week1 = String(id_week1, "mm/dd/yyyy"), my input of 6-Jun-2009 displays as 01/01/1900.

 

[mbalent]

01/01/1900 as a date generally indicates a null value. If you put a breakpoint in the code what are the various dates (strings)being set to?

Matt

"Nature forges everything on the anvil of time

 

[sriblon]

The string variable contents are correct:
ls_week1 = "6-Jun-2009"
ls_week2 = "13-Jun-2009"
ls_week3 = "20-Jun-2009"
ls_week4 = "27-Jun-2009"
ls_week5 = "4-Jul-2009"

The problem occurs on the following lines:
id_week1 = Date(ls_week1)
id_week2 = Date(ls_week2)
id_week3 = Date(ls_week3)
id_week4 = Date(ls_week4)
id_week5 = Date(ls_week5)

The value of these variables are now:
id_week1 = 1-Jan-1900 ls_week1 = "6-Jun-2009"
id_week2 = 13-Jun-2020 ls_week2 = "13-Jun-2009"
id_week3 = 20-Jun-2020 ls_week3 = "20-Jun-2009"
id_week4 = 27-Jun-2020 ls_week4 = "27-Jun-2009"
id_week5 = 1-Jan-1900 ls_week5 = "4-Jul-2009"

This is the result whether I edit the registry at runtime or not.

 

[thekl0wn]

Now my curiosity is sparked... Could you try adding a zero in front of the dates with only one integer for the day? To test it, use something like:

IF Len( ls_week1 ) = 10 THEN ls_week1 = '0' + ls_week1

Then try your Date( ) conversion...

 

[sriblon]

Adding the leading zero to the string solved the "null" date problem.

However, all my years are still showing up as "2020."
After execution of the following:
id_week1 = Date(ls_week1)
id_week2 = Date(ls_week2)
id_week3 = Date(ls_week3)
id_week4 = Date(ls_week4)
id_week5 = Date(ls_week5)

The value of these variables are:
id_week1 = 6-Jun-2020 ls_week1 = "06-Jun-2009"
id_week2 = 13-Jun-2020 ls_week2 = "13-Jun-2009"
id_week3 = 20-Jun-2020 ls_week3 = "20-Jun-2009"
id_week4 = 27-Jun-2020 ls_week4 = "27-Jun-2009"
id_week5 = 4-Jul-2020 ls_week5 = "04-Jul-2009"

 

[thekl0wn]

Looks as though it's expecting a 2-digit year, rather than the provided 4-digit, and it's only taking in the millennium/century, rather than the decade/year.

 

[sriblon]

That's what I'm figuring too, but I don't know how to fix it.

 

[mbalent]

What is the return value of your RegistrySet (ie, is it successful)?

Matt

"Nature forges everything on the anvil of time

 

[sriblon]

My Registry Edit is successful. Before my application starts, the sShortDate setting is "M/d/yy". At runtime I verified the change to "dd-Mmm-yyyy" and then back to "M/d/yy" after processing the string/date conversions.

Something interesting to note:
When I change my input data to the format M/dd/yyyy, the dates appear correctly.

i.e. 6/6/2009, 6/13/2009, etc. will appear correctly formatted as 6/6/09, 6/13/09.

When I tested this, I did not change the calls to edit the Registry's sShortDate format. I am still changing it to "dd-Mmm-yyyy" at runtime, but it doesn't seem to matter what is in the Registry at that time.

 

[mbalent]

I wonder if you changed the registry setting manually to 'dd-Mmm-yyyy' then ran the app what would happen?

Matt

"Nature forges everything on the anvil of time

 

[sriblon]

I am pretty convinced PowerBuilder's built in Date(<string>) function just doesn't know how to handle the dd-Mmm-yyyy format. When I manually change my registry to dd-Mmm-yyyy, after the following line of code:
id_week1 = Date(ls_week1)

the variable contents are:
ls_week1 = "06-Jun-2009"
id_week1 = "06-Jun-2020"

I need to use this date as a comparison in some logic later in the program. I am at the point to accept that I have to write a function to handle these dates (as originally suggested by the kl0wn).