Link to home
Start Free TrialLog in
Avatar of exp vg
exp vg

asked on

Access CDATE from Text

Please offer how I can use CDATE to convert a short text format field mm/yyyy to a date/format.

I have tried CDATE Left/Right to obtain place holders without success.

Thank you.
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

did you try

cdate([datefield]) ?
Avatar of exp vg
exp vg

ASKER

Yes - but the intent is to purposely only get the month and year field. Your suggestion present the full month, day, year.

I have researched examples that have the left/right parameters  - but I am unable to get this to work.

Thank you.
Avatar of exp vg

ASKER

I basically need to convert mm/dd/yyyy to mm/yyyy and have the latter in date/time format so I can sort correctly in my crosstab query.

I have tried all options without success.
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of exp vg

ASKER

Let me clarify - I did do the format option first - but this was transformed into a short text format - that does not sort properly.

What I prefer is the  to use the original date, and use a CDATE to obtain just the month and year from the original date.

Basically  is there a way to obtain CDATE - Left/Right to get just the month and year from the original date?

Thank you.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
when you use the Left/Right to get the month and year, you are converting the result to a Text data type. You can get a correct sorting of the Text date by using the format I posted above
The bottom line is, if you want proper sorting, you need to leave the sorting field as a date format.  A date format, by definition, ALWAYS has a day AND a time component to it.   Perhaps what you are looking for is to format the values in the display or reporting stage.  Alternatively, add another field for the formatted version and sort on the DateTime field that you get using CDate.

EDIT: Just to be clear, DateTime fields will have all of the components.  When you use Cdate with some like "7/2015", you will get back a DateTime where the DAY is set to a default of 1, and the time (I believe) is set to midnight.  (or is it 12:01.. still, point is the same)
<if you want proper sorting, you need to leave the sorting field as a date format. >
Not necessarily.. as I've posted above, it can be in text format.
Yes, Rey is correct, but proper sorting as a text value will require changing the order of the components so that year comes before month, as in yyyymm or yyyy-mm, etc...   If the desired output is mm/yyyy, there will still be the need for a formatting of the data at some point for user presentation.  

EDIT: So, essentially what I was getting at was if the information is starting out as a DateTime field (which I believe it is), then converting it to text prior to sorting it doesn't gain us any advantage because there is still going to be an additional step to deal with the formatting for output...   the conversion to text becomes an extra unnecessary step in the middle.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of exp vg

ASKER

Robert - exactly - I need the final format to be with the month followed by the year.

I do see now that conversion to text is not necessary (since I did not think that sorting on it would be an issue). This is much more of a challenge than I thought it would be.

To backtrack to the beginning - I will keep the original date as short date - mm/dd/yyyy. What is the easiest way to just extract the mm/yyyy in this order, in date/time?

Thank you.
The Crosstab Query Wizard, I believe, will take Dates and handle grouping them into intervals, such as month/year...   Have you tried using that to see if you can create what you want from scratch now that you have an understanding of the rest of it?
Avatar of exp vg

ASKER

Yes, the wizard will group - but the display will only indicate months - and I need months and years. I would also prefer a mechanism to store the coding that enables me to create a crosstab query with month/year as column headers.
Avatar of exp vg

ASKER

Thank you everyone - it looks like I will either have to manually enter criteria - or format after the output.
Avatar of exp vg

ASKER

Thank you.
Avatar of exp vg

ASKER

I ended up manually entering in the column header in properties - it was the only feasible way to make this work

Thank you everyone for your assistance.