What does the below query mean?

CONVERT(date,GETDATE()-1) between d.baslangictarihi and d.bitistarihi

I know how to use between by first selecting the column name and then giving the value. but here it is given the value first and then called 2 columns.

CodePudding user response：

Well you could "explode" the BETWEEN expression, such that this:

CONVERT(date, GETDATE() - 1) BETWEEN d.baslangictarihi AND d.bitistarihi

becomes this:

CONVERT(date, GETDATE() - 1) >= d.baslangictarihi AND
CONVERT(date, GETDATE() - 1) <= d.bitistarihi

This is just checking if yesterday's date happens to be in between baslangictarihi and bitistarihi, both ends included.

CodePudding user response：

As others explained, this somewhat quirky condition checks whether yesterday's date CONVERT(date,GETDATE()-1) is between two date fields baslangictarihi and bitistarihi. More importantly, it does so without preventing the server from using any indexes that cover baslangictarihi and bitistarihi.

Indexes are created based on the actual stored values, so applying a function to a field prevents the server from using indexes to speed up searching.

So while baslangictarihi <= GETDATE() can use any indexes that cover that field to limit processing only to the matching table rows, dateadd(d,1,baslangictarihi) <= GETDATE() would have to process all table rows, calculate the result and compare it against GETDATE(). In a large table, this can be very slow.

SQL Server Date quirks

The first part has some quirks too, due to SQL Server's somewhat quirky date support. To be fair all databases and programming languages have quirks when it comes to dates.

GETDATE() returns the legacy datetime type which often behaves as a float, with the integral part an offset from 1899-12-30 (no typo, it really is December 30), and the fractional representing time. That's how dates were stored in Visual Basic in the 1990s and Excel (OADate format)

Since GETDATE() acts as a float, it's possible to subtract days by subtracting integers, so GETDATE()-1 is equivalent to DATEADD('d',GETDATE(),-1).

SQL Server has no interval type, so in some quirky code you'll even see people storing intervals as datetime, eg 0000-00-01 01:00 and adding two dates directly. None of the "new" date types introduced in ... 2005 (datetime2,datetimeoffset,date) allows this.

Finally, convert(date,....) converts datetime to date, a type that only contains a date. Effectively, this truncates the time part returned by GETDATE()

The same expression without quirks would be CONVERT(date,DATEADD(d,-1,GETDATE()))

CodePudding user response：

Just understand that everything in a predicate like this is an expression. CONVERT(date,GETDATE()-1) means yesterday without the time component. Those two columns are whatever values are on the row that's being considered at the time. You know what it means if there's a column on the left, but this is no different. It gets evaluated just the same.

CodePudding user response：

lets consider a sample data to understand this better.

membership_dim

Your Query

CONVERT(date,GETDATE()-1) between d.baslangictarihi and d.bitistarihi

rewriting to match the above sample data

select * from membership_dim where CONVERT(date,GETDATE()-1) between membership_start_date and membership_end_date

Result set

Explanation:

lets breakdown the code

CONVERT(date,GETDATE()-1)

-> getdate()-1 = returns yesterday's date in datetime format (01-23-2022 xx:xx:xx.xxx)

-> convert = converts the datatime to date (01-23-2022)

-> between = a comparison operator

01-23-2022 between 01-05-2020 and 31-12-2022 - returns true

01-23-2022 between 21-01-2018 and 31-12-2021 - returns false

01-23-2022 between 21-01-2018 and 31-12-2022 - returns true