QuerySpec get current date

Posts   
 
    
scotru
User
Posts: 104
Joined: 16-Feb-2006
# Posted on: 16-Apr-2018 19:08:38   

Is there a built in way to map to the database's method for getting the current DATETIME in QuerySpec? For example GETDATE() in SQL Server or NOW() in MySQL?

I'm trying to map the following MySQL SQL fragment to QuerySpec:

IF (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(birthday, '00-%m-%d')) < 18, 1, 0) AS is_underage

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 16-Apr-2018 20:53:36   

Though for such functions I'd fall for code side date (i.e. getting the current date at the back end server rather than the DB).

But you can use FunctionMappings for such non built-in functions.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 17-Apr-2018 11:13:42   

Dateformat isn't mapped by default for MySQL so indeed you need to add a custom function mapping for that function. If you can't figure it out, just post what you got that doesn't work and we'll help you through it.

DateAdd() is mapped for MySQL as a function. (so in queryspec you can use DateTimeFunctions.AddDays etc.) and the Day/Year/Month etc. properties of DateTime are mapped as well, and which are available as properties on DateTimeFunctions as methods. So e.g.

myQuery.Select(DateTimeFunctions.Year(YourEntityFields.Birthday).As("BirthdayYear"))

selects the year value of the birthday field from the query in myQuery.

The function to map you're after is likely 'DateDiff' which substracts two dates, so you can do birthday + 18 years (using DateAdd, which is mapped) and then check whether datediff that with NOW() gives a negative or a positive value. (

DateDiff(now(), DateAdd(birthday, YEAR, 18)) > 0

-> under age )

Frans Bouma | Lead developer LLBLGen Pro