Not able to fix syntactical error in sql query

Question

I am trying to insert data from a staging table into original table for a particular time period in Microsoft SQL Server Management Studio 18 using below query.

INSERT INTO dbo.trial ([turbineid], [createddate], [lastupdatedby],[lastupdateddate])
    SELECT 
        [turbineid], [createddate], [lastupdatedby], [lastupdateddate] 
    FROM 
        dbo.trial_January
    WHERE
        createddate BETWEEN (DATEPART(mm, createddate) = 01 
                             AND DATEPART(dd, createddate) = 01))
                        AND (DATEPART(mm, createddate) = 01 
                             AND DATEPART(dd, createddate) = 30)

When trying to execute this query, I get this error:

Incorrect syntax near '='.

In a table whose name contains the word "January", why do you need to use any month-based logic as a filter? — SMor
– SMor, Commented Oct 1, 2020 at 12:55

Tim · Accepted Answer · 2020-10-01 12:56:54Z

1

You are trying to compare a date with some integers. Your where clause should look more like this

WHERE createddate BETWEEN (DATEFROMPARTS(DATEPART(YY,createddate),1,1)) AND (DATEFROMPARTS(DATEPART(YY,createddate),1,30))

answered Oct 1, 2020 at 12:56

Tim

881 gold badge1 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Coskun Ozogul · Accepted Answer · 2020-10-01 12:56:24Z

This claus doesn't make sense

 where  createddate between 
 (DATEPART(mm , createddate) = 01 AND DATEPART(dd, createddate) = 01))
  AND (DATEPART(mm, createddate) = 01 AND DATEPART(dd, createddate) = 30)

Between state needs two date to check if the given date is between two dates.

for exemple :

SELECT 'true' WHERE GETDATE() BETWEEN GETDATE() -1 AND GETDATE() + 1

And the DATEPART(mm, CreatedDate) returns the month of your date.

SELECT 'true' where  DATEPART(mm , GETDATE()) = 10 //Checks if the month of the given date is 10.

According your query, you want to get records of january. So you can try this

 INSERT INTO dbo.trial ([turbineid], [createddate], [lastupdatedby],[lastupdateddate])
SELECT 
    [turbineid], [createddate], [lastupdatedby], [lastupdateddate] 
FROM 
    dbo.trial_January
WHERE
   DATEPART(MONTH, createddate) = 01 
   AND DATEPART(YEAR, createddate) = 2020 -- the year you want

Derviş Kayımbaşıoğlu · Accepted Answer · 2020-10-01 13:25:29Z

0

your where condition is not correct

 where DATEPART(mm , createddate) = 1

gives you results for first month. you dont need to have anything else for the day if your intention is to look for whole january

edited Oct 1, 2020 at 13:25

answered Oct 1, 2020 at 12:46

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

1 Comment

marc_s Over a year ago

DATEPART(mm) returns an INT - so your check should not include any unnecessary single quotes - just DATEPART(MONTH, createdDate) = 01

Collectives™ on Stack Overflow

Not able to fix syntactical error in sql query

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related