The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
check if European date if so convert to US date
Posted: 31 Oct 06 12:04 PM
|
fiogf49gjkf0d We have a custom field in which we capture a date. Our foreign offices using European date format in their windows are writing dates like this :” 27/10/2006 14:53”. This is crashing the comparison I later try to perform in knowledge sync. I would like to know how, within the plugin script I can check to see if the date given is in Europe format and reformat it US. I am using a simple datetimepicker.
Thank you |
|
|
| |
| |
|
Re: check if European date if so convert to US date
Posted: 31 Oct 06 6:23 PM
|
fiogf49gjkf0d Userfield5 is a vchar(80) so the text value in the control is getting stored in the database.
Some ideas:
1. add a date time field to the database and bind the datatime object to it. The value saved will then be a GMT based date.
2. write your own handler to reformat the values,based on the user's timezone, on the way to/from the database. |
|
|
|
Re: check if European date if so convert to US date
Posted: 01 Nov 06 1:36 PM
|
fiogf49gjkf0d Kewl I can easily add an extended table with a datetime field. I will do that. 
But, if anybody has an example of code to reformat values, so I can learn how that would be appreciated.
Thanks for your help |
|
|
|
Re: check if European date if so convert to US date
Posted: 02 Nov 06 9:48 AM
|
fiogf49gjkf0d SQL
Syntax
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Example
CONVERT(datetime, UserField5, 101)
101-USA-mm/dd/yy
103-British/French-dd/mm/yy
--------------------------------------------------------------
VBA
Syntax
CDate ( Date )
FormatDateTime ( Date [, NamedFormat ] )
Example
FormatDateTime(CDate(Date), 3)
vbShortDate-2-Display a date using the short date format: like the default (mm/dd/yy)
vbLongTime-3-Display a time using the time format: hh:mm:ss PM/AM
(This one should use your regional settings to determine the date format it uses.) |
|
|
|
Re: check if European date if so convert to US date
Posted: 03 Nov 06 2:40 PM
|
fiogf49gjkf0d Just to play devil's advocate for a second here: Does this field have both Euro and US dates? If so there is no reliable way to convert them. Why? 1/10/2006 is either January 10th (US) or October 1st (Euro). 31/10/2006 on the other hand is pretty obvious because the Euro day exceeds the US month. |
|
|
|
Re: check if European date if so convert to US date
Posted: 03 Nov 06 3:12 PM
|
fiogf49gjkf0d If the underlying field is a datetime field and the users interact with a data bound datetime object then everything should work.
When information is stored it will be stored as a datetime value adjusted to GMT. When information is displayed it will be reformatted according to the user's timezone and regioinal settings.
|
|
|
|
Re: check if European date if so convert to US date
Posted: 03 Nov 06 4:02 PM
|
fiogf49gjkf0d When a locale-specific date is entered into a datetime, is it automatically converted to the ISO date SQL uses? If so then any datetime field would be correct.
The problem I addressed specifically dealt with this instance, a vchar(80) stored string representation of the date. Omitting the obvious data integrity issue with just the separator alone (- vs /), it is almost impossible to determine if 1/10/2006 is in US or Euro if the field has a combination of both types.
One could use cues to help. If the 1st field is above 12, it's definitely a Euro date. If the modifyuser is a userid in Europe, chances are the date is in that format but that's not guaranteed. They could have touched a record created with a US date and didn't modify it to suit their locale. There may be other cues if you're lucky, like all Euro dates are in dd-mm-yyyy instead of dd/mm/yyyy but that doesn't seem to be the case from the example given. |
|
|
|
Re: check if European date if so convert to US date
Posted: 03 Nov 06 4:13 PM
|
fiogf49gjkf0d Jeremy,
Sorry I did not realize that you were talking about the complexities of parsing the varchar(80) field. Your comments present more reasons to store the values in a datetime field. |
|
|
|