Datediff function?

  • Thread starter Thread starter aldunford
  • Start date Start date
A

aldunford

Guest
Hello. I'm trying to run a query that returnsa value by looking up a given
date in a range. The below works great however I don't want it to change
based on the year. I need it to look at the month/year and return the value
at that time. Here is my sql

SELECT [tblEmp Hire Info].*, tblNEAccruals.[Monthly Rate], [tblEmployee
Demographic Info].[Current Employee], [tblEmp Hire Info].[Hiring Status]
FROM tblNEAccruals, [tblEmployee Demographic Info] INNER JOIN [tblEmp Hire
Info] ON [tblEmployee Demographic Info].[SS# ID] = [tblEmp Hire Info].[SS# ID]
WHERE ((([tblEmployee Demographic Info].[Current Employee])=Yes) AND
(([tblEmp Hire Info].[Hiring Status])="NE") AND ((DateDiff("yyyy",[tblEmp
Hire Info].[Isac Original Hire Date],Date()))>=[tblNEAccruals].[From] And
(DateDiff("yyyy",[tblEmp Hire Info].[Isac Original Hire
Date],Date()))<[tblNEAccruals].[To]));


I did find another posting with something similar would this work for me?
Where would I put this in my above sql? Some of it is throwing me off when I
look at my own sql. Any help would be appreciated!! Thanks!!

SELECT BonnieA.DateSold, BonnieA.TermDate,
Int(DateDiff("d",[DateSold],[TermDate])/365) AS Years,
DateDiff("m",DateAdd("yyyy",Int(DateDiff("d",[DateSold],[TermDate])/365),[DateSold]),[TermDate]) AS Months
FROM BonnieA;
 
Re: Datediff function?


"aldunford" <aldunford@discussions.microsoft.com> wrote in message
news:29726FCD-3E83-456F-9533-A69902C341DB@microsoft.com...
> Hello. I'm trying to run a query that returnsa value by looking up a
> given
> date in a range. The below works great however I don't want it to change
> based on the year. I need it to look at the month/year and return the
> value
> at that time. Here is my sql
>
> SELECT [tblEmp Hire Info].*, tblNEAccruals.[Monthly Rate], [tblEmployee
> Demographic Info].[Current Employee], [tblEmp Hire Info].[Hiring Status]
> FROM tblNEAccruals, [tblEmployee Demographic Info] INNER JOIN [tblEmp Hire
> Info] ON [tblEmployee Demographic Info].[SS# ID] = [tblEmp Hire Info].[SS#
> ID]
> WHERE ((([tblEmployee Demographic Info].[Current Employee])=Yes) AND
> (([tblEmp Hire Info].[Hiring Status])="NE") AND ((DateDiff("yyyy",[tblEmp
> Hire Info].[Isac Original Hire Date],Date()))>=[tblNEAccruals].[From] And
> (DateDiff("yyyy",[tblEmp Hire Info].[Isac Original Hire
> Date],Date()))<[tblNEAccruals].[To]));
>
>
> I did find another posting with something similar would this work for me?
> Where would I put this in my above sql? Some of it is throwing me off
> when I
> look at my own sql. Any help would be appreciated!! Thanks!!
>
> SELECT BonnieA.DateSold, BonnieA.TermDate,
> Int(DateDiff("d",[DateSold],[TermDate])/365) AS Years,
> DateDiff("m",DateAdd("yyyy",Int(DateDiff("d",[DateSold],[TermDate])/365),[DateSold]),[TermDate])
> AS Months
> FROM BonnieA;
>

This is a Windows XP newsgroup. If you wish to attract the
attention of SQL experts then posting your question in an
SQL newsgroup would be a good idea.
 
Re: Datediff function?

aldunford wrote:
> Hello. I'm trying to run a query that returnsa value by looking up a given
> date in a range. The below works great however I don't want it to change
> based on the year. I need it to look at the month/year and return the value
> at that time. Here is my sql
>
> SELECT [tblEmp Hire Info].*, tblNEAccruals.[Monthly Rate], [tblEmployee
> Demographic Info].[Current Employee], [tblEmp Hire Info].[Hiring Status]
> FROM tblNEAccruals, [tblEmployee Demographic Info] INNER JOIN [tblEmp Hire
> Info] ON [tblEmployee Demographic Info].[SS# ID] = [tblEmp Hire Info].[SS# ID]
> WHERE ((([tblEmployee Demographic Info].[Current Employee])=Yes) AND
> (([tblEmp Hire Info].[Hiring Status])="NE") AND ((DateDiff("yyyy",[tblEmp
> Hire Info].[Isac Original Hire Date],Date()))>=[tblNEAccruals].[From] And
> (DateDiff("yyyy",[tblEmp Hire Info].[Isac Original Hire
> Date],Date()))<[tblNEAccruals].[To]));
>
>
> I did find another posting with something similar would this work for me?
> Where would I put this in my above sql? Some of it is throwing me off when I
> look at my own sql. Any help would be appreciated!! Thanks!!
>
> SELECT BonnieA.DateSold, BonnieA.TermDate,
> Int(DateDiff("d",[DateSold],[TermDate])/365) AS Years,
> DateDiff("m",DateAdd("yyyy",Int(DateDiff("d",[DateSold],[TermDate])/365),[DateSold]),[TermDate]) AS Months
> FROM BonnieA;
>
this is a windowsxp group. You might get more help from a group with
the word SQL in it.
Have you tried the help in SQL. I found it quite good, and it comes
with samples.
 
Re: Datediff function?

Perhaps posting in the SQL queries group would get the answer you are
looking for. This is a group for the Windows XP operating system. (try
link below)


http://www.microsoft.com/communitie...c76-df83-4203-bfc4-af63b54a0ad3&lang=en&cr=US

aldunford wrote:
> Hello. I'm trying to run a query that returnsa value by looking up a given
> date in a range. The below works great however I don't want it to change
> based on the year. I need it to look at the month/year and return the value
> at that time. Here is my sql
>
> SELECT [tblEmp Hire Info].*, tblNEAccruals.[Monthly Rate], [tblEmployee
> Demographic Info].[Current Employee], [tblEmp Hire Info].[Hiring Status]
> FROM tblNEAccruals, [tblEmployee Demographic Info] INNER JOIN [tblEmp Hire
> Info] ON [tblEmployee Demographic Info].[SS# ID] = [tblEmp Hire Info].[SS# ID]
> WHERE ((([tblEmployee Demographic Info].[Current Employee])=Yes) AND
> (([tblEmp Hire Info].[Hiring Status])="NE") AND ((DateDiff("yyyy",[tblEmp
> Hire Info].[Isac Original Hire Date],Date()))>=[tblNEAccruals].[From] And
> (DateDiff("yyyy",[tblEmp Hire Info].[Isac Original Hire
> Date],Date()))<[tblNEAccruals].[To]));
>
>
> I did find another posting with something similar would this work for me?
> Where would I put this in my above sql? Some of it is throwing me off when I
> look at my own sql. Any help would be appreciated!! Thanks!!
>
> SELECT BonnieA.DateSold, BonnieA.TermDate,
> Int(DateDiff("d",[DateSold],[TermDate])/365) AS Years,
> DateDiff("m",DateAdd("yyyy",Int(DateDiff("d",[DateSold],[TermDate])/365),[DateSold]),[TermDate]) AS Months
> FROM BonnieA;
>
 
Back
Top