SQL Date Help

[JamesBowtell]

Hey,

I'm creating a service program in Access 2003

I have two tables:

tbl_Appliance
ApplianceNo (Primary Key)
SerialNo
ApplianceType
ApplianceManufacturer
ApplianceModel
ApplianceDescription

tbl_NextService
Nxt_Service_ID (Primary Key)
Appliance_ID (Foreign Key)
Nxt_Service_Date

as you can see the tbl_Appliance stores all data about the appliance, the tbl_NextService table stores the next service date of that appliance.

What i need to do is find all the dates stored within the database that match the month of the Computers system date. is this possible using access?

e.g.

if i have the date 23/03/2005 within the tbl_NextService table, this date will be queried as the system date is the 14/03/2005 (Date is in the same month as the system date)

SELECT tbl_Appliance.*, tbl_NextService.Nxt_Service_Date
FROM tbl_Appliance INNER JOIN tbl_NextService ON tbl_Appliance.Appliance_No = tbl_NextService.Appliance_ID

how would i add a WHERE clause to the above SQL statement

Thanks

[techgnome]

SELECT tbl_Appliance.*, tbl_NextService.Nxt_Service_Date
FROM tbl_Appliance INNER JOIN tbl_NextService ON tbl_Appliance.Appliance_No = tbl_NextService.Appliance_ID
WHERE MONTH(tbl_NextService.Nxt_Service_Date) = MONTH(NOW)
  AND YEAR(tbl_NextService.Nxt_Service_Date) = YEAR(NOW)

Think that will do it.....if you are using Access. Otherwise, if you are using SQL Server, replace NOW with GETDATE()

Tg

[JamesBowtell]

Hey techgnome,

works great thanks

Cheers

[JamesBowtell]

HEY again

thanks again for the SQL

whould it also be possible to have the SQL WHERE clause you gave me to work to find dates that are in that WEEK thanks

[techgnome]

Hmmm.....I've never tried that, but if there is a WEEK function, then it would be the same, except replace MONTH with WEEK.... But that's if it exists. IF it doesn't then you may need to look at the Format function and see if there is an option in there that returns the week number.

Tg

[JamesBowtell]

Tired the WEEK function but seems not to exist..oh well....

I have another question for you.

How would i query the current system date to data in the tables

e.g

If the date today is the 15/03/2005 then all the dates 15/03/2005 in the table will be quiried...any idea how i would do tha?

i think i could use the Date() function but not sure how to set the SQL

Any help would be great

[JamesBowtell]

Hey

i have a solution to finding dates that is between the system date and the next seven days anyway. (Technically a week...lol)

SELECT tbl_Appliance.*, tbl_NextService.Nxt_Service_Date
FROM tbl_Appliance INNER JOIN tbl_NextService ON tbl_Appliance.Appliance_No = tbl_NextService.Appliance_ID
WHERE (((tbl_NextService.Nxt_Service_Date) Between Date() And Date()+7));

The above SQL finds dates that are between the current date and the date in seven days time...

[RobDog888]

Since you are only asking for the date and not the time then that will work fine. Another syntax is
the SomeDate >= SomeOtherDate And SomeDate < SomeEndDate + 1.

When you think about it, its not that complicated as some people make it out to be.