This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
-2
I have Date of birth column in SQL where Year part is defaulted to 2296 for all the rows.
Now the requirement is to get Next 7 days records from current date using SQL for reporting.
How to write SQL query to get the desired result?
Key part to notice here is year set to default value 2296 for all rows. And this was done for compliance reason.
I had 3 requirements- 1. Today's Birthday - Select Id from tableA where Day(dob)=Day(GetDate()) And Month(dob)= Month(GetDate())
I am really stuck with the 3rd requirement which I am posting in my original post.
Try something like this:
declare @start date = datefromparts(2296, month(getdate()), day(getdate()))
declare @end date = dateadd(day, 6, @start)
select * from TableA where dob between @start and @end
It assumes that there is no time part.
Thank you for your help!
This query will not work when we try to change Today Date with 26th December 2022. Because 26th Dec + 7 days will go to next year and query does not return records from next year
Hi @Bipin kumar
Year part is defaulted to 2296 for all the rows
the requirement is to get Next 7 days records from current date
You could replace the Year '2296' with current year '2022' first and then do a filter, like this:
SELECT * FROM TableC
WHERE REPLACE(dob,'2296',YEAR(GETDATE())) BETWEEN CONVERT(DATE, GETDATE()) AND CONVERT(DATE,DATEADD(DAY,6,GETDATE()))
Best regards,
LiHong
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Thank you for your reply.
What will happen if I execute your query on 28th December??
I don't think I will get 2023 year record because we replaced the year part with 2022.
Hi @Bipin kumar
Yes, you are right, this code will not return 2023 year records.
How about using an IF...ELSE statement, like this:
IF MONTH(GETDATE())=12 AND DAY(GETDATE())>=26
BEGIN
SELECT * FROM TableC
WHERE (REPLACE(dob,'2296',YEAR(GETDATE())) BETWEEN CONVERT(DATE, GETDATE()) AND DATEFROMPARTS(YEAR(GETDATE()),12,31))
OR (REPLACE(dob,'2296',YEAR(GETDATE())+1) BETWEEN DATEFROMPARTS(YEAR(GETDATE())+1,1,1) AND CONVERT(DATE,DATEADD(DAY,6,GETDATE())))
END
ELSE
BEGIN
SELECT * FROM TableC
WHERE REPLACE(dob,'2296',YEAR(GETDATE())) BETWEEN CONVERT(DATE, GETDATE()) AND CONVERT(DATE,DATEADD(DAY,6,GETDATE()))
END
Best regards,
LiHong
I get below error when I am executing this query
CONVERSION FAILED WHEN CONVERTING DATE AND/OR TIME FROM CHARACTER STRING
Hi @Bipin kumar
What's the datatype of your dob column?
Check a revised query:
declare @start1 date = datefromparts(2296, month(getdate()), day(getdate()))
declare @end1 date = dateadd(day, 6, @start1)
declare @start2 date = datefromparts(2295, month(getdate()), day(getdate()))
declare @end2 date = dateadd(day, 6, @start2)
select * from TableA
where dob between @start1 and @end1
or dob between @start2 and @end2