Regional Settings and Oracle To_Date Function

[The_Grudge]

I wrote a program back in 2006 that's served its purpose nicely. Unfortunately, I've always struggled with regional settings. Unless the user has the short date set as "MM/DD/YYYY" the query either doesn't populate the form or it throws an error.

My question to you all is, take the following SQL in my VB Code:

strSQL = "SELECT Prod.CSU_Nurse_Clinic.Clinic_Num, Prod.CSU_Nurse_Clinic.Nurse_Clinic_Name, " & _
          "PROD.CSU_Clinic_Info.Id, PROD.CSU_Clinic_Info.Occurs, " & _
          "PROD.CSU_Clinic_Info.Clinic_Num, PROD.CSU_Clinic_Info.User_Name, " & _
          "PROD.CSU_Clinic_Info.Clinic_Code,PROD.CSU_Clinic_Info.Sid_Doc_Num, " & _
          "PROD.CSU_Clinic_Info.Doc_Index, PROD.CSU_Clinic_Info.Rooms_Used, " & _
          "PROD.CSU_Clinic_Info.Cancelled, PROD.CSU_Clinic_Info.Rotation_Dept, " & _
          "PROD.CSU_Clinic_Info.Special_Booked_Date, " & _
          "PROD.CSU_Clinic_Info.Cancelled_Reason, PROD.CSU_Clinic_Info.Time_Of_Day, " & _
          "PROD.CSU_Clinic_Info.Special,PROD.CSU_Clinic_Info.Hold, " & _
          "PROD.CSU_Clinic_Info.Start_Date, PROD.CSU_Clinic_Info.End_Date, " & _
          "Prod.CSU_Clinic_Type.Clinic_Name, PROD.CSU_Clinic_Info.Reinstate, PROD.CSU_Clinic_Info.Reassignment, " & _
          "Prod.CSU_Clinic_Type.Clinic_ABRName, Prod.CSU_Location.Location " & _
          "FROM PROD.CSU_Clinic_Info, Prod.CSU_Nurse_Clinic, Prod.CSU_Clinic_Type, " & _
          "Prod.CSU_Location " & _
          "WHERE((PROD.CSU_Clinic_Info.Clinic_Date = To_Date ('" & dtDate & "','MM/DD/YYYY')))" & _
          "AND PROD.CSU_Clinic_Info.Clinic_Code = Prod.CSU_Clinic_Type.Clinic_CODE (+) " & _
          "AND PROD.CSU_Clinic_Info.Clinic_Num = Prod.CSU_Nurse_Clinic.Clinic_Num (+) " & _
          "AND PROD.CSU_Clinic_Info.Location_Num = Prod.CSU_Location.Location_Num(+) " & _
          "AND PROD.CSU_Clinic_Info.Cancelled <> 'Y' " & _
          "AND PROD.CSU_Clinic_Info.Institution = '" & strInstitution & "'" & _
          "AND not (PROD.CSU_CLINIC_INFO.LOCATION_NUM IN ('20', '30')) order by ID, DOC_INDEX desc"

Why does this work when the regional settings are set as described above but only then? My app is supposed to roll out on to PC's where I no longer have control over the regional settings so I need this to work regardless of a PC's configuration.

Any help is appreciated folks!

Thank you!

[Arnoutdv]

How is PROD.CSU_Clinic_Info.Clinic_Date stored in the Oracle Server?

Often it's easier to convert a VB6 date value (not the text representation) to an external date instead of relying of doing queries which require exact formatted text dates

[The_Grudge]

Appreciate the quick reply. It's stored as an Oracle DATE so today appears as:
03/22/2017 10:58 AM

[Arnoutdv]

That's also a representation I assume.

To_Date ('" & dtDate & "','MM/DD/YYYY')

The input for To_Date() is a dtDate variable, which is placed between ', so it seems text input
What kind of parameters does the To_Date function accept?

[The_Grudge]

String I thought.

https://www.techonthenet.com/oracle/...ns/to_date.php

[Arnoutdv]

Based on this code the input needs to be in the format : 'MM/DD/YYYY'

To_Date ('" & dtDate & "','MM/DD/YYYY')

I assume dtDate is your VB6 date variable?
Then try the following:

To_Date ('" & Month(dtDate) & "/" & Day(dtDate) & "/" & Year(dtDate) & "','MM/DD/YYYY')

[The_Grudge]

That seems to work, but I'm not sure I understand what's really changed. The date is still in string format but instead of all together it's parsed into three pieces. Thank you by the way!

[LaVolpe]

Then try the following:

To_Date ('" & Month(dtDate) & "/" & Day(dtDate) & "/" & Year(dtDate) & "','MM/DD/YYYY')

or: To_Date ('" & Format(dtDate, "m/d/yyyy") & "','MM/DD/YYYY')
if leading zeroes needed in the format then: Format(dtDate, "mm/dd/yyyy")

[Arnoutdv]

That seems to work, but I'm not sure I understand what's really changed. The date is still in string format but instead of all together it's parsed into three pieces.

Because your current local settings cause the date variable, when represented as text, does match the 'MM/DD/YYYY' specification or layout, but this will not be the case for or local settings
In my country date is presented as follow:

? date
23-3-2017

First the day then the month followed by the year, also note the different value separator.

That's why I did built the date string as separate parts, because Format(dtDate, "mm/dd/yyyy") seems to use local settings for the date separator, despite being specified as "/".

In the Netherlands it does the following:

? format(date,"mm/dd/yyyy")
03-23-2017

Because "-" is the local date separator.

[Phill.W]

So your user's date format <> your database's date "format".

The good news is that this doesn't matter.
Oracle's to_date() function lets you use any date format you want. In this instance, I would go with something like this:

"WHERE((PROD.CSU_Clinic_Info.Clinic_Date = to_date ('" & Format$( dtDate, "yyyy-mm-dd" ) & "','YYYY-MM-DD')))" & _

(This would, of course, be after choosing to use Parameterised Queries, which avoid this problem altogether, so long as you're passing the right Data Types around).

Regards, Phill W.

[Arnoutdv]

@Phil, in my previous post I explained why this can give a problem.
But I don't know how the Oracle To_Date method works and maybe it doesn't really care about the date separator.