[bravogila]

Hi guys, looking for help with a problem with a small database I am setting up.
My dbase has only 1 table which is

-Table-
Truck Number Plate(primary key)
Driver License Number
Driver License Expiry Date
Road Tax Number
Road Tax Expiry Date


Now the problem I have is when creating a query to 'flag up' expiry dates 30 day before it does expire. I have no problems in creating the SELECT statement or FROM statement (as is always the case), and most of the WHERE statement i.e. linking the tables. I know I cannot just subtract a integer from the expiry date, this is where I am struggling....

I have an inkling that I could use DATEDIFF but I have no idea how the SELECT statement should read like so that I can generate a report that lists the trucks that are expiring 30 days from today(now). I'm a noob with sql stuff. Any help would be greatly appreciated. Many thanks in advance!

[bravogila]

I did some research on the net and I think <code> Datediff('d', license expiry date, now() ) </code> could be what I should be looking at. But I don't know how the the other lines of codes should look like to make it all work.

Any help is greatly appreciated Many many thanks

This post has been edited by bravogila: 19 September 2007 - 10:55 AM

[orcasquall]

Add the following in your where clause for expiry dates exactly 30 days from now

datediff(dd, getdate(), expiry_date) = 30

Use this for expiry dates within 30 days

datediff(dd, getdate(), expiry_date) >= 0 and datediff(dd, getdate(), expiry_date) <= 30

The additional check for >=0 is because records with expired dates are also retrieved (which gives a negative value for the datediff() function).

Hope this helps!

This post has been edited by orcasquall: 21 September 2007 - 07:09 AM

[skyhawk133]

Thanks for helping orcasquall and welcome to dream.in.code!

[bravogila]

Thanks Aquascall and Skyhawk.

Ok this is what i have done but I still get a syntax error. Thanks helping out a noob dudes.

SELECT
  fms.PlateNo,
  fms.IDNo,
  fms.VehicleType,
  fms.VehicleDescription,
  fms.PermitNo,
  fms.PermitStartDate,
  fms.PermitExpireDate,
  fms.RoadTaxExpireDate,
  fms.PuspakomExpireDate,
  fms.InsuranceCompany,
  fms.InsuranceStartDate,
  fms.InsuranceExpireDate,
  fms.Owner,
  fms.Summons,
  fms.Incidents,
  fms.Notes,
  fms.`Status`
FROM
  fms
WHERE
  (datediff(dd, getdate(), fms.PermitExpireDate) >= 0) AND (datediff(dd, getdate(), fms.PermitExpireDate) <= 30 )

This post has been edited by bravogila: 22 September 2007 - 02:50 AM

[orcasquall]

It will help very much if you can tell us what database you're using (Access, SQL Server, Oracle or others), and the syntax error you got.

Two possible reasons why your query statement failed:
1) The datediff() function is not available in your database. In which case, you'd have to tell us what kind of database you're using before we can help you.

2) fms.PermitExpireDate is not of the datetime data type. If this is the case, I'm guessing you're using the char(8) data type (for storing dates in YYYYMMDD format). Then try this

datediff(dd, getdate(), convert(datetime,fms.PermitExpireDate,112) ) = 30

The 112 is a format descriptor code representing the YYYYMMDD format.

[PsychoCoder]

This one seems to parse in SQL Server

SELECT
  fms.PlateNo,
  fms.IDNo,
  fms.VehicleType,
  fms.VehicleDescription,
  fms.PermitNo,
  fms.PermitStartDate,
  fms.PermitExpireDate,
  fms.RoadTaxExpireDate,
  fms.PuspakomExpireDate,
  fms.InsuranceCompany,
  fms.InsuranceStartDate,
  fms.InsuranceExpireDate,
  fms.Owner,
  fms.Summons,
  fms.Incidents,
  fms.Notes,
  fms.Status
FROM
  fms
WHERE
  (datediff(dd, getdate(), fms.PermitExpireDate) >= 0) AND (datediff(dd, getdate(), fms.PermitExpireDate) <= 30 )

You had single quotes around Status, I removed them and it parsed just fine.

[bravogila]

Hi Orca.

Answer to your questions :
1) I'm using MYSQL 5.0.41
2) I'm using DATE data type and my date is stored as DDMMYYYY.

Appreciate the help. Many thanks!

Hi physcho, tried removing that even but it still doesn't work.

[PsychoCoder]

I believe you're doing the DATEDIFF function wrong

Quote

DATEDIFF(expr1,expr2)

DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
-> 1
mysql> SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');
-> -31

SELECT
  fms.PlateNo,
  fms.IDNo,
  fms.VehicleType,
  fms.VehicleDescription,
  fms.PermitNo,
  fms.PermitStartDate,
  fms.PermitExpireDate,
  fms.RoadTaxExpireDate,
  fms.PuspakomExpireDate,
  fms.InsuranceCompany,
  fms.InsuranceStartDate,
  fms.InsuranceExpireDate,
  fms.Owner,
  fms.Summons,
  fms.Incidents,
  fms.Notes,
  fms.Status
FROM
  fms
WHERE
  (datediff(Now(), fms.PermitExpireDate) >= 0) AND (datediff(Now(), fms.PermitExpireDate) <= 30 )

[bravogila]

Thanks Pyscho. It's working perfect now. It was the getdate() I used now() and it is working fine now. Hahaha.My bad man
Thanks lots for all your help guys. You're all good ppl!!! Cheers!!

PsychoCoder, on 22 Sep, 2007 - 09:47 AM, said:

I believe you're doing the DATEDIFF function wrong

Quote

DATEDIFF(expr1,expr2)

DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
-> 1
mysql> SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');
-> -31

SELECT
  fms.PlateNo,
  fms.IDNo,
  fms.VehicleType,
  fms.VehicleDescription,
  fms.PermitNo,
  fms.PermitStartDate,
  fms.PermitExpireDate,
  fms.RoadTaxExpireDate,
  fms.PuspakomExpireDate,
  fms.InsuranceCompany,
  fms.InsuranceStartDate,
  fms.InsuranceExpireDate,
  fms.Owner,
  fms.Summons,
  fms.Incidents,
  fms.Notes,
  fms.Status
FROM
  fms
WHERE
  (datediff(Now(), fms.PermitExpireDate) >= 0) AND (datediff(Now(), fms.PermitExpireDate) <= 30 )