July 17, 2020 at 5:55 am
Hi
I have below query.
Select EmpCode,Deptt,EmpName from Employee
I want if Date is 16th then record from Day 1 to Day 15 should get displayed.
If Date is 1 then record's from 16th to Last Date of previous month should get displayed.
Thanks
July 17, 2020 at 9:17 am
check out date functions in books online !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 17, 2020 at 2:57 pm
Hi
I have below query.
Select EmpCode,Deptt,EmpName from Employee
I want if Date is 16th then record from Day 1 to Day 15 should get displayed.
If Date is 1 then record's from 16th to Last Date of previous month should get displayed.
Thanks
What are you going to do if the system is down for maintenance or other reasons on the 1st and 16th of the month?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 17, 2020 at 3:08 pm
Just to confirm, if the date was the 15th, would you want it from the 28th/29th/30th/31st (depending on the month) to the 14th?
Basically, are you wanting it from 15 days ago until yesterday?
OR is this query only going to be run on the 16th and the 1st?
I am just trying to figure out the logic because the logic for the 1st is not the same as the logic on the 16th.
ALSO - in your query, what is your date column? EmpCode, Deptt, and EmpName do not sound like they are date or datetime columns.
But, to get you on the ground running:
1 - GETDATE() <-- gets the current date
2 - DATEADD(section, value, date) <--will let you add or remove days if section is "day", value is the number of days to add or subtract (positive will add, negative will subtract), and date is the start date. So to get yesterday: DATEADD(day,-1,GETDATE())
Does that help?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 17, 2020 at 3:29 pm
This should give you the general idea...
DECLARE
@today date = '2020-07-16',
@beg_dt date,
@end_dt date;
SELECT
@beg_dt = CASE WHEN dt.cur_day < 16 THEN DATEFROMPARTS(dt.prev_year, dt.prev_month, 16) ELSE DATEFROMPARTS(dt.cur_year, dt.cur_month, 1) END,
@end_dt = CASE WHEN dt.cur_day < 16 THEN DATEFROMPARTS(dt.cur_year, dt.cur_month, 1) ELSE DATEFROMPARTS(dt.cur_year, dt.cur_month, 16) END-- note end date is 1 day later than your cutoff.
FROM
( VALUES (
DATEPART(DAY, @today),
DATEPART(MONTH, @today),
DATEPART(YEAR, @today),
DATEPART(MONTH, DATEADD(MONTH, -1, @today)),
DATEPART(YEAR, DATEADD(MONTH, -1, @today))
) ) dt (cur_day, cur_month, cur_year, prev_month, prev_year);
--SELECT
--beg_dt = @beg_dt,
--end_dt = @end_dt;
-------------------------
SELECT
*
FROM
dbo.some_table st
WHERE
st.some_date >= @beg_dt
AND st.some_date < @end_dt;-- using the "<" with the @end_dt ensures that you get ALL fows for the desired date range.
July 17, 2020 at 7:36 pm
;WITH cte_date_calcs AS (
SELECT
CASE WHEN todays_day >= 16
THEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
ELSE DATEADD(DAY, 15, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0))
END AS start_date,
CASE WHEN todays_day >= 16
THEN DATEADD(DAY, 15, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
ELSE DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
END AS end_date
FROM (
SELECT DAY(GETDATE()) AS todays_day
) AS calc1
)
SELECT e.EmpCode,e.Deptt,e.EmpName
FROM dbo.Employee e
CROSS JOIN cte_date_calcs cdc
WHERE e.date >= cdc.start_date AND e.date < cdc.end_date
For consistency, I'm sticking with the standard pattern of date manipulation, i.e.:
DATEADD(<time_period>, DATEDIFF(<time_period>, 0, GETDATE()), 0)
with additional DATEADD(s) if/as needed to further refine the date/datetime value.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 17, 2020 at 9:25 pm
Heh... I'm still waiting to hear back from the OP with an answer to my question! 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
July 17, 2020 at 9:49 pm
Heh... I'm still waiting to hear back from the OP with an answer to my question! 😉
It was a bit of a vague question. Is the query only run on 2 days of the month OR were those just example dates to show what was expected? What should happen on dates outside those 2? Does the source data have a DATE or DATETIME column? If the original query is identical to "SELECT * FROM Employee", then there is nothing that can be done to show what they are looking for unless one of EmpCode, Deptt, or EmpName is a DATE or DATETIME datatype and if it is, that is some strange naming convention.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply