Date and time formats in schema.ini wrt exported data

[Phil Stokes]

I am exporting data to a .txt file using TransferText with "Export Word for
Windows Merge" as type. this creates a schema.ini file with

col2 indx date

as a line in the file. This field indx holds a time in the format for
example 03:15
When this is exported it comes up in the text file as 30/12/1899 03:15:00.

I want the data to appear in the exported file as 03:15 but dont know how to
code this in the schema.ini file. I tried

col2 indx date "hh:mm"

and some others but none worked
There is also a field which comprises a date in the format dd/mm/yyyy that
also appears in the same format as above.

I would appreciate any help

 

[George Nicholson]

Date/Time fields hold both date and time values, to the right and left of
the decimal point, even if you aren't intentionally using both of them.
3:15 AM is stored as 0.1359375. Since zero equals December 30, 1899, that's
why it is showing up in your text file. As long as you are exporting a
Date/Time value, you will probably get both Date and Time exported.

Therefore, to get the results you want you probably need to convert your
Date/Time field to text during your export. If you are exporting directly
from a table, export from a query instead and use the Format function to
change your Date/Time to text within that query:

HHMMTime: Format([MyDateTimeField],"hh:mm")
(or)
HHMMTime: Format(#03:15#,"hh:mm")
(or)
HHMMTime: Format(#12/30/1899 03:15#,"hh:mm")

All return the same text result: 03:15.

MMDDYYYDate: Format([MyDateTimeField],"mm/dd/yyyy")

should work for your date field.

 

[John Nurick]

Hi Phil,

I've never tried it myself, but the documentation says there's a
DateTimeFormat key for schema.ini. It goes in the header between the
filename and the column descriptions, and controls formats for all
date/time fields in the file, e.g.
DateTimeFormat=mm.dd.yy.hh.mm.ss

Alternatively, modify the query you're exporting to get the date/time
fields the way you want them. Use Format() in calculated fields, e.g.
fIndx: Format([indx], "hh:mm")

See these links for information on schema.ini:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

Create a Schema.ini file based on an existing table in your database:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;155512

http://support.microsoft.com/default.aspx?scid=kb;EN-US;149090
http://www.devx.com/tips/Tip/12566