Workbench

Workbench

Last 2 years with 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.

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In

Comments

  • How familiar are you with SQL?

     

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

     

    1. 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.

  • 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.

     

  • 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.**
  • 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.