Displaying Date and Time in prefered format in table

[den1s]

Hi,

My table contains a Date field and a Time field. I want to store the date as

25/09/07 instead of 25/09/2007

and time as

21:45 instead of 09:45:00 00 pm

Can someone please advise how i can change the table settings to accomplish this.

Thanks,

Denis

[Stan Mathews]

Re: Displaying Date and Time in prefered format in table

Hi,

My table contains a Date field and a Time field. I want to store the date as

25/09/07 instead of 25/09/2007

and time as

21:45 instead of 09:45:00 00 pm

Can someone please advise how i can change the table settings to accomplish this.

Thanks,

Denis

You can't change the storage format nor should you care how the date/time is stored. What you need is formatting where these values are displayed (or a calculated field which will display in the format you want).

How would you want
09:45:33 22 pm
stored?

[den1s]

Re: Displaying Date and Time in prefered format in table

Stan,

The ultimate reason is to export this data to an excel spreadsheet. When i convert to excel and display the spreadsheet the time and date are in the wrong format. I want to change them to dd/mm/yy and hh:mm.

I am currently exporting as

export.field2 = "date"
export.field2 = "time"

I have a feeling that i need to convert these values, but not sure how.

Can you suggest the appropriate action?

Thanks in advance.

Denis

[Melvin Davidson]

Re: Displaying Date and Time in prefered format in table

Can someone please advise how i can change the table settings to accomplish this.

You do not/cannot change the "table" settings, as there are none. A table is merely a "container" for the data. Instead, when you create the fields in in design mode on a browse, form, report, etc, you just right click on the field, select properties, format tab, and then select the style you want from the format style drop down box.

[G Gabriel]

Re: Displaying Date and Time in prefered format in table

Displaying Date and Time

You phrased your question very appropriately.
As you said "displaying". You could make anything display anyway you want it to, but that won't do anything for the actual stored value.

The question then become: How do I change the stored value?
Possibly couple ways:
1-I never tried this, but if you change your Windows settings to use European or Canadian date or whichever date you want, then that's all there is to it.
otherwise,
2-Create couple other character fields, make them calculated ones, and store values in them compatible with your final desired format.

[Stan Mathews]

Re: Displaying Date and Time in prefered format in table

Stan,

The ultimate reason is to export this data to an excel spreadsheet. When i convert to excel and display the spreadsheet the time and date are in the wrong format. I want to change them to dd/mm/yy and hh:mm.

I am currently exporting as

export.field2 = "date"
export.field2 = "time"

I have a feeling that i need to convert these values, but not sure how.

Can you suggest the appropriate action?

Thanks in advance.

Denis

You will have to create calculated fields, in your table, and export these instead of the "real fields".

The expressions indicated should guide you to the appropriate expressions for these table level calculated fields.

dt = date()
? dt
= {09/25/2007}

? date_format(dt,"MM/dd/yy")
= "09/25/07"

tm = now()
? time("0h:0m",tm)
= "16:22"

[den1s]

Re: Displaying Date and Time in prefered format in table

Thanks Guys,

I will try it all out.

Denis

[den1s]

Re: Displaying Date and Time in prefered format in table

Sorry for sounding a bit dumb, but its quite late here in the UK.

How would i construct the syntax in the calc field in my table?

e.g.

dt = date_format(dt,"MM/dd/yy")

is this correct?

Denis

[den1s]

Re: Displaying Date and Time in prefered format in table

I tried the syntax and its not being accepted.

[G Gabriel]

Re: Displaying Date and Time in prefered format in table

Correct.
Another way:
dtoc(field,-2)

[G Gabriel]

Re: Displaying Date and Time in prefered format in table

I tried the syntax and its not being accepted

It should work assuming dt is a date field,i.e Type D
The syntax actually should be
format_date(dt,"dd/MM/yy")

[G Gabriel]

Re: Displaying Date and Time in prefered format in table

dt = date_format(dt,"MM/dd/yy")

That definitely would not work! it creates a circular referrence.

You should have a new field name:
new_dt=format(dt,"dd/MM/yy")

new_dt being the newly added calculated field.

[den1s]

Re: Displaying Date and Time in prefered format in table

I have tried both and get the following results;

format_date(dt,"dd/MM/yy")

Function not recognised

dtoc(dt,-2)

Argument is incorrect data type

Denis

[den1s]

Re: Displaying Date and Time in prefered format in table

Heres a screenshot

The actual Date field is 'rDate'
The new Character field is 'rDate2'

I placed the code in the calculated field section of field 'rDate2'.

Denis

[G Gabriel]

Re: Displaying Date and Time in prefered format in table

What type of field is dt?