I have a db datetime field that is in the long format; eg., 2003/09/24 15:00:33.201811

How can I convert it to shorter; eg., 2003/09/24 15:00:33

If this date has an internal format of A25, you first can convert it to
NEWDATE/A19=EDIT(OLDDATE,'9999999999999999999$$$$$$$$');
then the milliseconds are gone.

But the old date field is a date timestamp field; USAGE=HYYMDm, ACTUAL=HYYMDm. EDIT() would not work. You get an error:
INCORRECT USE OF DATE-TIME FIELD OR CONSTANT

Check out the HDATE function in Help.

HDATE converts datetime to DATE. I'll lose the time portion.

quote:

How can I convert it to shorter; eg., 2003/09/24 15:00:33

DATE_X/A20 = HCNVRT(DATE_COLUMN,'(HYYMDs)', 26, 'A20');

EDIT: Had the incoming date length incorrect; Check out the documentation...

This message has been edited. Last edited by: Tom Flynn,

Try something a bit simpler.

NEWDATE/HYYMDS = db_field;


I was able to do the following on a NORTHWIND table in SQL server on R714, so I expect the reverse would work.

TABLE FILE ORDERS
PRINT ORDERDATE
COMPUTE NEWDT/HYYMDm = ORDERDATE;
IF RECORDLIMIT EQ 1
END
 

Pipster,

OOOPPPSSS!!! My Bad...

HYYMDm not HYYMDs

MILLISECONDS - m, not SECONDS - s, THANKS!!!

I hope Mike let's us know if that's an efficient solution; I like your idea, as well...

This message has been edited. Last edited by: Tom Flynn,

thanks to both Piipster & Tom. I do like Piipster's solution better. It is easier and simpler. As a matter of fact, I still have to play with Tom's suggestion to make it work, which I am sure it will. Reason being that HCNVRT will return blank if the specified length is smaller than needed, which means I have to specific a longer length than I want to get the return value and then use EDIT or some other function to truncate the unwanted portion.

Hope I did not confuse you guys. Thanks!