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.
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()))
CONVERT(date,GETDATE()-1) between d.baslangictarihi and d.bitistarihi) qualify for your logic re: "applying a function to a field"? They're not applying a function to a field but rather the result of getdate() and comparing that to the data stored in the table. I'm probably missing something.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.
lets consider a sample data to understand this better.
membership_dim
| id | name | dob | membership_start_date | membership_end_date |
|---|---|---|---|---|
| 1 | abc | 19-05-1976 | 01-05-2020 | 31-12-2022 |
| 2 | efg | 10-01-1990 | 21-01-2018 | 31-12-2021 |
| 3 | xyz | 31-01-1990 | 12-01-2022 | 31-12-2022 |
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
| id | name | dob | membership_start_date | membership_end_date |
|---|---|---|---|---|
| 1 | abc | 19-05-1976 | 01-05-2020 | 31-12-2022 |
| 3 | xyz | 31-01-1990 | 12-01-2022 | 31-12-2022 |
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
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.
