Parse date portions of string 1

[Jacque]

Hi Everyone,
I need to be able to parse out the date(s) passed from user selected values that is passed in as a string that we normally use for display.

Examples:
Date Range is Before Current Time & Date
Date Range ,2002/10/11 00:00:00 thru 2003/03/12 15:21:18

The first example would be easy to weed out and I'd just use the default 24 hr time period for averaging but I need to be able to determine if the user selects a partial day and get the number of hours difference or if multiple days, then I'd just use a default 24 as the denominator in my formula.

I'm having early morning brain fade...

Any suggestions/help would be greatly appreciated.
Thanks in advance,

Jacque

 

[cmpgeek]

i am not sure i understand what you are needing... can you explain a little more about where you are going with it? sometimes that helpe me think through it...

 

[lyanch]

It is fairly straight forward to extract dates from strings *if* they are ALWAYS entered in the same format. If the date can be things like:

Date Range ,2002/10/11 00:00:00 thru 2003/3/12 15:21:18

instead of

Date Range ,2002/10/11 00:00:00 thru 2003/03/12 15:21:18

Then you will start to have problems.

Here is a sql expression example of string -> datetime for a time in format 2003-03-11T22:39:46.996000Z. This is Sybase syntax and also handles the problems of nulls and blanks.

convert (datetime, (nullif(substring (o2.Date , 6, 2) +"/" +
substring (o2.Date , 9, 2) + "/"+ substring(o2.Date , 1, 4) + " " +
substring(o2.Date , 12, 12), "//&quot), 101)


once you have the date times then it will be easy to datediff(h,d1,d2) <24 for a partial day.

Lisa

 

[Jacque]

Hi sstatzer,
My report displays by time period (hour) and within each time period, I display the name, number of missions and duration of all missions which happened during that time period. Then the number of missions and duration is totaled for each period.

At the end of the report, there is a grand total of missions and durations and then these totals are averaged based upon the number of time periods populated. If the user selected 10 hours but only 3 of the 10 time periods are populated, it averages by 3 not 10. I need to be able to average by the user selected time period if a partial day.

I need to take the two dates and compare them to see if the user selected a partial day (for example)
Date Range ,2003/03/11 00:00:00 thru 2003/03/11 10:00:00
which would give me 10 time periods. This would allow me to use the number of time periods to get my average.

Hopefully this makes more sense.

 

[pelajhia]

It sounds like the second example is the one needing work:
Date Range ,2002/10/11 00:00:00 thru 2003/03/12 15:21:18

You can search your string for the '/' symbol, or colons, and that will give you a place to start.
eg:

numbervar countchar:=1;
do while countchar<=length(string)
if string[countchar] = &quot;/&quot;
then
countchar:=countchar+1
...and so forth

Once you figure out where the first / is located (the value of countchar), you should be able to count back or forward to determine what part of the string is the first datetime and what part is the second datetime, move these into other variables and then do your comparisons. I just hope that your don't have too many different ways this can be entered!

 

[Jacque]

Hi Lisa,
Yes, the dates are always passed in the same format.
Jacque

 

[Jacque]

Hi Pelajhia,
I'll give that a try.
Thanks,
Jacque