Last 2 years with Workbench

Grunny
Grunny Member
in Workbench

I am trying to pull a table from a database that is really big.  So, I would like to only have the last 2 years worth.  I have a column that has the order start date, but the format is like this 2005-07-26 14:25:00 -05:00

Was wondering if someone could point me in the right direction with this.

Comments

  • dthierjung
    dthierjung Contributor

    How familiar are you with SQL?

     

    You should be able to apply a simple where clause like the following:

     

    SELECT 
    		*
    FROM 
    		dbo.TableName
    WHERE
    		`order_start_date` > DATEADD(yy,-2,GETDATE())

     If you have to, you can try casting or converting the `order_start_date` column to a datetime that SQL understands.

  • Grunny
    Grunny Member

    When i put that in i get the following during preview:

    [02.21.19 08:29:23 AM] Query String: SELECT * FROM OpOrder
    WHERE 'OrderStartDateOffset' > DATEADD(yy,-2,GETDATE())
    [02.21.19 08:29:25 AM] One or more errors occurred.
    ERROR [22007] [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.

     

  • guitarhero23
    guitarhero23 Contributor

    What datatype is your date stored as?


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • dthierjung
    dthierjung Contributor

    Oh, my apologies. You need to remove the apostrophes from around your column name. I had originally incldued them in my example just as a way of showing the column name should go here.

     

    So the query would be (with removed apostrophes from around OrderStartDateOffset):

    SELECT

                  *

    FROM

                  OpOrder
    WHERE

                  OrderStartDateOffset > DATEADD(yy,-2,GETDATE())

     

     

    And with what guitarhero23 has asked, if your datatype for that column is a string, you'll need to cast it to a DateTime, possibly using this:

    CAST(OrderStartDateOffset as datetime)

     

    But let me know if the first suggestion doesn't help fix the error.

This discussion has been closed.

Categories