Hibernate Community • View topic - HOUR() and MINUTE() functions in Native SQL

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

HOUR() and MINUTE() functions in Native SQL

Page 1 of 1

[ 5 posts ]

Previous topic | Next topic

Author

Message

jah_404

Post subject: HOUR() and MINUTE() functions in Native SQL

Posted: Thu Aug 09, 2012 9:34 am

Newbie

Joined: Wed Aug 01, 2012 5:51 am
Posts: 8

Hi !

I need to get the hour and minute from dates and I'm using Native SQL, MONTH() and DAY() work perfectly, but I get an error if I change them to HOUR() or MINUTE()

Do you know a way to get them ?

thanks

Top

Kenny_Zhang

Post subject: Re: HOUR() and MINUTE() functions in Native SQL

Posted: Thu Aug 09, 2012 9:46 pm

Newbie

Joined: Tue Aug 07, 2012 4:58 am
Posts: 4

hi, jah_404

if your database is Oracle, you can try this:

SELECT EXTRACT(HOUR FROM TO_TIMESTAMP('01-1月-2005 19:15:26', 'DD-MON-YYYY HH24:MI:SS')) AS HOUR,
EXTRACT(MINUTE FROM TO_TIMESTAMP('01-1月-2005 19:15:26', 'DD-MON-YYYY HH24:MI:SS')) AS MINUTE,
EXTRACT(SECOND FROM TO_TIMESTAMP('01-1月-2005 19:15:26', 'DD-MON-YYYY HH24:MI:SS')) AS SECOND
FROM dual;

it works in my local.

best wishes.

Top

jah_404

Post subject: Re: HOUR() and MINUTE() functions in Native SQL

Posted: Fri Aug 10, 2012 3:03 am

Newbie

Joined: Wed Aug 01, 2012 5:51 am
Posts: 8

Thank you for your answer Kenny_Zhang !
unfortunately I'm using SQL Server and it doesn't seem to work

Top

jah_404

Post subject: Re: HOUR() and MINUTE() functions in Native SQL

Posted: Fri Aug 10, 2012 3:53 am

Newbie

Joined: Wed Aug 01, 2012 5:51 am
Posts: 8

Thank to your post, I realized that using native sql request depends on the DBMS
As I'm using SQL Server, the way to get the hour of a date is :

Code:

session = HibernateUtil.currentSession();

String sql_query = "SELECT" +
      " DATEPART(hh,DH_departTrain) AS hour," +
      " Id" +
      " FROM Circulation" +
      " WHERE Id = 1722";

@SuppressWarnings("unchecked")
Query query = session.createSQLQuery(sql_query)
.addScalar("hour", Hibernate.INTEGER)
.addScalar("Id", Hibernate.INTEGER);

Top

Kenny_Zhang

Post subject: Re: HOUR() and MINUTE() functions in Native SQL

Posted: Fri Aug 10, 2012 7:09 am

Newbie

Joined: Tue Aug 07, 2012 4:58 am
Posts: 4

jah_404 wrote:

Thank to your post, I realized that using native sql request depends on the DBMS
As I'm using SQL Server, the way to get the hour of a date is :

Code:

session = HibernateUtil.currentSession();

String sql_query = "SELECT" +
      " DATEPART(hh,DH_departTrain) AS hour," +
      " Id" +
      " FROM Circulation" +
      " WHERE Id = 1722";

@SuppressWarnings("unchecked")
Query query = session.createSQLQuery(sql_query)
.addScalar("hour", Hibernate.INTEGER)
.addScalar("Id", Hibernate.INTEGER);

hi, Jah_404

yes, you can use DATEPART function to get the hour of a date, if you are using SQL Server.

but I think this is not a good thing, if you migrate your data from SQL Server to Oracle one day.

so, maybe you should figure out another solution that is Independent of DBMS.

Top

Page 1 of 1

[ 5 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum