Unable to Get Results from Query

Transact-SQL
1

Hello Community,
I'm going to be straight with you and let you know that I have posted the following question to another SQL forum, but they aren't able to find a solution, so I am going ask you guys if you can help take a look at the following query and let me know why I keep on getting the following error:

1 Incorrect syntax near ','.

The query is a follows:

SELECT
  *
FROM Data.Stock
WHERE Stock.DateBought BETWEEN CAST(YEAR(DATEADD(m, -1, GETDATE())) AS CHAR(4)), CAST(MONTH(DATEADD(m, -1, GETDATE())) AS VARCHAR(2)) AND EOMONTH(DATEADD(m, -1, GETDATE()))

The table is a follows:

CREATE TABLE [szen-ds-sql-rdp-tstdbase4].Data.Stock (
StockCode NVARCHAR(50) NULL
,ModelID SMALLINT NULL
,Cost MONEY NULL
,RepairsCost MONEY NULL
,PartsCost MONEY NULL
,TransportInCost MONEY NULL
,IsRHD BIT NULL
,Color NVARCHAR(50) NULL
,BuyerComments NVARCHAR(4000) NULL
,DateBought DATE NULL
,TimeBought TIME NULL
)
GO

and some data:

|StockCode|ModelID|Cost|RepairsCost|PartsCost|TransportInCost|IsRHD|Color|BuyerComments|DateBought|TimeBought|
|---|---|---|---|---|---|---|---|---|---|---|
|CF66838B-8A21-4084-9771-07A82CDBEBD8|86|7160.00|500.00|228.00|150.00|True|Dark Purple|(null)|29/10/2018|12:55:00|
|7461FB42-ECE2-4C8C-BDBB-EF26AF3069F9|87|7600.00|500.00|750.00|150.00|True|Green|(null)|29/10/2018|12:55:00|
|F287EE1D-48C6-4E75-9595-E0AE235FBEA8|86|7600.00|500.00|330.00|150.00|True|Green|(null)|01/11/2018|12:55:00|

I have also submitted the query to the following group, but without any luck

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f834764b-1e0d-4111-80cc-aeb6cd6346b6/unable-to-get-tsql-to-produce-results?forum=transactsql

Any help will be grealy appreciated.

Cheers

2

your syntax is wrong for the WHERE clause

Assuming you wanted from first of last month till end of last month

WHERE      Stock.DateBought 
BETWEEN    DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
AND        EOMONTH(DATEADD(m, -1, GETDATE()))
3

Something smells funny here

CAST(YEAR(DATEADD(m, -1, GETDATE())) AS CHAR(4)), CAST(MONTH(DATEADD(m, -1, GETDATE())) AS VARCHAR(2)) ```
4

Hi khtan,

Thanks for reaching out, however I would like someone to fix the error in my code to make it work.

5

Yosiasz

What does that mean? :slight_smile:

6

See @khtan answer

7

@khtan, worked like a dream

8

hi

i tried to get this to work
hope this helps :slight_smile: :slight_smile: ... i love feedback .. thanks

drop create sample data .. 
drop table #Stock
go 

CREATE TABLE #Stock (
StockCode NVARCHAR(50) NULL
,ModelID SMALLINT NULL
,Cost MONEY NULL
,RepairsCost MONEY NULL
,PartsCost MONEY NULL
,TransportInCost MONEY NULL
,IsRHD BIT NULL
,Color NVARCHAR(50) NULL
,BuyerComments NVARCHAR(4000) NULL
,DateBought DATE NULL
,TimeBought TIME NULL
)
GO


insert into #stock select 'CF66838B-8A21-4084-9771-07A82CDBEBD8',86,7160.00,500.00,228.00,150.00,1,'Dark Purple',null,'2019-07-01','12:55:00'
insert into #stock select '7461FB42-ECE2-4C8C-BDBB-EF26AF3069F9',87,7600.00,500.00,750.00,150.00,1,'Green',null,'2019-07-30','12:55:00'
insert into #stock select 'F287EE1D-48C6-4E75-9595-E0AE235FBEA8',86,7600.00,500.00,330.00,150.00,1,'Green',null,'2019-07-15','12:55:00'
go 

select * from #Stock
go
your SQL modified .... 
SELECT * 
FROM   #stock 
WHERE  datebought BETWEEN Dateadd(month, -1, Getdate()) AND Eomonth( 
                          Dateadd(m, -1, Getdate()))

image
image.png1879×564 22.8 KB

9

Hi Harishgg1

Thanks for reaching out, this is same as @khtan, which worked fine.

Cheers

10

i tried to do my own thinking ... did not see Khans logic

As long as it helps you !!!! thats all I care :slight_smile: