0

I am sure this question has been asked in different ways but I am at my wits end for some reason. I am performing a pretty straight forward SELECT INTO FROM statement which includes A WHERE on the date to be added but I cannot get the temporary table to create - I continually get the above error. Tried all kinds of CONVERT/CAST etc but no luck. The date is stored as this format in the DB: 2017-01-02 00:00:00.000 but I have had no problem querying in other areas of the DB using '01/01/2017' for example.

SQL Code: 

SELECT AO.SALES_ORDER_NUMBER AS 'SALES_ORDER_NUMBER', 
       AO.SALES_ORDER_ITEM_NUMBER AS 'SALES_ORDER_ITEM_NUMBER',
       II.BILLING_DOCUMENT_NUMBER AS 'BILLING_DOCUMENT_NUMBER', 
       CASE 
            WHEN IH.SOLD_TO = '0000006070'
            THEN '66400'
            WHEN IH.SOLD_TO = '0000006075'
            THEN '66315'
            ELSE AO.BILL_TO
       END AS 'BILL_TO',
       AO.MATERIAL AS 'MATERIAL', 
       AO.SIZE_INDEX AS 'SIZE_INDEX',
       AO.REQUESTED_DELIVERY_DATE AS 'REQUESTED_DELIVERY_DATE'


INTO ##Temp_Invoiced

FROM  VW_AR_ORDER_ORIGINAL AO 

JOIN  VW_INVOICES_ITEM II ON AO.SALES_ORDER_ITEM_NUMBER = II.SALES_ORDER_ITEM_NUMBER AND AO.SALES_ORDER_NUMBER = II.SALES_ORDER_NUMBER

JOIN VW_INVOICES_HEADER IH ON II.BILLING_DOCUMENT_NUMBER = IH.BILLING_DATE

WHERE AO.STOCK_CATEGORY LIKE '%1002%'

AND   AO.REQUESTED_DELIVERY_DATE >= '01/01/2017';
Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
user3496218
  • 185
  • 3
  • 5
  • 19
  • 1
    Perhaps it have something to do with this `II.BILLING_DOCUMENT_NUMBER = IH.BILLING_DATE`. – Gordon Linoff Apr 25 '17 at 02:08
  • where exactly are you encountering the error? what line? – Vian Ojeda Garcia Apr 25 '17 at 02:10
  • Try to use `2017/01/01` or `Datefromparts(2017,01,01)` – TriV Apr 25 '17 at 02:19
  • Hi and thank you - it is showing as Msg 241, Level 16, State 1, Line 8 which is actually where SELECT begins. There are comments above. – user3496218 Apr 25 '17 at 02:22
  • @GordonLinoff - you were exactly right sorry for the dummy question lol! I am not quite an expert at knowing where to look first but all of you gurus are a huge help and next time I will make sure to look at my joins! Best. – user3496218 Apr 25 '17 at 02:24
  • I got lazy and used the auto populate (and a new computer which is very sensitive). Thanks all! Hopefully one day soon I will be able to help others! – user3496218 Apr 25 '17 at 02:33
  • 1
    This is why a query should be well formated. I like separating each column into its own row for most of my queries. Guesswork gone, clarity, yes. – clifton_h Apr 25 '17 at 02:37

0 Answers0