CODEWITHSUNDEEP
sql-serverdatetime
Ionut P.
Ionut P.

Reputation: 63

Where condition based on month and year

i have the following code:

select COUNT(STL.ITEID)

from STORETRADELINES STL

left join FINTRADE FT ON STL.FTRID=FT.ID

where RIGHT(CONVERT(datetime, FT.FTRDATE, 3), 5) < RIGHT(CONVERT(datetime, GETDATE(), 3), 5) 
and FT.FTRDATE > CONVERT(datetime,'01.01.2017',103)

I want to select all the documents that fall between 01.01.2017 and 30.04.2017 (always, the last day of the previous month).

Seem's that this way is not good, because is returning all the docs from 01.01 until today.

Where am i wrong ? Btw, i use sql server 2008.

Thank you

Upvotes: 1

Views: 1240

Answers (2)

TriV
TriV

Reputation: 5148

dateadd(mm,datediff(mm, 0 , current_timestamp),0) will return the first day of current month.
Your query could be

SELECT COUNT(STL.ITEID)
FROM STORETRADELINES STL
   left join FINTRADE FT ON STL.FTRID=FT.ID
where 
FT.FTRDATE >=  '2017-01-01' AND FT.FTRDATE < dateadd(mm,datediff(mm, 0 , current_timestamp),0)

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

The problem is, you're doing string comparisons. Use date comparisons instead:

select COUNT(STL.ITEID)

from STORETRADELINES STL

left join FINTRADE FT ON STL.FTRID=FT.ID

where FT.FTRDATE < DATEADD(month,DATEDIFF(month,0,GETDATE()),0)
and FT.FTRDATE >= '20170101'

Here, the DATEADD/DATEDIFF pair of calls are rounding the current date down to the start of the current month.

You can use a similar DATEADD/DATEDIFF pair for your start-of-period condition, using year instead of month if you want this query to be generic for any current year

The reason you're "stringly-typed" code doesn't work is that your CONVERT calls are converting datetime values to datetime. The style parameter is ignored here since datetimes don't have a format. You then get an uncontrolled implicit conversion of the datetimes into varchar so that RIGHT can work, and it's that conversion that you should have tried to control.

As it is, this:

select RIGHT(CONVERT(datetime, GETDATE(), 3), 5)

Generates:

:50AM

Which you can hopefully see is nothing like what you wanted.

Upvotes: 2

Related Questions