SQL Server: select data from between a range of dates if dates exist, if not return all data
up vote
0
down vote
favorite
@dateFrom and @dateTo are the variables coming from outside users. They may can put a date in, or choose not to have any range and want to see all data,
SELECT *
FROM tbl
WHERE --
I want to implement following conditions
If
@dateFromand@dateTOboth variable have values, then select data in rangeIf
@dateFromhas value and@dateTodon't, then select data range starting from@dateFromwith no ending range.If
@dateTohas value and@dateFromdon't, then select data range ending at@dateTowith no starting range.If non of the variable exist, return all the data.
sql
sql-server tsql edited Nov 8 at 5:52
marc_s
567k12810961247
asked Nov 8 at 5:12
S. Saleem
1
add a comment |
up vote
0
down vote
favorite
@dateFrom and @dateTo are the variables coming from outside users. They may can put a date in, or choose not to have any range and want to see all data,
SELECT *
FROM tbl
WHERE --
I want to implement following conditions
If
@dateFromand@dateTOboth variable have values, then select data in rangeIf
@dateFromhas value and@dateTodon't, then select data range starting from@dateFromwith no ending range.If
@dateTohas value and@dateFromdon't, then select data range ending at@dateTowith no starting range.If non of the variable exist, return all the data.
sql
sql-server tsql edited Nov 8 at 5:52
marc_s
567k12810961247
asked Nov 8 at 5:12
S. Saleem
1
1
What about the table itself? Does it also have aDateFromandDateTocolumns, or just one date column?
– Zohar Peled
Nov 8 at 6:51
1
If there are two columns then there is an additional question: what if the user choose (Dec 21, Dec 25) and you have one row for (Dec 20, Dec 26).
– Salman A
Nov 8 at 7:05
And what if the date column(s) are nullable? Suppose you have a single nullable date column, and a row where the date is null. Should you return it in all of the search options, or only if both parameters are null, or perhaps if at least one parameter is null? That's also a question to ask for a date range - if The one of the columns is null it's quite easy to understand the record has only one limit, but what if they are both nulls? (usually, this would be avoided declaring the ToDate column as non-nullable or a check constraint allowing only one of the columns to be null for each row)
– Zohar Peled
Nov 8 at 7:16
My point is, that this question might seem simple (and it might very well be simple), but there are too many details missing to give a good answer that is not based, at least partially, on guesses, or that is not very long so that it would cover all (or at least most) of these potential pitfalls.
– Zohar Peled
Nov 8 at 7:18
@S. Saleem : You have some answers below to pick! And please tick mark any of your best answer!
– im_one
Nov 13 at 5:02
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
@dateFrom and @dateTo are the variables coming from outside users. They may can put a date in, or choose not to have any range and want to see all data,
SELECT *
FROM tbl
WHERE --
I want to implement following conditions
If
@dateFromand@dateTOboth variable have values, then select data in rangeIf
@dateFromhas value and@dateTodon't, then select data range starting from@dateFromwith no ending range.If
@dateTohas value and@dateFromdon't, then select data range ending at@dateTowith no starting range.If non of the variable exist, return all the data.
sql
sql-server tsql edited Nov 8 at 5:52
marc_s
567k12810961247
asked Nov 8 at 5:12
S. Saleem
1
@dateFrom and @dateTo are the variables coming from outside users. They may can put a date in, or choose not to have any range and want to see all data,
SELECT *
FROM tbl
WHERE --
I want to implement following conditions
If
@dateFromand@dateTOboth variable have values, then select data in rangeIf
@dateFromhas value and@dateTodon't, then select data range starting from@dateFromwith no ending range.If
@dateTohas value and@dateFromdon't, then select data range ending at@dateTowith no starting range.If non of the variable exist, return all the data.
sql
sql-server tsql sql
sql-server tsql edited Nov 8 at 5:52
marc_s
567k12810961247
asked Nov 8 at 5:12
S. Saleem
1
edited Nov 8 at 5:52
marc_s
567k12810961247
asked Nov 8 at 5:12
S. Saleem
1
edited Nov 8 at 5:52
marc_s
567k12810961247
edited Nov 8 at 5:52
marc_s
567k12810961247
edited Nov 8 at 5:52
marc_s
567k12810961247
567k12810961247
asked Nov 8 at 5:12
S. Saleem
1
asked Nov 8 at 5:12
S. Saleem
1
asked Nov 8 at 5:12
S. Saleem
1
1
1
What about the table itself? Does it also have aDateFromandDateTocolumns, or just one date column?
– Zohar Peled
Nov 8 at 6:51
1
If there are two columns then there is an additional question: what if the user choose (Dec 21, Dec 25) and you have one row for (Dec 20, Dec 26).
– Salman A
Nov 8 at 7:05
And what if the date column(s) are nullable? Suppose you have a single nullable date column, and a row where the date is null. Should you return it in all of the search options, or only if both parameters are null, or perhaps if at least one parameter is null? That's also a question to ask for a date range - if The one of the columns is null it's quite easy to understand the record has only one limit, but what if they are both nulls? (usually, this would be avoided declaring the ToDate column as non-nullable or a check constraint allowing only one of the columns to be null for each row)
– Zohar Peled
Nov 8 at 7:16
My point is, that this question might seem simple (and it might very well be simple), but there are too many details missing to give a good answer that is not based, at least partially, on guesses, or that is not very long so that it would cover all (or at least most) of these potential pitfalls.
– Zohar Peled
Nov 8 at 7:18
@S. Saleem : You have some answers below to pick! And please tick mark any of your best answer!
– im_one
Nov 13 at 5:02
add a comment |
1
What about the table itself? Does it also have aDateFromandDateTocolumns, or just one date column?
– Zohar Peled
Nov 8 at 6:51
1
If there are two columns then there is an additional question: what if the user choose (Dec 21, Dec 25) and you have one row for (Dec 20, Dec 26).
– Salman A
Nov 8 at 7:05
And what if the date column(s) are nullable? Suppose you have a single nullable date column, and a row where the date is null. Should you return it in all of the search options, or only if both parameters are null, or perhaps if at least one parameter is null? That's also a question to ask for a date range - if The one of the columns is null it's quite easy to understand the record has only one limit, but what if they are both nulls? (usually, this would be avoided declaring the ToDate column as non-nullable or a check constraint allowing only one of the columns to be null for each row)
– Zohar Peled
Nov 8 at 7:16
My point is, that this question might seem simple (and it might very well be simple), but there are too many details missing to give a good answer that is not based, at least partially, on guesses, or that is not very long so that it would cover all (or at least most) of these potential pitfalls.
– Zohar Peled
Nov 8 at 7:18
@S. Saleem : You have some answers below to pick! And please tick mark any of your best answer!
– im_one
Nov 13 at 5:02
1
1
What about the table itself? Does it also have a
DateFrom and DateTo columns, or just one date column?– Zohar Peled
Nov 8 at 6:51
What about the table itself? Does it also have a
DateFrom and DateTo columns, or just one date column?– Zohar Peled
Nov 8 at 6:51
1
1
If there are two columns then there is an additional question: what if the user choose (Dec 21, Dec 25) and you have one row for (Dec 20, Dec 26).
– Salman A
Nov 8 at 7:05
If there are two columns then there is an additional question: what if the user choose (Dec 21, Dec 25) and you have one row for (Dec 20, Dec 26).
– Salman A
Nov 8 at 7:05
And what if the date column(s) are nullable? Suppose you have a single nullable date column, and a row where the date is null. Should you return it in all of the search options, or only if both parameters are null, or perhaps if at least one parameter is null? That's also a question to ask for a date range - if The one of the columns is null it's quite easy to understand the record has only one limit, but what if they are both nulls? (usually, this would be avoided declaring the ToDate column as non-nullable or a check constraint allowing only one of the columns to be null for each row)
– Zohar Peled
Nov 8 at 7:16
And what if the date column(s) are nullable? Suppose you have a single nullable date column, and a row where the date is null. Should you return it in all of the search options, or only if both parameters are null, or perhaps if at least one parameter is null? That's also a question to ask for a date range - if The one of the columns is null it's quite easy to understand the record has only one limit, but what if they are both nulls? (usually, this would be avoided declaring the ToDate column as non-nullable or a check constraint allowing only one of the columns to be null for each row)
– Zohar Peled
Nov 8 at 7:16
My point is, that this question might seem simple (and it might very well be simple), but there are too many details missing to give a good answer that is not based, at least partially, on guesses, or that is not very long so that it would cover all (or at least most) of these potential pitfalls.
– Zohar Peled
Nov 8 at 7:18
My point is, that this question might seem simple (and it might very well be simple), but there are too many details missing to give a good answer that is not based, at least partially, on guesses, or that is not very long so that it would cover all (or at least most) of these potential pitfalls.
– Zohar Peled
Nov 8 at 7:18
@S. Saleem : You have some answers below to pick! And please tick mark any of your best answer!
– im_one
Nov 13 at 5:02
@S. Saleem : You have some answers below to pick! And please tick mark any of your best answer!
– im_one
Nov 13 at 5:02
add a comment |
3 Answers
3
active
oldest
votes
up vote
2
down vote
You can try OR with AND and your logic.
SELECT * FROM tbl
WHERE
(@dateFrom IS NULL OR DateFrom > @dateFrom)
AND
(@dateTo IS NULL OR DateTo < @dateTo)
answered Nov 8 at 5:23
D-Shih
24.4k61431
This won't work if both the variables are null. As per the question if both variables are null we need to return all the data
– Sanal Sunny
Nov 8 at 5:37
1
If@dateFromand@dateToareNULLit can return all data from tbl. because contain logic in()isOR
– D-Shih
Nov 8 at 5:39
1
didn't notice that
– Sanal Sunny
Nov 8 at 5:49
1
You have a typo in the last row, and your overlap check is wrong. You should checkfrom < @to and @from < toto check for overlapping periods.
– Zohar Peled
Nov 8 at 7:35
@ZoharPeled Thank for point out :)
– D-Shih
Nov 8 at 7:38
|
show 2 more comments
up vote
2
down vote
You can try BETWEEN Operator with ISNULL
SELECT * FROM Table1
WHERE ISNULL(OrderDate,'') BETWEEN COALESCE(@DateFrom,OrderDate,'') AND COALESCE(@DateTo,OrderDate,'')
Edited: Zohar's approach for NULL value in Table's Column.
Now it's working perfect!
answered Nov 8 at 5:37
im_one
323114
Assuming the OrderDate column is nullable, this will not return any of the rows where it's null.
– Zohar Peled
Nov 8 at 7:20
@ZoharPeled: I didn't consider that case!!! But Yes, I have just edited my answer by taking reference of Sunny's COALESCE. It works perfectly now. Thank you for Idea Zohar!
– im_one
Nov 8 at 8:43
You might also want to read the rest of the comments on his answer....
– Zohar Peled
Nov 8 at 8:45
@ZoharPeled: Yes, I did!
– im_one
Nov 8 at 8:49
add a comment |
up vote
1
down vote
You can use ISNULL for this purpose
SELECT * FROM tbl
WHERE ISNULL(DateFrom,'')=COALESCE(@dateFrom,DateFrom,'')
AND ISNULL(DateTo,'')=COALESCE(@dateTo,DateTo,'')
answered Nov 8 at 5:15
Sanal Sunny
6548
Assuming the DateFrom or DateTo columns are nullable, this will not return any of the rows where one of them is null.
– Zohar Peled
Nov 8 at 7:20
@ZoharPeled I have updated the solution to get the values including the null ones. Can you please check whether is it the right way to do.
– Sanal Sunny
Nov 8 at 8:02
Yes, that will work better, but D-Shih's answer is still better - using functions on columns prevents the database engine from using any indexes that might help the query execution, making them unseargable. Also, you are assuming an exact date range match (which might be correct, we don't know because the OP didn't tell us).
– Zohar Peled
Nov 8 at 8:07
add a comment |
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
You can try OR with AND and your logic.
SELECT * FROM tbl
WHERE
(@dateFrom IS NULL OR DateFrom > @dateFrom)
AND
(@dateTo IS NULL OR DateTo < @dateTo)
answered Nov 8 at 5:23
D-Shih
24.4k61431
This won't work if both the variables are null. As per the question if both variables are null we need to return all the data
– Sanal Sunny
Nov 8 at 5:37
1
If@dateFromand@dateToareNULLit can return all data from tbl. because contain logic in()isOR
– D-Shih
Nov 8 at 5:39
1
didn't notice that
– Sanal Sunny
Nov 8 at 5:49
1
You have a typo in the last row, and your overlap check is wrong. You should checkfrom < @to and @from < toto check for overlapping periods.
– Zohar Peled
Nov 8 at 7:35
@ZoharPeled Thank for point out :)
– D-Shih
Nov 8 at 7:38
|
show 2 more comments
up vote
2
down vote
You can try OR with AND and your logic.
SELECT * FROM tbl
WHERE
(@dateFrom IS NULL OR DateFrom > @dateFrom)
AND
(@dateTo IS NULL OR DateTo < @dateTo)
answered Nov 8 at 5:23
D-Shih
24.4k61431
This won't work if both the variables are null. As per the question if both variables are null we need to return all the data
– Sanal Sunny
Nov 8 at 5:37
1
If@dateFromand@dateToareNULLit can return all data from tbl. because contain logic in()isOR
– D-Shih
Nov 8 at 5:39
1
didn't notice that
– Sanal Sunny
Nov 8 at 5:49
1
You have a typo in the last row, and your overlap check is wrong. You should checkfrom < @to and @from < toto check for overlapping periods.
– Zohar Peled
Nov 8 at 7:35
@ZoharPeled Thank for point out :)
– D-Shih
Nov 8 at 7:38
|
show 2 more comments
up vote
2
down vote
up vote
2
down vote
You can try OR with AND and your logic.
SELECT * FROM tbl
WHERE
(@dateFrom IS NULL OR DateFrom > @dateFrom)
AND
(@dateTo IS NULL OR DateTo < @dateTo)
answered Nov 8 at 5:23
D-Shih
24.4k61431
You can try OR with AND and your logic.
SELECT * FROM tbl
WHERE
(@dateFrom IS NULL OR DateFrom > @dateFrom)
AND
(@dateTo IS NULL OR DateTo < @dateTo)
answered Nov 8 at 5:23
D-Shih
24.4k61431
edited Nov 8 at 7:37
answered Nov 8 at 5:23
D-Shih
24.4k61431
answered Nov 8 at 5:23
D-Shih
24.4k61431
answered Nov 8 at 5:23
D-Shih
24.4k61431
24.4k61431
This won't work if both the variables are null. As per the question if both variables are null we need to return all the data
– Sanal Sunny
Nov 8 at 5:37
1
If@dateFromand@dateToareNULLit can return all data from tbl. because contain logic in()isOR
– D-Shih
Nov 8 at 5:39
1
didn't notice that
– Sanal Sunny
Nov 8 at 5:49
1
You have a typo in the last row, and your overlap check is wrong. You should checkfrom < @to and @from < toto check for overlapping periods.
– Zohar Peled
Nov 8 at 7:35
@ZoharPeled Thank for point out :)
– D-Shih
Nov 8 at 7:38
|
show 2 more comments
This won't work if both the variables are null. As per the question if both variables are null we need to return all the data
– Sanal Sunny
Nov 8 at 5:37
1
If@dateFromand@dateToareNULLit can return all data from tbl. because contain logic in()isOR
– D-Shih
Nov 8 at 5:39
1
didn't notice that
– Sanal Sunny
Nov 8 at 5:49
1
You have a typo in the last row, and your overlap check is wrong. You should checkfrom < @to and @from < toto check for overlapping periods.
– Zohar Peled
Nov 8 at 7:35
@ZoharPeled Thank for point out :)
– D-Shih
Nov 8 at 7:38
This won't work if both the variables are null. As per the question if both variables are null we need to return all the data
– Sanal Sunny
Nov 8 at 5:37
This won't work if both the variables are null. As per the question if both variables are null we need to return all the data
– Sanal Sunny
Nov 8 at 5:37
1
1
If
@dateFrom and @dateTo are NULL it can return all data from tbl. because contain logic in () is OR– D-Shih
Nov 8 at 5:39
If
@dateFrom and @dateTo are NULL it can return all data from tbl. because contain logic in () is OR– D-Shih
Nov 8 at 5:39
1
1
didn't notice that
– Sanal Sunny
Nov 8 at 5:49
didn't notice that
– Sanal Sunny
Nov 8 at 5:49
1
1
You have a typo in the last row, and your overlap check is wrong. You should check
from < @to and @from < to to check for overlapping periods.– Zohar Peled
Nov 8 at 7:35
You have a typo in the last row, and your overlap check is wrong. You should check
from < @to and @from < to to check for overlapping periods.– Zohar Peled
Nov 8 at 7:35
@ZoharPeled Thank for point out :)
– D-Shih
Nov 8 at 7:38
@ZoharPeled Thank for point out :)
– D-Shih
Nov 8 at 7:38
|
show 2 more comments
up vote
2
down vote
You can try BETWEEN Operator with ISNULL
SELECT * FROM Table1
WHERE ISNULL(OrderDate,'') BETWEEN COALESCE(@DateFrom,OrderDate,'') AND COALESCE(@DateTo,OrderDate,'')
Edited: Zohar's approach for NULL value in Table's Column.
Now it's working perfect!
answered Nov 8 at 5:37
im_one
323114
Assuming the OrderDate column is nullable, this will not return any of the rows where it's null.
– Zohar Peled
Nov 8 at 7:20
@ZoharPeled: I didn't consider that case!!! But Yes, I have just edited my answer by taking reference of Sunny's COALESCE. It works perfectly now. Thank you for Idea Zohar!
– im_one
Nov 8 at 8:43
You might also want to read the rest of the comments on his answer....
– Zohar Peled
Nov 8 at 8:45
@ZoharPeled: Yes, I did!
– im_one
Nov 8 at 8:49
add a comment |
up vote
2
down vote
You can try BETWEEN Operator with ISNULL
SELECT * FROM Table1
WHERE ISNULL(OrderDate,'') BETWEEN COALESCE(@DateFrom,OrderDate,'') AND COALESCE(@DateTo,OrderDate,'')
Edited: Zohar's approach for NULL value in Table's Column.
Now it's working perfect!
answered Nov 8 at 5:37
im_one
323114
Assuming the OrderDate column is nullable, this will not return any of the rows where it's null.
– Zohar Peled
Nov 8 at 7:20
@ZoharPeled: I didn't consider that case!!! But Yes, I have just edited my answer by taking reference of Sunny's COALESCE. It works perfectly now. Thank you for Idea Zohar!
– im_one
Nov 8 at 8:43
You might also want to read the rest of the comments on his answer....
– Zohar Peled
Nov 8 at 8:45
@ZoharPeled: Yes, I did!
– im_one
Nov 8 at 8:49
add a comment |
up vote
2
down vote
up vote
2
down vote
You can try BETWEEN Operator with ISNULL
SELECT * FROM Table1
WHERE ISNULL(OrderDate,'') BETWEEN COALESCE(@DateFrom,OrderDate,'') AND COALESCE(@DateTo,OrderDate,'')
Edited: Zohar's approach for NULL value in Table's Column.
Now it's working perfect!
answered Nov 8 at 5:37
im_one
323114
You can try BETWEEN Operator with ISNULL
SELECT * FROM Table1
WHERE ISNULL(OrderDate,'') BETWEEN COALESCE(@DateFrom,OrderDate,'') AND COALESCE(@DateTo,OrderDate,'')
Edited: Zohar's approach for NULL value in Table's Column.
Now it's working perfect!
answered Nov 8 at 5:37
im_one
323114
edited Nov 8 at 8:43
answered Nov 8 at 5:37
im_one
323114
answered Nov 8 at 5:37
im_one
323114
answered Nov 8 at 5:37
im_one
323114
323114
Assuming the OrderDate column is nullable, this will not return any of the rows where it's null.
– Zohar Peled
Nov 8 at 7:20
@ZoharPeled: I didn't consider that case!!! But Yes, I have just edited my answer by taking reference of Sunny's COALESCE. It works perfectly now. Thank you for Idea Zohar!
– im_one
Nov 8 at 8:43
You might also want to read the rest of the comments on his answer....
– Zohar Peled
Nov 8 at 8:45
@ZoharPeled: Yes, I did!
– im_one
Nov 8 at 8:49
add a comment |
Assuming the OrderDate column is nullable, this will not return any of the rows where it's null.
– Zohar Peled
Nov 8 at 7:20
@ZoharPeled: I didn't consider that case!!! But Yes, I have just edited my answer by taking reference of Sunny's COALESCE. It works perfectly now. Thank you for Idea Zohar!
– im_one
Nov 8 at 8:43
You might also want to read the rest of the comments on his answer....
– Zohar Peled
Nov 8 at 8:45
@ZoharPeled: Yes, I did!
– im_one
Nov 8 at 8:49
Assuming the OrderDate column is nullable, this will not return any of the rows where it's null.
– Zohar Peled
Nov 8 at 7:20
Assuming the OrderDate column is nullable, this will not return any of the rows where it's null.
– Zohar Peled
Nov 8 at 7:20
@ZoharPeled: I didn't consider that case!!! But Yes, I have just edited my answer by taking reference of Sunny's COALESCE. It works perfectly now. Thank you for Idea Zohar!
– im_one
Nov 8 at 8:43
@ZoharPeled: I didn't consider that case!!! But Yes, I have just edited my answer by taking reference of Sunny's COALESCE. It works perfectly now. Thank you for Idea Zohar!
– im_one
Nov 8 at 8:43
You might also want to read the rest of the comments on his answer....
– Zohar Peled
Nov 8 at 8:45
You might also want to read the rest of the comments on his answer....
– Zohar Peled
Nov 8 at 8:45
@ZoharPeled: Yes, I did!
– im_one
Nov 8 at 8:49
@ZoharPeled: Yes, I did!
– im_one
Nov 8 at 8:49
add a comment |
up vote
1
down vote
You can use ISNULL for this purpose
SELECT * FROM tbl
WHERE ISNULL(DateFrom,'')=COALESCE(@dateFrom,DateFrom,'')
AND ISNULL(DateTo,'')=COALESCE(@dateTo,DateTo,'')
answered Nov 8 at 5:15
Sanal Sunny
6548
Assuming the DateFrom or DateTo columns are nullable, this will not return any of the rows where one of them is null.
– Zohar Peled
Nov 8 at 7:20
@ZoharPeled I have updated the solution to get the values including the null ones. Can you please check whether is it the right way to do.
– Sanal Sunny
Nov 8 at 8:02
Yes, that will work better, but D-Shih's answer is still better - using functions on columns prevents the database engine from using any indexes that might help the query execution, making them unseargable. Also, you are assuming an exact date range match (which might be correct, we don't know because the OP didn't tell us).
– Zohar Peled
Nov 8 at 8:07
add a comment |
up vote
1
down vote
You can use ISNULL for this purpose
SELECT * FROM tbl
WHERE ISNULL(DateFrom,'')=COALESCE(@dateFrom,DateFrom,'')
AND ISNULL(DateTo,'')=COALESCE(@dateTo,DateTo,'')
answered Nov 8 at 5:15
Sanal Sunny
6548
Assuming the DateFrom or DateTo columns are nullable, this will not return any of the rows where one of them is null.
– Zohar Peled
Nov 8 at 7:20
@ZoharPeled I have updated the solution to get the values including the null ones. Can you please check whether is it the right way to do.
– Sanal Sunny
Nov 8 at 8:02
Yes, that will work better, but D-Shih's answer is still better - using functions on columns prevents the database engine from using any indexes that might help the query execution, making them unseargable. Also, you are assuming an exact date range match (which might be correct, we don't know because the OP didn't tell us).
– Zohar Peled
Nov 8 at 8:07
add a comment |
up vote
1
down vote
up vote
1
down vote
You can use ISNULL for this purpose
SELECT * FROM tbl
WHERE ISNULL(DateFrom,'')=COALESCE(@dateFrom,DateFrom,'')
AND ISNULL(DateTo,'')=COALESCE(@dateTo,DateTo,'')
answered Nov 8 at 5:15
Sanal Sunny
6548
You can use ISNULL for this purpose
SELECT * FROM tbl
WHERE ISNULL(DateFrom,'')=COALESCE(@dateFrom,DateFrom,'')
AND ISNULL(DateTo,'')=COALESCE(@dateTo,DateTo,'')
answered Nov 8 at 5:15
Sanal Sunny
6548
edited Nov 8 at 8:00
answered Nov 8 at 5:15
Sanal Sunny
6548
answered Nov 8 at 5:15
Sanal Sunny
6548
answered Nov 8 at 5:15
Sanal Sunny
6548
6548
Assuming the DateFrom or DateTo columns are nullable, this will not return any of the rows where one of them is null.
– Zohar Peled
Nov 8 at 7:20
@ZoharPeled I have updated the solution to get the values including the null ones. Can you please check whether is it the right way to do.
– Sanal Sunny
Nov 8 at 8:02
Yes, that will work better, but D-Shih's answer is still better - using functions on columns prevents the database engine from using any indexes that might help the query execution, making them unseargable. Also, you are assuming an exact date range match (which might be correct, we don't know because the OP didn't tell us).
– Zohar Peled
Nov 8 at 8:07
add a comment |
Assuming the DateFrom or DateTo columns are nullable, this will not return any of the rows where one of them is null.
– Zohar Peled
Nov 8 at 7:20
@ZoharPeled I have updated the solution to get the values including the null ones. Can you please check whether is it the right way to do.
– Sanal Sunny
Nov 8 at 8:02
Yes, that will work better, but D-Shih's answer is still better - using functions on columns prevents the database engine from using any indexes that might help the query execution, making them unseargable. Also, you are assuming an exact date range match (which might be correct, we don't know because the OP didn't tell us).
– Zohar Peled
Nov 8 at 8:07
Assuming the DateFrom or DateTo columns are nullable, this will not return any of the rows where one of them is null.
– Zohar Peled
Nov 8 at 7:20
Assuming the DateFrom or DateTo columns are nullable, this will not return any of the rows where one of them is null.
– Zohar Peled
Nov 8 at 7:20
@ZoharPeled I have updated the solution to get the values including the null ones. Can you please check whether is it the right way to do.
– Sanal Sunny
Nov 8 at 8:02
@ZoharPeled I have updated the solution to get the values including the null ones. Can you please check whether is it the right way to do.
– Sanal Sunny
Nov 8 at 8:02
Yes, that will work better, but D-Shih's answer is still better - using functions on columns prevents the database engine from using any indexes that might help the query execution, making them unseargable. Also, you are assuming an exact date range match (which might be correct, we don't know because the OP didn't tell us).
– Zohar Peled
Nov 8 at 8:07
Yes, that will work better, but D-Shih's answer is still better - using functions on columns prevents the database engine from using any indexes that might help the query execution, making them unseargable. Also, you are assuming an exact date range match (which might be correct, we don't know because the OP didn't tell us).
– Zohar Peled
Nov 8 at 8:07
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53201891%2fsql-server-select-data-from-between-a-range-of-dates-if-dates-exist-if-not-ret%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
What about the table itself? Does it also have a
DateFromandDateTocolumns, or just one date column?– Zohar Peled
Nov 8 at 6:51
1
If there are two columns then there is an additional question: what if the user choose (Dec 21, Dec 25) and you have one row for (Dec 20, Dec 26).
– Salman A
Nov 8 at 7:05
And what if the date column(s) are nullable? Suppose you have a single nullable date column, and a row where the date is null. Should you return it in all of the search options, or only if both parameters are null, or perhaps if at least one parameter is null? That's also a question to ask for a date range - if The one of the columns is null it's quite easy to understand the record has only one limit, but what if they are both nulls? (usually, this would be avoided declaring the ToDate column as non-nullable or a check constraint allowing only one of the columns to be null for each row)
– Zohar Peled
Nov 8 at 7:16
My point is, that this question might seem simple (and it might very well be simple), but there are too many details missing to give a good answer that is not based, at least partially, on guesses, or that is not very long so that it would cover all (or at least most) of these potential pitfalls.
– Zohar Peled
Nov 8 at 7:18
@S. Saleem : You have some answers below to pick! And please tick mark any of your best answer!
– im_one
Nov 13 at 5:02