Asking for example code for MySQL function DATEDIFF()

English
, , ,
#1

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 :frowning: 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_datediff_mysql.asp

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

#2

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 Grave accent - Wikipedia 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`;
#3

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

#4

@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.

#5

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 :frowning: so it’s like DATEDIFF was not working. Why?

#6

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

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

#7

I’M GIVING YOU A LINK TO DOWNLOAD THE ODB FILE HERE: MEGA

#8

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

#9

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";
#10

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…

#11

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?

#12

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?

#13

In MySQL the command always is implicitly DATEDIFF('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

#14

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.