| Author |
Message |
CoachGBT
Newbie
Joined: 08 Apr 2011
Online Status: Offline
Posts: 29
|
  
Topic: Convert Date
Posted: 22 Sep 2011 at 6:25am |
How do I convert this date format 2011093000000000 to 09/30/2011?
I tried the following date function:
DATE(RIGHT({TRHI.MISC_FT_FROM_VALUE},2)+"/"+MID({TRHI.MISC_FT_FROM_VALUE},5,2)+"/"+LEFT({TRHI.MISC_FT_FROM_VALUE},4))
To no avail, it did not work. Your suggestion is appreciated.
Thanks,
CoachGBT
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 Sep 2011 at 7:09am |
|
DATE(MID({TRHI.MISC_FT_FROM_VALUE},7,2)+"/"+MID({TRHI.MISC_FT_FROM_VALUE},5,2)+"/"+LEFT({TRHI.MISC_FT_FROM_VALUE},4))
|
| IP Logged |
|
CoachGBT
Newbie
Joined: 08 Apr 2011
Online Status: Offline
Posts: 29
|
Posted: 22 Sep 2011 at 8:27am |
I left out one other important detail, this field I want to do the date conversion already have existing dates for other record in the format of MM/DD/YYYY, and the function
DATE(RIGHT({TRHI.MISC_FT_FROM_VALUE},7)+"/"+MID({TRHI.MISC_FT_FROM_VALUE},5,2)+"/"+LEFT({TRHI.MISC_FT_FROM_VALUE},4))
to convert '2011093000000000' to MM/DD/YYYY format is affecting existing dates.
Any suggestion.....
Thanks,
CoachGBT
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 Sep 2011 at 8:34am |
not sure what you mean...?
Some rows of the same field have a different format than
'YYYYDDMM00000000000' ?
If so what is the other format for the string like? Edited by DBlank - 22 Sep 2011 at 8:34am
|
| IP Logged |
|
CoachGBT
Newbie
Joined: 08 Apr 2011
Online Status: Offline
Posts: 29
|
Posted: 22 Sep 2011 at 10:27am |
The other record dates are of format 09/22/2011, while this record date is 2011093000000000. So, when I attempt to use this date function:
DATE(RIGHT({TRHI.MISC_FT_FROM_VALUE},7)+"/"+MID({TRHI.MISC_FT_FROM_VALUE},5,2)+"/"+LEFT({TRHI.MISC_FT_FROM_VALUE},4))
I get an error because of the record dates format of 09/22/2011. Should I start out with an IF statement, i.e.;
IF {TRHI.MISC_FT_FROM_VALUE} <> DATE({TRHI.MISC_FT_FROM_VALUE}, 'MM-DD-YYYY') then
DATE(RIGHT({TRHI.MISC_FT_FROM_VALUE},7)+"/"+MID({TRHI.MISC_FT_FROM_VALUE},5,2)+"/"+LEFT({TRHI.MISC_FT_FROM_VALUE},4))
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 Sep 2011 at 10:31am |
try using the isdate()...
if isdate({TRHI.MISC_FT_FROM_VALUE}) then date({TRHI.MISC_FT_FROM_VALUE}) else
DATE(RIGHT({TRHI.MISC_FT_FROM_VALUE},7)+"/"+MID({TRHI.MISC_FT_FROM_VALUE},5,2)+"/"+LEFT({TRHI.MISC_FT_FROM_VALUE},4))
|
| IP Logged |
|
CoachGBT
Newbie
Joined: 08 Apr 2011
Online Status: Offline
Posts: 29
|
Posted: 22 Sep 2011 at 10:56am |
DBlank,
Genius...Thank you so much.
CoachGBT
|
| IP Logged |
|
Robotacha
Groupie
Joined: 11 Nov 2009
Location: United Kingdom
Online Status: Offline
Posts: 97
|
Posted: 23 Sep 2011 at 1:21am |
|
I agree, DBlank is genius :)
|
|
Regards,
Michael Jones
|
| IP Logged |
|
|
|
