using getdat() function

I am trying to use getdat() in a sql query and it is working great except it doesn't seem to return value for just the current date (EXAMPLE)




SELECT DISTINCT CITIES.CIT_NAME, STATES.STA_CODE


FROM STATES INNER JOIN
ADDRESSES INNER JOIN
CITIES ON ADDRESSES.ADD_CIT_ID = CITIES.CIT_ID INNER JOIN
EVENTS ON ADDRESSES.ADD_ID = EVENTS.EVE_ADD_ID ON STATES.STA_ID = ADDRESSES.ADD_STA_ID


WHERE (EVENTS.EVE_DATE = GETDATE())


Should return 2 value but does not.


If I use "WHERE (EVENTS.EVE_DATE >= GETDATE())" it does return the 2 values.


Any ideas as to why the first is not working


Thank You,
Rookie

Post Edited (Rookie) : 7/13/2006 11:01:38 PM GMT

 

I bet your comparing a dateTime the time is whats making you results not work. So you just want to check the date and not the time. This should work for you...

WHERE datediff(dd,EVENTS.EVE_DATE,getDate()) = 0

the differance in days is 0 if its the same date....

There are a ton of ways you can do this qury this one is dirty but it works (I think)

Here some more info


Some other functions that can be used for various purposes are DATEADD, DATEDIFF, DATENAME, DATEPART, DAY, GETDATE, MONTH, and YEAR. Here's some further detail on these functions as well as a code sample showing their use:

Dateadd: Returns a new datetime value based on adding an interval to the specified date.

Syntax: DATEADD ( datepart, number, date )

Datediff: Returns the number of date and time boundaries crossed between two specified dates.

Syntax: DATEDIFF ( datepart, startdate, enddate )

Datename: Returns a character string representing the specified datepart of the specified date.

Syntax: DATENAME ( datepart, date )

Datepart: Returns an integer representing the specified datepart of the specified date.

Syntax: DATEPART ( datepart, date )

Day: Returns an integer representing the day datepart of the specified date.

Syntax: DAY ( date )

Getdate: Returns the current system date and time in the Microsoft® SQL Server? standard internal format for datetime values.

Syntax: GETDATE ( )

Month: Returns an integer that represents the month part of a specified date.

Syntax: MONTH ( date )

Year: Returns an integer that represents the year part of a specified date.

Syntax: YEAR ( date )

declare @datevar datetime
select @datevar = getdate()

/*Example for getdate() : getting current datetime*/
select getdate() [Current Datetime]

/*Example for dateadd : getting date 7 days from current datetime*/
select dateadd(dd, 7, @datevar) [Date 7 days from now]

/*Example for datediff : getting no of days passed since 01-01-2004*/
select datediff(dd,'20040101',@datevar) [No of days since 01-01-2004]

/*Example for datename : getting month name*/
select datename(mm, @datevar) [Month Name]

/*Example for datepart : getting week from date*/
select datepart(wk, @datevar ) [Week No]

/*Example for day : getting day part of date*/
select day (@datevar) [Day]

/*Example for month : getting month part of date*/
select month(@datevar) [Month]

/*Example for year : getting year part of date*/
select year(@datevar) [Year]

Now I will provide you with some code samples which you can use for various tasks. I will try to include as many examples I can think of, but this list is not exhaustive:

1. To find the first day of a month:

select dateadd(dd,-(day(DateColumn)-1),DateColumn)

2. To find last day of a month:

select dateadd(dd,-(day(dateadd(mm,1,DateColumn))),dateadd(mm,1,DateColumn))

kevinasp.com

 

Thank you Kevin, works like a charm.

Rookie