curdate() compare date and todays dates in a query

Question

I have a field

eventdate it is a text field with a date like 12/25/2011

 

I have a query/function that is works well with an an innerjoin and some spatial geographic data etc.

but now i want to add the ability to limit the selections i.e.

Where eventdate => curdate()

this doesn't seem to be allowed in SQL SERVER R2 on my godaddy implementation

do I need to add todays date as a paramater and pass it from to the query?
The error message seems to not like => and curdate() as well saying it's not
recognized as a built in function.

I also suspect that to be absolutely correct in retrieving the data some conversion
from a string to a date-value of some sort  might be necessary.

Any ideas on how to handle this? I am looking around but as of yet I have not
found a resource with a clear plan for my situation.

Thanks - sorry if this post is not exactly in the right place...

 

---

- 10spotdomain for all your domain and domain related needs

Answer 1

How is this related to SSIS?

You said "eventdate it is a text field with a date like 12/25/2011". GETDATE() is the T-SQL function to get the current date AND time.

You would need to cast this text to the proper date format e.g. an ISO date as 2011-12-25.

A Derived Column Transform can like

(DT_DBDATE) (EventDate)

---

Arthur My Blog

Answer 2

sorry like I said I don't know where to put this type of question... i looked through the loads of topical boards but didn't find one that was about sql

 

here is my query

SELECT
venues.ID,    venues.Events,    venues.Walk,    venues.Bike, venues.Fish,    venues.Camp,    venues.SiteName,   
venues.StreetAddress,    venues.City,    venues.State,    venues.Zip,    venues.Phone,    venues.SiteURL,    venues.Latitude,    venues.Longitude,   
venues.Description,    venues.ImageURL,    venues.Category,    venues.WaterSports,    venues.SnowSports,    venues.EventsOnly,   
events.ID,    events.SiteName,    events.EventID,    events.EventName,    events.EventStartDate,    events.EventEndDate,    events.Age,    events.Price,   
events.StartTime,    events.StopTime,    events.EventDescription,    events.EventImageURL, convert( varchar(12), events.EventStartDate, 105 ) as orderdate,
round( Geo.STDistance(GEOGRAPHY::Point(@lat,    @long,  4326))/1609.344 ,2,0) as milesaway
FROM venues
INNER JOIN events
ON venues.id=events.ID
where venues.Geo.STDistance(GEOGRAPHY::Point(@lat,    @long,  4326)) < (@range * 1609.344) and EventStartDate > DATEADD( d, -1, GETDATE() )
Order by orderdate, StartTime DESC
 
note that about 7th line from bottom of query

convert( varchar(12), events.EventStartDate, 105 ) as orderdate, <== I try to convert my text date from 12/1/2011 to a YYMMDD format and create a new field for it called ORDERDATE and then to use that new field to order the information

but the conversion doesn't seem to work ORDERDATE is exactly the same format as the original field no mater I tried style 112 and now 105 no diff...

any suggestions would be great.

 

Note that in the WHERE section EventStartDate > DATEADD( d, -1, GETDATE() ) <== this seems to work fine to select only todays date and later.

 

 

 

---

- 10spotdomain for all your domain and domain related needs

Answer 3

Note that in the WHERE section EventStartDate > DATEADD( d, -1, GETDATE() ) <== this seems to work fine to select only todays date and later.- 10spotdomain for all your domain and domain related needs

 

The right way to do this is to change the data type of EventStartDate to date.  But as long as your session date format setting is MDY, the separated date string value will be implicitly converted to datetime for the comparison the DATEADD function result.    Note that this query works because EventStartDate contains only a date (without time component). Otherwise, rows less that 24 hours old would be selected.

You could simplify as:

 

EventStartDate >= CAST(GETDATE() AS date)

 

 

 

 

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 4

Dan thanks for the help

I will try your suggestion

In the mean time I have discovered that CONVERT just will not work in my situation
all seem like a bug?

rather DATEPART works with my eventdate field

so here is my striped down sample of my current solution.

 

select *, convert( varchar(12), events.EventStartDate, 1 ) as orderdate,
DATEPART(mm, EventStartDate) as esm,  DATEPART(dd, EventStartDate) as esd,
DATEPART(yy, EventStartDate) as esy
from dbo.events where EventStartDate > DATEADD( d, -1, GETDATE() )
Order by esy, esm, esd, StartTime DESC

so I created three fields Year Month DATE and order by them... stupid but works

I have worked with lots of data bases and dates are always a pain. SQL Server is no better
the CONVERT function is worse.

 

---

- 10spotdomain for all your domain and domain related needs

Answer 5

I think you can refactor your query as:

DECLARE @EventStartDate date = '20111201';
SELECT *
	, CAST(events.EventStartDate AS date) as orderdate
FROM dbo.events 
WHERE CAST(EventStartDate AS date) >= @EventStartDate
ORDER BY 
	orderdate
	, StartTime DESC;

Be aware that this will require a scan because of the function applied to the EventStartDate column.  The underlying issue is that EventStartDate is not properly typed.  Is it possible to change the date type?  In that case, you could use the query below, which can use an index on EventStartDate efficiently for botht he WHERE and ORDER BY:

 

DECLARE @EventStartDate date = '20111201';
SELECT *
FROM dbo.events 
WHERE EventStartDate >= @EventStartDate
ORDER BY 
	EventStartDate
	, StartTime DESC;

 

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 6

Dan

Thanks for your thoughtful analysis of the query problems I am having.

It is possible to create a new field from the date and time

when the data is imported and or modify / translate the date text into a date typed field

when importing the data I did not notice how this could be accomplished - due to the nature of

this job I have to import and recreate the data tables every week

by SQL Server standards our data size is very small - the whole purpose

of moving to SQL server is performance in the Internet environment plus adding 

additional tables that can be joined, again doing this with Access was way to slow.

 

So during the import procedures some additional steps can be added to create the proper date and time fields this is where it it is about SQL BIDS....

some how I skipped over date and time field types....

 

---

- 10spotdomain for all your domain and domain related needs