You create a view based on the following statement:
You grant the Select permission to User1 for this view. You need to change the view so that it display s only the
records that were processed in the month prior to the current month. You need to ensure that after the
changes, the view functions correctly for User1. Which four Transact-SQL statements should you use? (To
answer, move the appropriate SQL statements from the list of statements to the answer area and arrangethem
in the correct order.)
Answer:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/hh213020.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms186819.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms173846.aspx
<=why eomonth(getdate(),-1) won't work?
we need the records “that were processed in the month prior to the current month”.
If current month is January – then we would want the records for the December.
EOMONTH(GETDATE(), -1) = last day of Jan – 1 month = last day of December
so if our date is <= EOMONTH(GETDATE(),-1) we only get the dates BEFORE THE LAST DAY of December!
But we want to include that last day of December – that is why we add an additional day.
I think eomonth(getdate(),-1) starts on 00:00:00 on the last day of the month so if your date is at 5:00pm on the last day of the month, you’d still be greater than eomonth(getdate(),-1). I haven’t checked yet.
2,5,3,6 should be the correct answer
No. This is the correct:
2,5,4,6
2,5,4,6
You can check with AdventureWorks2012 database as below,
USE AdventureWorks2012
GO
DECLARE @CurrentDate DATETIME = ‘2007-05-22’
–SELECT dateadd(d,1,EOMONTH(GETDATE(),-2))
–SELECT dateadd(d,1,EOMONTH(GETDATE(),-1))
SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate >= dateadd(d,1,EOMONTH(@CurrentDate,-2))
AND OrderDate < dateadd(d,1,EOMONTH(@CurrentDate,-1))
both work the same way
yes ineeded
Yes, both will give the same results… interesting
If we assume that today is 15th of October
select dateadd(d, 1, month(getdate(), -1) will return 1st of October
select eomonth(getdate(), -1) will return 30th of September
process_date < dateadd(d, 1, month(getdate(), -1)
process_date < 01-10-2016
process_date <= eomonth(getdate(), -1)
process_date <= 30-09-2016
Looks the same but … probably if we compare dates with time
process_date < dateadd(d, 1, month(getdate(), -1)
process_date < 01-10-2016 00:00:00
process_date <= eomonth(getdate(), -1)
process_date <= 30-09-2016 00:00:00
In second approach we could miss records after midnight (30-09-2016 03:00:00)
Probably because of that process_date < dateadd(d, 1, month(getdate(), -1) is better option.
I used to be suggested this blog by way of my cousin. I’m not sure whether this post is written by means of him as no one else recognise such detailed about my trouble. You’re wonderful! Thanks!|
Everything is very open with a precise explanation of the issues. It was definitely informative. Your website is very useful. Many thanks for sharing!|