I have a date/time computed field, in the value of the the field i have the word Date. I have the sort to sort in desending order so the date will be newest to oldest. But the sort is not sorting correctly, it is out of order. I looked at the data type of the date field in the document properties and it shows the data type as text. How do i convert this field to date/time field? I’ve already tried @Date(Date), Date being the fieldnamebut that didn’t seem to work.
Subject: RE: Convert Data Type
If the value of the field contains the word “Date”, obviously it can’t be a date-time value. You are trying to convert it to a real date/time, which is good, but the automatic conversion functions will not necessarily be able to pick out the date from a longer string. You have to process the string and find where the date appears in it, and throw away the rest of the junk, before you convert it.
Also, of course, the conversion follows the default date formatting conventions on the system running the code. If the date is formatted in some other way, you’ll have to do more work to either interpret the date yourself, or convert it to a syntax that your computer will recognize.
Subject: RE: Convert Data Type
Thank you for your response, but i’m not sure i’m following what you are saying. The date field displays the date in this format 5/1/2008. What i’m trying to figure out is even though I have the field property on the Date field as ‘Date/Time’ why is it reading it as a text. In your repsonse you say to convert it to a syntax that my computer will recognize, i’m not sure i’m following you there? So are you saying that the the @Date, or @Time functions that are available will not help convert my text data type of 5/1/2008 into a a date/time data type?
Subject: Convert Data Type
Sounds like you have a number of issues. First:
Normally, anything displayed on a view is converted to “text.” If you leave the column type as “General”
Therefore, if you display a date as text and sort it, unless your regional settings are set to yyyy(sep)mm(sep)dd. Dates won’t sort by “date” but are storted by the text string. You should get better results if you change the style to just date
Just to confirm the field is called “date,” it’s a computed field. The value/formula is date. The data type is Text.
" I looked at the data type of the date field in the document properties and it shows the data type as text." makes it sound like the original developer committed one of the basic sins of coding and took a date and converted it to text, completely ignoring the fact date formats are different in different part of the world. Meaning you have no idea if you see 01/02/03 means 2001-Feb-03 or Jan-02-2003 or whatever else…
If your company has a “standard” and you can guarantee the format then there are things you can do the fix the data. First change the data type to date/time. Then you “scrub” the existing data to convert the text values back to date/times. This will ONLY work if all the dates were entered using one standard format.
Otherwise, you need to handle them case by case.
Subject: RE: Convert Data Type
Sorry, this is incorrect information:
anything displayed on a view is converted to “text.” If you leave the column type as “General”
As I explain here, there is no such thing as a column type. You don’t need to mess with the Style options unless you’re unhappy with the way the column displays. It has no effect on sorting.
Naturally, dates must always be converted to text when they are displayed, but this happens on the workstation as the value is displayed – not on the server.
As for Melissa’s questions:
The date field displays the date in this format 5/1/2008.
I don’t understand what you mean by this. 5/1/2008 is not a format. mm/dd/yyyy is a format. I can’t tell whether this is 1 May or 5 January. The date field only displays the date in a particular format if the date is a date. If a field contains a text value, it will not be “formatted” as a date because the system doesn’t know it’s a date.
even though I have the field property on the Date field as ‘Date/Time’ why is it reading it as a text.
You said the document properties show the item as being of type text. You didn’t specify whether you were looking at the document properties from a view or from a document window. It makes a difference. When you look from a view you’re seeing the data that’s really stored in the note. When you look from a document window you’re not looking at the document on disk, but the document in memory (which might be being edited, and which might contain “computed for display” fields that aren’t stored – so it could be different from the document on disk).
A document can store any items with any data types. The design of the form makes no difference to what data are stored in your document, unless you use the form to edit and save that document. Even in that case, you can have code that assigns the field (for instance, in the Input Translation formula) and assigns it a different datatype than the type you’ve specified in the form design.
That means two things:
If you edit the design of a database and change the datatype of a field on a form, you must also take action to update your old documents, because they are not affected by this change until they are edited and saved.
It’s up to you to make sure that any code that assigns the field, assigns it a value consistent with the datatype defined on the form. You can override this very easily.
As an example of the second point, suppose the field is of type Date but the input translation formula is as follows:
@Text(Date; “D0S0”)
The formula returns a text value, so when you save the document, a text value is stored even though this is different from the purported datatype. When you sort these values in a view, they sort alphabetically.
The other thing you said was that “in the value of the the field i have the word Date”. Perhaps I misunderstood this. It sounds like you’re saying that the value of the field is not the text string “5/1/2008”, but (let’s say) “Date: 5/1/2008”. The former value can be automatically converted to a date; the latter cannot, because the text-to-date conversion doesn’t understand the word “Date”. You would have to write code to isolate the part of the string that is a date, and convert that.
In your repsonse you say to convert it to a syntax that my computer will recognize, i’m not sure i’m following you there? So are you saying that the the @Date, or @Time functions that are available will not help convert my text data type of 5/1/2008 into a a date/time data type?
Subject: RE: Convert Data Type
Thanks Andre for the corrections to my posting. I didn’t know you could “flip” the type using Input Translation. Not that I think I would ever want to do this but it’s an interesting learning.