Menu
I have a table of accounts that has columns for start date and end date. I want the user to supply a start date and end date and all data must be returned where the account was active at any time between the user specified start date and end date.
The catch is that the end date may be null if the account is still currently active.
The account must be selected if it was open at any time between the user specified range which means that even accounts that were opened or closed between the range must be included.
Here is a picture of what I want.
The yellow part is the date range entered by the user. The green parts is what I want selected. The light red part is what I don't want selected. The arrows on the end of G, H and I mean that those accounts don't have an end date. All other ones that don't have an arrow have an end date.
677
As stated above, the format of date and time in our table shall be yyyy:mm: dd hh:mm: ss which is implied by DATETIME2. The time is in a 24-hour format. Syntax: SELECT * FROM TABLE_NAME WHERE DATE_TIME_COLUMN BETWEEN 'STARTING_DATE_TIME' AND 'ENDING_DATE_TIME';
SELECT * FROM YourTable. WHERE [dateColumn] >DATEADD(day,1,'4/25/2022') AND [dateColumn] <= DATEADD(day,1,'4/26/2022')
You can use the dateadd function of SQL. This will return ID 1,2,3,4. We are doing a double Dateadd ; the first is to add a day to the current endDate , it will be 2012-03-28 00:00:00, then you subtract one second to make the end date 2012-03- 27 23:59:59.
The SQL BETWEEN OperatorThe values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included.
So, this amounts to checking that the account start date is prior to the user supplied end date and the account end date is after the user supplied start date (or is null, in which case we don't even need to check it):
where
account.start_date < @user_end_date and
(account.end_date >= @user_start_date or account.end_date is null)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
