-
Mar 22nd, 2017, 09:53 AM
#1
Thread Starter
Fanatic Member
Regional Settings and Oracle To_Date Function
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:
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!
-
Mar 22nd, 2017, 09:58 AM
#2
Re: Regional Settings and Oracle To_Date Function
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
-
Mar 22nd, 2017, 09:59 AM
#3
Thread Starter
Fanatic Member
Re: Regional Settings and Oracle To_Date Function
Appreciate the quick reply. It's stored as an Oracle DATE so today appears as:
03/22/2017 10:58 AM
-
Mar 22nd, 2017, 10:01 AM
#4
Re: Regional Settings and Oracle To_Date Function
That's also a representation I assume.
Code:
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?
-
Mar 22nd, 2017, 10:06 AM
#5
Thread Starter
Fanatic Member
Re: Regional Settings and Oracle To_Date Function
-
Mar 22nd, 2017, 10:23 AM
#6
Re: Regional Settings and Oracle To_Date Function
Based on this code the input needs to be in the format : 'MM/DD/YYYY'
Code:
To_Date ('" & dtDate & "','MM/DD/YYYY')
I assume dtDate is your VB6 date variable?
Then try the following:
Code:
To_Date ('" & Month(dtDate) & "/" & Day(dtDate) & "/" & Year(dtDate) & "','MM/DD/YYYY')
-
Mar 22nd, 2017, 11:13 AM
#7
Thread Starter
Fanatic Member
Re: Regional Settings and Oracle To_Date Function
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!
-
Mar 22nd, 2017, 03:12 PM
#8
Re: Regional Settings and Oracle To_Date Function
Originally Posted by Arnoutdv
Then try the following:
Code:
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")
-
Mar 23rd, 2017, 03:45 AM
#9
Re: Regional Settings and Oracle To_Date Function
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:
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:
Code:
? format(date,"mm/dd/yyyy")
03-23-2017
Because "-" is the local date separator.
Last edited by Arnoutdv; Mar 23rd, 2017 at 03:50 AM.
-
Mar 24th, 2017, 06:41 AM
#10
Re: Regional Settings and Oracle To_Date Function
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:
Code:
"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.
-
Mar 24th, 2017, 07:29 AM
#11
Re: Regional Settings and Oracle To_Date Function
@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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|