CODEWITHSUNDEEP
databasehibernatesql-server-2008datehql
Dhanyan KI
Dhanyan KI

Reputation: 21

how to format date in "YYYY-mm-dd" format using hql?

When I use MS SQL database, I do this to convert date in timestamp to "YYYY-mm-dd" format.

CONVERT(DATE, "Timestamp")

I need to do the same function using HQL.

Which function will remove all the time indices and give the date in "YYYY-mm-dd" format?

Upvotes: 2

Views: 27460

Answers (5)

chethan-gh
chethan-gh

Reputation: 31

Use this hive function:

date_format(mydatewithtimestamp,'dd-MM-yyyy')

Reference: https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

Upvotes: 3

isurujay
isurujay

Reputation: 1436

You can simply use TO_DATE function.

Upvotes: 0

Divyesh Kanzariya
Divyesh Kanzariya

Reputation: 3789

Use

DATE_FORMAT(DATE,'%Y-%m-%d')

instead of CONVERT().

Upvotes: 2

G.Noulas
G.Noulas

Reputation: 462

You can use the following that converts a date to a string with the specified format:

to_char(mydate,'yyyy-mm-dd')

Upvotes: 5

Cookalino
Cookalino

Reputation: 1619

You have to either use vendor-specific functions or extend the dialect. Unfortunately this isn't supported out of the box in Hibernate (not sure why as would have thought it was simple to implement).

Extending the dialect is simple and is a better option as it keeps your application database-agnostic and works seamlessly across the app.

Option 1: Extend the dialect(s)

Create classes for the dialects that you want to extend such as:

class ExtendedOracle10gDialect extends Oracle10gDialect {
    ExtendedOracle10gDialect() {
        // Add a generic 'date_format' function that can be used across databases
        registerFunction("date_format", new SQLFunctionTemplate(StandardBasicTypes.DATE, "to_char(?1, ?2)"));
    }
}

Then change your datasource to use the custom dialect, for example by changing hibernate.cfg.xml:

<property name="hibernate.dialect">com.mycompany.dialect.ExtendedOracle10gDialect</property>

You can then use DATE_FORMAT(MYDATE,'YYYY-MM-DD') in your HQL

Option 2: Use vendor-specific functions

MySQL / MariaDB:

DATE_FORMAT(MYDATE,'YYYY-MM-DD')

SQL Server:

FORMAT(MYDATE,'YYYY-MM-DD')

Oracle / PostgreSQL:

TO_CHAR(MYDATE,'YYYY-MM-DD')

Upvotes: 3

Related Questions