[Solved] Update Query to enter Current Date in format

[jdwilcock]

This is probably a really easy question to answer. All I want to do is to fill an exising column with the current date in the format MM-DD-YY using an Update sql query. Using Curdate() gives me YYYY-MM-DD. Is there a way to change the format of the date using a single update query?

[Villeroy]

Update queries are issued to the database engine. Database engines follow standards.
You can write the required data through a form or dialog into a table and then combine the update query with a view or something.

[jdwilcock]

Thanks Villeroy. I will be running the sql command directly. This is the statement I am using:

UPDATE "Table1" SET "OrderDate" = CURDATE() ;

Which works fine to poplute all the OrderDate fields with the current date. However, the format is in yyyy-mm-dd. I would like it to be in the mm-dd-yy format. Is there a way to do this with an update query? Even mm-dd-yyyy would be acceptable.

[Villeroy]

A date value is not stored in a particular format. You can choose any format you want.
However, a text value may be stored in ISO format. I never tried to store the return value of CURDATE() in a text field though.

Edit: Just tried and indeed curdate() stored in a text field stores the 10 characters "2015-07-02". No, you can not apply any date format to this text

[Sliderule]

Thanks Villeroy. I will be running the sql command directly. This is the statement I am using:

UPDATE "Table1" SET "OrderDate" = CURDATE() ;

Which works fine to poplute all the OrderDate fields with the current date. However, the format is in yyyy-mm-dd. I would like it to be in the mm-dd-yy format. Is there a way to do this with an update query? Even mm-dd-yyyy would be acceptable.

Just as an FYI ( For Your Information ) . . . you did not tell us ( the forum ) the database back-end, product and version, you are using.

You can get this information from the STATUS BAR at the bottom, after opening your database.

If, you are using the OpenOffice / LibreOffice Embedded Version ( that is HSQL 1.8.0.10 ) . . . then, there is no 'direct' function to do it as you say.

But, if you, for example, you are using the highly recommended SPLIT DATABASE . . . with . . . HSQL Version 2.3.2 or HSQL Version 2.3.3 . . . then, you, can use the built in function TO_DATE to convert a text string to a database DATE field.

For example ( various examples ):

Code: Select all

UPDATE "Table1" SET "OrderDate" = TO_DATE('07/02/2015', 'MM/DD/YYYY') ;

UPDATE "Table1" SET "OrderDate" = TO_DATE('07/02/15', 'MM/DD/YY') ;

UPDATE "Table1" SET "OrderDate" = TO_DATE('07-02-2015', 'MM-DD-YYYY') ;

UPDATE "Table1" SET "OrderDate" = TO_DATE('07-02-15', 'MM-DD-YY') ;

UPDATE "Table1" SET "OrderDate" = TO_DATE('07/02/2015', 'DD/MM/YYYY') ;

UPDATE "Table1" SET "OrderDate" = TO_DATE('07/02/15', 'DD/MM/YY') ;

UPDATE "Table1" SET "OrderDate" = TO_DATE('07-02-2015', 'DD-MM-YYYY') ;

UPDATE "Table1" SET "OrderDate" = TO_DATE('07-02-15', 'DD-MM-YY') ;

UPDATE "Table1" SET "OrderDate" = TO_DATE('07.02.2015', 'DD.MM.YYYY') ;

UPDATE "Table1" SET "OrderDate" = TO_DATE('07.02.15', 'DD.MM.YY') ;

Explanation: HSQL 2.3 function TO_DATE is documented at ( scroll down to find TO_DATE , and, Table 10.1 to see the format codes accepted ):

http://www.hsqldb.org/doc/2.0/guide/bui ... _functions

Just thought I would mention it. I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[jdwilcock]

Thanks guys. I am using openoffice Base. It's not a big deal because I can just use an Update function to enter the date as text. But it would be a little nicer not to have to change the date each day.

[Villeroy]

Code: Select all

ALTER TABLE "TableName" ADD COLUMN "TempDateColumn" DATE DEFAULT CURRENT_DATE;
UPDATE "TableName" SET "TempDateColumn" = CAST("DateColumn" AS DATE);
ALTER TABLE "TableName" DROP COLUMN "DateColumn";
ALTER TABLE "TableName" ALTER COLUMN "TempDateColumn" RENAME TO "DateColumn";
ALTER TABLE "TableName" ALTER COLUMN "DateColumn" SET NOT NULL;

Replace "TableName" and "DateColumn" with your actual table name and column name and run it in the Tools>SQL dialog.
Now you have a true date column with the same name and data as the old column with wrong text values.
Easy, isn't it?