First date of the Month (to date on Range basis)

Question

First date of the Month (to date on Range basis)

smer

SSCommitted

Points: 1684
More actions
September 28, 2015 at 1:46 am

#300988

Hi Team,
Help to get the Sql Query e.g when execute on 8/15/2015 get the records from 8/1/2015 to 8/15/2015, if run on 9/20/2015 get the results from 9/1/2015 to 9/20/2015
Same for every month ?

Viewing 15 posts - 1 through 15 (of 15 total)

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

Neil Burton SSC-Insane Points: 23000 More actions · Answer 1

smer (9/28/2015)
Hi Team,
Help to get the Sql Query e.g when execute on 8/15/2015 get the records from 8/1/2015 to 8/15/2015, if run on 9/20/2015 get the results from 9/1/2015 to 9/20/2015
Same for every month ?

If you have a look at this link[/url] it will give you some very useful date calculations. There's one there for the start of a month.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 2

smer (9/28/2015)
Hi Team,
Help to get the Sql Query e.g when execute on 8/15/2015 get the records from 8/1/2015 to 8/15/2015, if run on 9/20/2015 get the results from 9/1/2015 to 9/20/2015
Same for every month ?

I suggest you use

WHERE YourDateColumn >= @StartDate

AND YourDateColumn < @DayAfterEndDate

@StartDate for beginning of the month would be

DATEADD(Month, DATEDIFF(Month, 0, GetDate()), 0)

and @DayAfterEndDate for "tomorrow" (i.e. the day after your end point)

DATEADD(Day, DATEDIFF(Day, 0, GetDate()) + 1, 0

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Kristen-173977 (9/28/2015)
smer (9/28/2015)
Hi Team,
Help to get the Sql Query e.g when execute on 8/15/2015 get the records from 8/1/2015 to 8/15/2015, if run on 9/20/2015 get the results from 9/1/2015 to 9/20/2015
Same for every month ?
I suggest you use
WHERE YourDateColumn >= @StartDate
AND YourDateColumn < @DayAfterEndDate
@StartDate for beginning of the month would be
DATEADD(Month, DATEDIFF(Month, 0, GetDate()), 0)
and @DayAfterEndDate for "tomorrow" (i.e. the day after your end point)
DATEADD(Day, DATEDIFF(Day, 0, GetDate()) + 1, 0

+1000

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 4

Jeff Moden (9/28/2015)
+1000

My new high score :w00t:

FWIW a +1000 pet-hate of mine is using short mnemonics for the datepart parameters e.g. DATEADD(dd, ... instead of DATEADD(Day, ...

I have no idea, without checking, whether "M" is Month, Minute, Millisecond, Microsecond and which of W and WW is Week and which is WeekDay, is N nanosecond, or something else (its something else ...) and I think it leads to coding errors, e.g. during maintenance, as someone else looking at the code "assumes" that X is the correct mnemonic for the coding situation. Using the mnemonics seems to be very popular though ...

Whereas I reckon I can reliably get the full datepart names right-first-time-every-time:

year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond, nanosecond

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 5

Kristen-173977 (9/28/2015)
smer (9/28/2015)
Hi Team,
Help to get the Sql Query e.g when execute on 8/15/2015 get the records from 8/1/2015 to 8/15/2015, if run on 9/20/2015 get the results from 9/1/2015 to 9/20/2015
Same for every month ?
I suggest you use
WHERE YourDateColumn >= @StartDate
AND YourDateColumn < @DayAfterEndDate
@StartDate for beginning of the month would be
DATEADD(Month, DATEDIFF(Month, 0, GetDate()), 0)
and @DayAfterEndDate for "tomorrow" (i.e. the day after your end point)
DATEADD(Day, DATEDIFF(Day, 0, GetDate()) + 1, 0

Why:

WHERE YourDateColumn >= @StartDate

AND YourDateColumn < @DayAfterEndDate

and not:

WHERE YourDateColumn >= @StartDate

AND YourDateColumn <= GETDATE()

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 6

Alvin Ramard (9/28/2015)
Why:
WHERE YourDateColumn >= @StartDate
AND YourDateColumn < @DayAfterEndDate
and not:
WHERE YourDateColumn >= @StartDate
AND YourDateColumn <= GETDATE()

If it is a DATE datatype column that's fine. If it is a DATETIME datatype then there is still time between GetDate() and midnight tonight.

You cannot say

AND YourDateColumn <= '20150928 23:59:59.999'

because SQL will round 23:59:59.999 up to midnight

We have a mix of DATE and DATETIME datatypes in our code and my preference is to always use

AND YourDateColumn < @DayAfterEndDate

so that I don't have to worry about whether the column being tested is DATE or DATETIME - or might CHANGE! between the two datatypes in the future.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 7

Kristen-173977 (9/28/2015)
Alvin Ramard (9/28/2015)
Why:
WHERE YourDateColumn >= @StartDate
AND YourDateColumn < @DayAfterEndDate
and not:
WHERE YourDateColumn >= @StartDate
AND YourDateColumn <= GETDATE()
If it is a DATE datatype column that's fine. If it is a DATETIME datatype then there is still time between GetDate() and midnight tonight.
You cannot say
AND YourDateColumn <= '20150928 23:59:59.999'
because SQL will round 23:59:59.999 up to midnight
We have a mix of DATE and DATETIME datatypes in our code and my preference is to always use
AND YourDateColumn < @DayAfterEndDate
so that I don't have to worry about whether the column being tested is DATE or DATETIME - or might CHANGE! between the two datatypes in the future.

I realized after I posted my comment that I had forgotten about the time before midnight.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

Alvin Ramard (9/28/2015)
Kristen-173977 (9/28/2015)
Alvin Ramard (9/28/2015)
Why:
WHERE YourDateColumn >= @StartDate
AND YourDateColumn < @DayAfterEndDate
and not:
WHERE YourDateColumn >= @StartDate
AND YourDateColumn <= GETDATE()
If it is a DATE datatype column that's fine. If it is a DATETIME datatype then there is still time between GetDate() and midnight tonight.
You cannot say
AND YourDateColumn <= '20150928 23:59:59.999'
because SQL will round 23:59:59.999 up to midnight
We have a mix of DATE and DATETIME datatypes in our code and my preference is to always use
AND YourDateColumn < @DayAfterEndDate
so that I don't have to worry about whether the column being tested is DATE or DATETIME - or might CHANGE! between the two datatypes in the future.
I realized after I posted my comment that I had forgotten about the time before midnight.

Sometimes we need to leave those rows out because they're still in the future. It depends on what's needed and the design of the application (some table shouldn't have future records).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 9

Luis Cazares (9/28/2015)
Alvin Ramard (9/28/2015)
Kristen-173977 (9/28/2015)
Alvin Ramard (9/28/2015)
Why:
WHERE YourDateColumn >= @StartDate
AND YourDateColumn < @DayAfterEndDate
and not:
WHERE YourDateColumn >= @StartDate
AND YourDateColumn <= GETDATE()
If it is a DATE datatype column that's fine. If it is a DATETIME datatype then there is still time between GetDate() and midnight tonight.
You cannot say
AND YourDateColumn <= '20150928 23:59:59.999'
because SQL will round 23:59:59.999 up to midnight
We have a mix of DATE and DATETIME datatypes in our code and my preference is to always use
AND YourDateColumn < @DayAfterEndDate
so that I don't have to worry about whether the column being tested is DATE or DATETIME - or might CHANGE! between the two datatypes in the future.
I realized after I posted my comment that I had forgotten about the time before midnight.
Sometimes we need to leave those rows out because they're still in the future. It depends on what's needed and the design of the application (some table shouldn't have future records).

I thought about that too. If the date column was an order date, then there should not be any future dates/times, but it's an expected ship date, then the opposite would be true.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 10

In these situations I adopt what I consider to be a Defensive Programming stance. Of course we can all argue that Black-is-White on this situation 🙂 It may be that the system does allow future dates and we definitely don't want a date/time that is not yet due.

Assuming there should be no future dates I could say

SET @EndDate = GetDate()

SELECT ...

...

WHERE MyDateColumn >= @StartDate

AND MyDateColumn <= @EndDate

to ensure I don't accidentally get any future-dated rows.

But if they shouldn't be there I wouldn't program for that eventuality, I would treat that as a bug that had to be solved as a separate issue, when it was detected / reported.

In fact for this situation

WHERE MyDateColumn >= @StartDate

would be enough.

However, assuming the first scenario, I would program against the @EndDate potentially becoming something other than "now"

SET @EndDate = DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0) -- Date-after-end-point

SELECT ...

...

WHERE MyDateColumn >= @StartDate

AND MyDateColumn < @EndDate

so that @EndDate could become any suitable cutoff date in the future.

Dee-ZA Old Hand Points: 347 More actions · Answer 11

SELECT CONVERT(DATE, DATEADD(DAY, - DAY(GETDATE()) + 1, GETDATE()))

Dee-ZA Old Hand Points: 347 More actions · Answer 12

WHERE UpdateDate >= CONVERT(DATE, DATEADD(DAY, - DAY(GETDATE()) + 1, GETDATE()))

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

Kristen-173977 (9/28/2015)
Jeff Moden (9/28/2015)
+1000
My new high score :w00t:
FWIW a +1000 pet-hate of mine is using short mnemonics for the datepart parameters e.g. DATEADD(dd, ... instead of DATEADD(Day, ...
I have no idea, without checking, whether "M" is Month, Minute, Millisecond, Microsecond and which of W and WW is Week and which is WeekDay, is N nanosecond, or something else (its something else ...) and I think it leads to coding errors, e.g. during maintenance, as someone else looking at the code "assumes" that X is the correct mnemonic for the coding situation. Using the mnemonics seems to be very popular though ...
Whereas I reckon I can reliably get the full datepart names right-first-time-every-time:
year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond, nanosecond

Heh... we're even! A pet hate of mine is people that spell the date-parts out especially when there are many nested values. 😛 I've never had any code errors because of it either. Seems like a lot of unnecessary clutter to me. That, notwithstanding, I won't change someone else's code just because of my dislike for it nor will I try to convince anyone that they're wrong in their choice to use the long versions because they're not. It's a style preference of mine that has worked very well for me and I also understand why other's might prefer otherwise.

Just as an interesting point, I find it odd that people that don't like the 2 character date parts will still use "0" to represent 1900-01-01 and "-1" in next day calculations. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Jeff Moden (10/1/2015)
Kristen-173977 (9/28/2015)
Jeff Moden (9/28/2015)
+1000
My new high score :w00t:
FWIW a +1000 pet-hate of mine is using short mnemonics for the datepart parameters e.g. DATEADD(dd, ... instead of DATEADD(Day, ...
I have no idea, without checking, whether "M" is Month, Minute, Millisecond, Microsecond and which of W and WW is Week and which is WeekDay, is N nanosecond, or something else (its something else ...) and I think it leads to coding errors, e.g. during maintenance, as someone else looking at the code "assumes" that X is the correct mnemonic for the coding situation. Using the mnemonics seems to be very popular though ...
Whereas I reckon I can reliably get the full datepart names right-first-time-every-time:
year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond, nanosecond
Heh... we're even! A pet hate of mine is people that spell the date-parts out especially when there are many nested values. 😛 I've never had any code errors because of it either. Seems like a lot of unnecessary clutter to me. That, notwithstanding, I won't change someone else's code just because of my dislike for it nor will I try to convince anyone that they're wrong in their choice to use the long versions because they're not. It's a style preference of mine that has worked very well for me and I also understand why other's might prefer otherwise.
Just as an interesting point, I find it odd that people that don't like the 2 character date parts will still use "0" to represent 1900-01-01 and "-1" in next day calculations. 😛

I've just found it easier to use the full name of the part as I tend to forget a few of the abbreviated forms or I confuse one for something else. More the former than the latter.