April 17, 2017 at 3:11 pm
Should be an easy one right ?
I like it in YYYYMMDD format
April 17, 2017 at 3:19 pm
mw112009 - Monday, April 17, 2017 3:11 PMShould be an easy one right ?
I like it in YYYYMMDD format
This will get the start and end dates of the prev month, based on today's date
SELECT
FirstOfPrevMonth = CONVERT(VARCHAR(8), DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) -1, 0), 112) ,
LastOfPrevMonth = CONVERT(VARCHAR(8), DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0) -1, 112)
April 17, 2017 at 4:30 pm
I recommend this article for your date needs.
http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 17, 2017 at 4:58 pm
mw112009 - Monday, April 17, 2017 3:11 PMShould be an easy one right ?
I like it in YYYYMMDD format
Yes, it IS easy. You should try it yourself. 😉
Shifting gears a bit... I don't know what you intend to use the dates for but... if you're going to use them in a WHERE clause to isolate rows for the previous month, then use the first of last month as the INclusive start and the first of this month as the EXclustive end date. That will make your code absolutely bullet-proof against any possible "normal temporal" data-type changes and get you into the habit of doing it consistently when any query requires such criteria.
Also, if you don't understand the things in the link for Lynn Pettis' list of helpful methods, you should spend some time on things like why they work and what the "0"s in his code represent. After all, you are the one that will need to support the code and you'll find some terrific uses of similar code in the future.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 18, 2017 at 6:43 am
Credit to DesNorton
Jeff: The dates are used as default values in a SSRS report. The preferred format is YYYYMMDD. So what Des provided helps!
The purpose of me saying "that was easy" was because I have confidence in your team ( Someone will be there who knows how to provide this within a few seconds... ).
Sorry, my work environment expects speedy delivery. So I depend on your team to help me out. Great help!
April 18, 2017 at 7:11 am
mw112009 - Tuesday, April 18, 2017 6:43 AMThe purpose of me saying "that was easy" was because I have confidence in your team ( Someone will be there who knows how to provide this within a few seconds... ).
Sorry, my work environment expects speedy delivery. So I depend on your team to help me out. Great help!
Ummmm... If you have that much confidence in us, how much are you going to pay us for doing your job for you?
April 18, 2017 at 7:15 am
mw112009 - Tuesday, April 18, 2017 6:43 AMCredit to DesNorton
Jeff: The dates are used as default values in a SSRS report. The preferred format is YYYYMMDD. So what Des provided helps!
The purpose of me saying "that was easy" was because I have confidence in your team ( Someone will be there who knows how to provide this within a few seconds... ).
Sorry, my work environment expects speedy delivery. So I depend on your team to help me out. Great help!
If you depend on a forum to do your work, you should report this to your manager. This only means that you're getting paid to do a job you are not qualified to do. If you're lucky, they'll train you.
If you don't want to do that, I suggest that you start to learn very fast. People will get tired of doing your work without getting paid for it.
April 18, 2017 at 7:44 am
mw112009 - Tuesday, April 18, 2017 6:43 AMCredit to DesNorton
Jeff: The dates are used as default values in a SSRS report. The preferred format is YYYYMMDD. So what Des provided helps!
The purpose of me saying "that was easy" was because I have confidence in your team ( Someone will be there who knows how to provide this within a few seconds... ).
Sorry, my work environment expects speedy delivery. So I depend on your team to help me out. Great help!
My work environment is demanding, as well. That's why I learned as much as I could about T-SQL and push myself to learn something new just about every day.
The reason why folks pointed you to a good reference (Lynn's blog on the subject of dates) was to try to help you get interested in learning something new. If you depend on forums to do your job, you will fail and we don't wish that on anyone. But it appears that you've not even tried to Google something to help yourself never mind studying T-SQL. We're trying to incite you to do that.
As for your YYYYMMDD format requirement, here's a hint that you should Google for. "YYYYMMDD format in SQL Server". Give it a try.
I also think it's a sin as to what they've done to "Books Online" (used to be a Help system for SQL Server). They no longer install it with the product and they've made it terribly difficult to download it and install it for 2016. However, if your desktop is setup with SSMS and it's been enabled, you should be able to press the F1 key and get to it online.
Seriously... if part of your job is to work with SQL Server T-SQL, spend some time learning it.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 18, 2017 at 7:49 am
mw112009 - Tuesday, April 18, 2017 6:43 AMCredit to DesNorton
Jeff: The dates are used as default values in a SSRS report. The preferred format is YYYYMMDD. So what Des provided helps!
The purpose of me saying "that was easy" was because I have confidence in your team ( Someone will be there who knows how to provide this within a few seconds... ).
Sorry, my work environment expects speedy delivery. So I depend on your team to help me out. Great help!
Much as we would all enjoy being on Jeff's team, we are not. We are just a group of individuals who share a common interest & have a broad mix of abilities and experience. We are not paid to help you out, so you really should not be depending on it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 18, 2017 at 9:19 am
To All ( Bran,Luis,Jeff,Phil and anyone who comments ... )
You guys are a great help. Sorry I am not able to pay you.
Hey, Helping a needy person is a act of Good Will. You may not get paid by me but you will receive a bigger return ( Count on that ).
Why do I say this ? I have complete trust in the Bible.
Also one more point... When helping others do it with love ( Never complain ...only then will you receive a return for your favor )
That is how real "Faith" works ( Galatians 5:6 , The only thing that counts is faith expressing itself through love. ) .
Ha Ha... I can see a few of you may be getting pizzed off by my comments... Hey,no hard feelings...but I have 100% faith in what I wrote above.. if it works for me it works for you. It is a UNIVERSAL LAW.. guaranteed to work.. )
April 18, 2017 at 9:32 am
mw112009 - Tuesday, April 18, 2017 9:19 AMTo All ( Bran,Luis,Jeff,Phil and anyone who comments ... )
You guys are a great help. Sorry I am not able to pay you.Hey, Helping a needy person is a act of Good Will. You may not get paid by me but you will receive a bigger return ( Count on that ).
Why do I say this ? I have complete trust in the Bible.
Also one more point... When helping others do it with love ( Never complain ...only then will you receive a return for your favor )
That is how real "Faith" works ( Galatians 5:6 , The only thing that counts is faith expressing itself through love. ) .Ha Ha... I can see a few of you may be getting pizzed off by my comments... Hey,no hard feelings...but I have 100% faith in what I wrote above.. if it works for me it works for you. It is a UNIVERSAL LAW.. guaranteed to work.. )
We're not really expecting to get paid. We help in here because we want to give back what we have learned from this community.
What you need to realize is that you're lying to your employer pretending to know something you don't. If you trust the Bible, you should know that you shouldn't lie.
In the future, if you want help, you should help yourself first.
April 18, 2017 at 9:37 am
mw112009 - Tuesday, April 18, 2017 9:19 AMTo All ( Bran,Luis,Jeff,Phil and anyone who comments ... )
You guys are a great help. Sorry I am not able to pay you.Hey, Helping a needy person is a act of Good Will. You may not get paid by me but you will receive a bigger return ( Count on that ).
Why do I say this ? I have complete trust in the Bible.
Also one more point... When helping others do it with love ( Never complain ...only then will you receive a return for your favor )
That is how real "Faith" works ( Galatians 5:6 , The only thing that counts is faith expressing itself through love. ) .Ha Ha... I can see a few of you may be getting pizzed off by my comments... Hey,no hard feelings...but I have 100% faith in what I wrote above.. if it works for me it works for you. It is a UNIVERSAL LAW.. guaranteed to work.. )
You are 100% correct in the good will aspect. People who help on the forums do it out of good will. When the good will of people starts getting abused, there will come a time when the good will either stops, turns into sharp comments intended to correct the abusive nature, or flat out disappears. The comments made impugn a behavior that is not very pleasing either from an ecclesiastical point of view or from a professional point of view.
Here is another tidbit to go with the goodwill - Matthew 4:19. I will let you read it and enjoy. Most contributors here on the forums prefer to adhere to this principle along with the service and goodwill that you mentioned.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 18, 2017 at 9:38 am
I think you've missed everyone's point here. Our point is that your need isn't for answers, you need to learn, which you aren't doing.
I see someone in need as someone who can't fight/fend/speak for themselves. You can. If you can't write SQL when your job is to write SQL then you're not in need; you're in the wrong job. You aren't in need, you need to learn and should want to.
Have you ever heard the proverb "give a man a fish and you feed him for a day; teach a man to fish and you feed him for a lifetime"? We're not here to give you fish, we're trying to teach you how get them yourself. If you aren't going to try to learn to fish, you're going to end up hungry, as you're not going to be given free fish every day for the rest of your life.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 18, 2017 at 9:57 am
Thom: My job is not to write SQL. When I need to extract data I need help. I can write some SQL ( In fact if I were to write that by myself I would have written a scalar function.. That's just my way of thinking ) But when you ask a group of people, they will present a mix of answers ( that is good ) and I get to pick the best one. So I like to ask. I hope that is not wrong. and in this case someone was nice enough to help me with one single expression that gives me what I wanted.
If you think a user is abusing.. then simply do not reply ( Or just say go search google or learn from this or that book, or point me to a resource )
I am sure there are many people who just love to throw the best answer they think would be best to use. So one person did that! Great! )
BTW .. no hard feelings
April 18, 2017 at 10:05 am
Thom A - Tuesday, April 18, 2017 9:38 AMHave you ever heard the proverb "give a man a fish and you feed him for a day; teach a man to fish and you feed him for a lifetime"?
Haha, that is the reference from Matthew I posted.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply