Ask Your Question
0

Asking for example code for MySQL function DATEDIFF()

asked 2015-03-21 03:04:08 +0100

mrmister gravatar image

Hello I'm starting with functions and SQL / MySQL programming in LibreOffice Base, and I'm having some little problems to create my first programs. I need a bit of help with structure, notation, etc. Well right now I have a table and in that table I have a field with the employee DOB (Date of Born), that field is in Spanish sorry, and it is: "FechaNac" Then, I have other field that retrieve the current system date. I do that with the MySQL function CURDATE() In SQL I type... CURDATE( ) "FechaActual" That introduce the current system date in the field "FechaActual" Perfect. Now what I want is to obtain the difference in days between the date of born (DOB) "FechaNac" and the current system date "FechaActual" For that purpose we have a function called DATEDIFF() However, I'm typing this... DATEDIFF("FechaActual","FechaNac") AS Anos And that give me a syntax error :( so I'm doing something wrong... This is my piece of code:

SELECT "Nombre", "Apellido1ero", "Apellido2seg", "Sexo", "FechaNac",

CURDATE( ) "FechaActual"

DATEDIFF("FechaActual","FechaNac") AS Anos

FROM "tbl_EMPLEADOS"

Reference for DATEDIFF() can be found here http://www.w3schools.com/sql/func_dat...

What I want to do is finding the days of difference between "FechaActual" and "FechaNac". Having that information, I can divide the days of difference between both dates by 365 and then calculating the employee's years having it's DOB and the current system date. However, I don't know how to use well DATEDIFF() and I am getting that error...

What am I doing wrong? Any help would be really appreciated

Cheers

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2015-03-22 02:42:39 +0100

doug gravatar image

updated 2015-03-22 13:43:14 +0100

There are actually three more issues to be fixed.

  1. you cannot declare a new field in a query AND refer to that same new field in the same query; need to provide the definition for FechaActual in the function;
  2. this is one of those situations where HSQLDB syntax is slightly different, and it has a third criteria before the dates;
  3. the order of the date variables should be reversed, old to new for a positive value.

For those reasons, the desired query is:

SELECT "Nombre", "Apellido1ero", "Apellido2seg", "Sexo", "FechaNac",
CURDATE( ) "FechaActual", DATEDIFF('year', "FechaNac", CURDATE() ) AS "Anos" 
FROM "tbl_EMPLEADOS";
edit flag offensive delete link more

Comments

Doug works fine, nice piece of code. Thanks a lot. I'd like to understand how this works okay? I'm having some doubts in DATEDIFF, everything else is crystal clear. Ok, let's go. DATEDIFF we have there DATEDIFF(datepart,startdate,enddate)... okay, so... FechaNac it's a date already given by the user... the enddate would be our current day, so CURDATE()... okay what I don't understand here is 'year'... consulting the reference seems to be DATEDIFF can extract a certain value from...

mrmister gravatar imagemrmister ( 2015-03-24 02:38:23 +0100 )edit

a given date (in this case the date already entered by the user FechaNac, and the other date, in this case the current system date, so CURDATE()... and seems to the information we can gather up from these two vales can be retrieved in the datepart which could be... according the SQL reference year... quarter... month etc... and typing AS "Anos" what you are doing is creating an Alias to load those values in a new data column, this case Anos. Is that correct?

mrmister gravatar imagemrmister ( 2015-03-24 02:40:30 +0100 )edit

and of course you are retrieving year, but you could also retrieve according the MySQL reference for DATEDIFF year, quarter, dayofyear, day, week, weekday... is that right?

mrmister gravatar imagemrmister ( 2015-03-24 02:54:56 +0100 )edit

In MySQL the command always is implicitlyDATEDIFF('day', NewDate, OldDate) and only the dates are entered. The direct analog to the query above in MySQL might be roughly something like YEAR(NewDate) - YEAR(OldDate) - CASE WHEN MONTH(NewDate) < MONTH(OldDate) THEN 1 ELSE CASE WHEN MONTH(NewDate) = MONTH(OldDate) and DAY(NewDate) < DAY(OldDate) THEN 1 ELSE 0 END END

doug gravatar imagedoug ( 2015-03-24 05:09:11 +0100 )edit
1

answered 2015-03-21 03:26:22 +0100

Don't use w3schools, using the dev's documentation is the way to go. Stick to MySQL 5.6 (no reason to use anything older; 5.7 ain't out just yet; regular, Oracle MySQL generally gets work done, but I'd recommend MariaDB, a drop-in replacement fork with so much more features and higher performance).

If you're running the code on a MySQL Server, you're basically introducing syntax violations by using double quotes around column names and table name; you need to use https://en.wikipedia.org/wiki/Grave_a... since MySQL defaults to it. You could use double quotes by using a specific SQL mode, but I won't try to get that to work.

You forgot a comma, and you should add the terminating semicolon just in case:

SELECT `Nombre`, `Apellido1ero`, `Apellido2seg`, `Sexo`, `FechaNac`, CURDATE( ) `FechaActual`, DATEDIFF(`FechaActual`,`FechaNac`) AS `Anos` FROM `tbl_EMPLEADOS`;
edit flag offensive delete link more

Comments

I use documentation for MySQL 5.5 -- pretty sure MariaDB 10.x forked at that point.

doug gravatar imagedoug ( 2015-03-21 03:50:05 +0100 )edit

@doug, actually MariaDB 10.0 is MySQL 5.6. MariaDB 10.1 will be 5.7. MariaDB 5.5 and earlier are from respective MySQL.

rautamiekka gravatar imagerautamiekka ( 2015-03-21 03:52:59 +0100 )edit

Hi there, I tried that code... and when I run it... it doesn't yield any results... I've created in my database two fields... FechaActual... and... Anos... so both fields can be present and ready to load data when I type the SQL query. FechaActual is a DATE field and Anos is a NUMERIC field without decimals. Okay, I run that code and Anos get any result :( so it's like DATEDIFF was not working. Why?

mrmister gravatar imagemrmister ( 2015-03-22 02:03:56 +0100 )edit

This is the exact code I tried: SELECT "Nombre", "Apellido1ero", "Apellido2seg", "Sexo", "FechaNac",

CURDATE( ) "FechaActual", DATEDIFF("FechaActual","FechaNac") AS "Anos" FROM "tbl_EMPLEADOS";

mrmister gravatar imagemrmister ( 2015-03-22 02:04:27 +0100 )edit

I'M GIVING YOU A LINK TO DOWNLOAD THE ODB FILE HERE: https://mega.co.nz/#!s0JhhaoL!k8-jMR4...

mrmister gravatar imagemrmister ( 2015-03-22 02:06:12 +0100 )edit

Please, download the database and try to play a bit with the SQL query to find out why it's not working? I really don't know what to do... need help

mrmister gravatar imagemrmister ( 2015-03-22 02:08:18 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-03-21 03:04:08 +0100

Seen: 2,236 times

Last updated: Mar 22 '15