9 Replies - 3717 Views - Last Post: 22 September 2007 - 10:37 AM Rate Topic: -----

#1 bravogila  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 19-September 07

working with dates in SQL

Post icon  Posted 19 September 2007 - 10:42 AM

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!

Is This A Good Question/Topic? 0
  • +

Replies To: working with dates in SQL

#2 bravogila  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 19-September 07

Re: working with dates in SQL

Posted 19 September 2007 - 10:51 AM

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

Was This Post Helpful? 0
  • +
  • -

#3 orcasquall  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 12
  • View blog
  • Posts: 158
  • Joined: 14-September 07

Re: working with dates in SQL

Posted 21 September 2007 - 07:07 AM

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

Was This Post Helpful? 0
  • +
  • -

#4 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1875
  • View blog
  • Posts: 20,282
  • Joined: 17-March 01

Re: working with dates in SQL

Posted 21 September 2007 - 08:14 AM

Thanks for helping orcasquall and welcome to dream.in.code!
Was This Post Helpful? 0
  • +
  • -

#5 bravogila  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 19-September 07

Re: working with dates in SQL

Posted 22 September 2007 - 02:49 AM

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

Was This Post Helpful? 0
  • +
  • -

#6 orcasquall  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 12
  • View blog
  • Posts: 158
  • Joined: 14-September 07

Re: working with dates in SQL

Posted 22 September 2007 - 06:16 AM

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.
Was This Post Helpful? 0
  • +
  • -

#7 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: working with dates in SQL

Posted 22 September 2007 - 08:39 AM

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.
Was This Post Helpful? 0
  • +
  • -

#8 bravogila  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 19-September 07

Re: working with dates in SQL

Posted 22 September 2007 - 09:34 AM

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.
Was This Post Helpful? 0
  • +
  • -

#9 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: working with dates in SQL

Posted 22 September 2007 - 09:47 AM

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 )


Was This Post Helpful? 0
  • +
  • -

#10 bravogila  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 19-September 07

Re: working with dates in SQL

Posted 22 September 2007 - 10:37 AM

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

View PostPsychoCoder, 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 )


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1