Question about a date query

karlshea · **Joined:** Thu Oct 16, 2003 3:26 pm **Posts:** 12

I'm trying to generate a list of people whose birthday is today. Previously these were sql stored procedures. This is the query I have right now:

Code:

select person 
from com.ci.birthdays.Person as person 
where ABS( DATEPART(dy, GETDATE()) - DATEPART(dy, DATEADD(yy, DATEDIFF(yy, birthday, GETDATE()), birthday)) ) = 0

It works, but I don't know if it will continue to work if I move off of MS SQL Server.

Does anyone know a way of doing this same query that would make it independant of the database it was on?

steve · **Posted:** Thu Oct 16, 2003 4:33 pm

How about:

Code:

Date rangeStart = null;
Date rangeEnd = null;
Calendar calendar = Calendar.getInstance();
calendar.clear(Calendar.MILLISECOND);
...
calendar.clear(Calender.HOUR_OF_DAY);
rangeStart = calendar.getTime();
calendar.add(Calendar.Date,1);
Query query = session.createQuery("from Person as p where p.birthday BETWEEN :rangeStart AND :rangeEnd");
query.setDate("rangeStart", rangeStart);
query.setDate("rangeEnd ", rangeEnd );
List results = query.list();
...

karlshea · **Joined:** Thu Oct 16, 2003 3:26 pm **Posts:** 12

The thing is, I have to ignore the year, because it's the full birthday in the database. So that's why I'm using all the weird dateadds and datediff.

I'm trying to get all the dates where their birthday (ignoring the year) is today. Which is hard to do when you try to take leap years and whatnot into account.

steve · **Posted:** Thu Oct 16, 2003 4:58 pm

The how about SQL Extension functions. http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec/jdbc-spec.frame11.html

They would not be supported everywhere, but would be more widely supported than SQLServer-specfic functions. My guess is that they would not be allowed in normal HQL and that you'd have to utilize Hibernate's native sql syntax.

karlshea · **Joined:** Thu Oct 16, 2003 3:26 pm **Posts:** 12

Those functions aren't supported by SQLServer at all.

So now I'm back to not being able to use Hibernate, unless I want to pass in SQLServer-specific functions, which kind of defeats the whole point of using it.

steve · **Posted:** Wed Oct 22, 2003 10:50 pm

Thats not true; SQL Server supports just about all of the SQL Extensions. Your JDBC driver for SQL Server may not, but SQL Server itself does.

Not sure how you came to this conclusion, but here's how to check which of these extensions you driver and db combo support:

Code:

Connection conn = ...;
DatabaseMetaData databaseMetaData = conn.getMetaData();

System.out.println("Supported SQL Extension numeric functions:");
System.out.println(databaseMetaData.getNumericFunctions());

System.out.println("Supported SQL Extension string functions:");
System.out.println(databaseMetaData.getStringFunctions());

System.out.println("Supported SQL Extension date/time functions:");
System.out.println(databaseMetaData.getTimeDateFunctions());

System.out.println("Supported SQL Extension system functions:");
System.out.println(databaseMetaData.getSystemFunctions());

karlshea · **Joined:** Thu Oct 16, 2003 3:26 pm **Posts:** 12

This is what I get:

Code:

Supported SQL Extension numeric functions:
abs,acos,asin,atan,atan2,ceiling,cos,cot,degrees,exp,floor,log,log10,mod,pi,power,radians,rand,round,sign,sin,sqrt,tan

Supported SQL Extension string functions:
ascii,concat,difference,left,length,lcase,ltrim,repeat,right,rtrim,soundex,space,substring,ucase,char

Supported SQL Extension date/time functions:
dayname,dayofmonth,dayofweek,dayofyearhour,minute,month,monthname,now,quarter,timestampadd,timestampdiffsecond,week,year

Supported SQL Extension system functions:
dbname,ifnull,username

But if I use one of those extensions, this is what I get:

Code:

Exception: net.sf.hibernate.JDBCException: Could not execute query: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]'DAYOFYEAR' is not a recognized function name.

This is the code:

Code:

select person from com.ci.birthdays.Person as person where ABS(DAYOFYEAR(:now) - DAYOFYEAR(TIMESTAMPADD(SQL_TSI_YEAR, TIMESTAMPDIFF(SQL_TSI_YEAR, birthday, :now), birthday))) = 0

steve · **Posted:** Wed Oct 22, 2003 11:34 pm

Yeah, thats not how you call SQL Extension functions. The SQL Extension syntax is like: {fn functionName()}. So in JDBC, some thing like:
"SELECT COUNT(*) FROM triggers WHERE time_to_fire < {fn now()}"

Please see the link I posted earlier regarding SQL Extension functions. Also note that I said this may or may not work in HQL. I have not tried it. If not, you may need to use Hibernate's new "native SQL" support.

karlshea · **Joined:** Thu Oct 16, 2003 3:26 pm **Posts:** 12

Great! That seems like it worked. Thanks for the help :)

steve · **Posted:** Thu Oct 23, 2003 12:40 pm

Did it work in HQL? Cool...

karlshea · **Joined:** Thu Oct 16, 2003 3:26 pm **Posts:** 12

Yeah, this is what I ended up with:

Code:

select person
from com.ci.birthdays.Person as person
where 
{fn ABS( 
    {fn DAYOFYEAR(:now)} - 
    {fn DAYOFYEAR( 
        {fn TIMESTAMPADD(SQL_TSI_YEAR,
            {fn TIMESTAMPDIFF(SQL_TSI_YEAR, birthday, :now)}, 
            birthday
        )}
    )}
)} = 0

gavin · **Posted:** Thu Oct 23, 2003 2:41 pm

we could perhaps mention that this works in tghe doco.

christian · **Posted:** Thu Oct 23, 2003 5:57 pm

Already noted :)

gavin · **Posted:** Thu Oct 23, 2003 6:10 pm

Thanks :)

steve · **Posted:** Thu Oct 23, 2003 11:24 pm

Very cool.

Hibernate rocks :)