none
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
    Sunday, October 02, 2011 3:08 PM

All replies

  • 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
    By: TwitterButtons.com
    Sunday, October 02, 2011 3:50 PM
  • 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
    Sunday, October 02, 2011 4:51 PM
  • 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/
    Sunday, October 02, 2011 5:04 PM
  • 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
    Sunday, October 02, 2011 5:29 PM
  • 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/
    Sunday, October 02, 2011 5:48 PM
  • 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
    Monday, October 03, 2011 2:38 PM