Date Function

Question

Date Function

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

SumonB (1/20/2016)
This is used because if Current Date is less than 7th of the month, it should go to previous month to set the start date

OK, this is opposite to the logic in your original script.

That script was saying "If NOT @Cnt1 > 7 set @StartDate to the end of the previous month. Otherwise set @StartDate to 15 ago from today".

And how about 8th of month?

15 days before 8th of month will still start in a previous month.

How should it work?

_____________
Code for TallyGenerator

MMartin1 One Orange Chip Points: 27795 More actions · Answer 2

Since this is a SS2K8 T-SQL forum,

A cleaner approach:

Declare @StartDate Date;

Declare @EndDate Date='2016-01-05';--getdate();

Set@StartDate = dateadd(d,-15, @EndDate);

Select@startDate, @endDate;

Just changing variable types from datetime to Date declaration

----------------------------------------------------

MMartin1 One Orange Chip Points: 27795 More actions · Answer 3

Sergiy (1/20/2016)
SumonB (1/20/2016)
This is used because if Current Date is less than 7th of the month, it should go to previous month to set the start date
OK, this is opposite to the logic in your original script.
That script was saying "If NOT @Cnt1 > 7 set @StartDate to the end of the previous month. Otherwise set @StartDate to 15 ago from today".
And how about 8th of month?
15 days before 8th of month will still start in a previous month.
How should it work?

I think he was not totally understanding date math in sql server. Once he defined the problem better Scott was able to give him a solution. I was thrown by this as well .

----------------------------------------------------

Sergiy SSC Guru Points: 110208 More actions · Answer 4

I think he was not totally understanding date math in sql server. Once he defined the problem better Scott was able to give him a solution. I was thrown by this as well .

I think both you and Scott did not care to understand the requirements.

Following statement is not clear for me too, but it does not mean it may be completely ignored.

SumonB (1/20/2016)
This is used because if Current Date is less than 7th of the month, it should go to previous month to set the start date

It's about some 7th day threshold.

I do not see how either yours or Scott's solution implement this requirement.

I'm not sure what would be the right solution (the requrements are not clear), but there is no doubt - your solutions are wrong.

_____________
Code for TallyGenerator

MMartin1 One Orange Chip Points: 27795 More actions · Answer 5

Sergiy (1/23/2016)
I think he was not totally understanding date math in sql server. Once he defined the problem better Scott was able to give him a solution. I was thrown by this as well .
I think both you and Scott did not care to understand the requirements.
Following statement is not clear for me too, but it does not mean it may be completely ignored.
SumonB (1/20/2016)
This is used because if Current Date is less than 7th of the month, it should go to previous month to set the start date
It's about some 7th day threshold.
I do not see how either yours or Scott's solution implement this requirement.
I'm not sure what would be the right solution (the requrements are not clear), but there is no doubt - your solutions are wrong.

No, since this particular statement is vague the best we can do is start him off with some basics to try and get more information. This is not wrong, but thank you for caring enough to point that out.

----------------------------------------------------

SumonB SSC-Addicted Points: 490 More actions · Answer 6

Okay here is the scenario.

I am creating a DBA Report where I need to show the backup details for last 15 days including today's date.

I have 2 variables @StartDate and @EndDate.. I need to populate this 2 variables. I am just trying to get a solution, which will work in 2008 R2 / 2012 or 2014.

Thanks for your help.

MMartin1 One Orange Chip Points: 27795 More actions · Answer 7

SumonB (1/25/2016)
Okay here is the scenario.
I am creating a DBA Report where I need to show the backup details for last 15 days including today's date.
I have 2 variables @StartDate and @EndDate.. I need to populate this 2 variables. I am just trying to get a solution, which will work in 2008 R2 / 2012 or 2014.
Thanks for your help.

Hi SumonB:

Did this solution not work for you ... ?

Declare @StartDate Date;

Declare @EndDate Date='2016-01-05';--getdate();

Set@StartDate = dateadd(d,-15, @EndDate);

Select@startDate, @endDate;

Just uncomment the getdate portion. You can use the DATE data type in the editions you mention.

Declare @StartDate Date;

Declare @EndDate Date=getdate();

Set@StartDate = dateadd(d,-15, @EndDate);

Select@startDate as StartDate, @endDate as EndDate;

----------------------------------------------------

Sergiy SSC Guru Points: 110208 More actions · Answer 8

MMartin1 (1/25/2016)
No, since this particular statement is vague the best we can do is start him off with some basics to try and get more information. This is not wrong, but thank you for caring enough to point that out.

There is a requirement.

It might be vague and/or confusing, but it's there.

The solution ignores it altogether, for whatever reason.

The solution is wrong.

End of story.

_____________
Code for TallyGenerator

ScottPletcher SSC Guru Points: 101093 More actions · Answer 9

Sergiy (1/25/2016)
MMartin1 (1/25/2016)
No, since this particular statement is vague the best we can do is start him off with some basics to try and get more information. This is not wrong, but thank you for caring enough to point that out.
There is a requirement.
It might be vague and/or confusing, but it's there.
The solution ignores it altogether, for whatever reason.
The solution is wrong.
End of story.

No, don't think so. I think that code was copied from somewhere else because whoever did the new code wasn't sure how to do it so they tried to copy it. The other code may have a legitimate need to distinguish day 7 from other days, but not the current code.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

MMartin1 One Orange Chip Points: 27795 More actions · Answer 10

Sergiy (1/25/2016)
MMartin1 (1/25/2016)
No, since this particular statement is vague the best we can do is start him off with some basics to try and get more information. This is not wrong, but thank you for caring enough to point that out.
There is a requirement.
It might be vague and/or confusing, but it's there.
The solution ignores it altogether, for whatever reason.
The solution is wrong.
End of story.

Hi Sergiy:

Please see the clarified requirements four posts above. Can you detail how the subsequent post does not provide the OP something to work with? We got to this point by engaging with the OP, not sure if you noticed. I think it is a good idea to let him decide if this works for him or not also.

The solution ignores it altogether, for whatever reason.

A solution was not declared, just a "is this what you meant?" Remember we are conversing here.

Thanks

----------------------------------------------------

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 11

SumonB,

have you had a look at the solution posted here?

😎

Sergiy SSC Guru Points: 110208 More actions · Answer 12

ScottPletcher (1/25/2016)
Sergiy (1/25/2016)
MMartin1 (1/25/2016)
No, since this particular statement is vague the best we can do is start him off with some basics to try and get more information. This is not wrong, but thank you for caring enough to point that out.
There is a requirement.
It might be vague and/or confusing, but it's there.
The solution ignores it altogether, for whatever reason.
The solution is wrong.
End of story.
No, don't think so. I think that code was copied from somewhere else because whoever did the new code wasn't sure how to do it so they tried to copy it. The other code may have a legitimate need to distinguish day 7 from other days, but not the current code.

I was not referring to the code.

I might ha e noticed if you'd pay attention.

_____________
Code for TallyGenerator

ScottPletcher SSC Guru Points: 101093 More actions · Answer 13

Sergiy (1/25/2016)
ScottPletcher (1/25/2016)
Sergiy (1/25/2016)
MMartin1 (1/25/2016)
No, since this particular statement is vague the best we can do is start him off with some basics to try and get more information. This is not wrong, but thank you for caring enough to point that out.
There is a requirement.
It might be vague and/or confusing, but it's there.
The solution ignores it altogether, for whatever reason.
The solution is wrong.
End of story.
No, don't think so. I think that code was copied from somewhere else because whoever did the new code wasn't sure how to do it so they tried to copy it. The other code may have a legitimate need to distinguish day 7 from other days, but not the current code.
I was not referring to the code.
I might ha e noticed if you'd pay attention.

I believe the OP's initial source code was probably copied from other code just to get a starting point for the current task. The original code that was copied had a need for "day > 7" logic, but the current code does not. Yes, that's just a guess, but based on the re-stated requirements from the OP, I believe it's a reasonable guess.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".