Dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dates

Author

Topic

sanjay5219
Posting Yak Master

240 Posts

Posted - 2010-06-09 : 10:08:08

Dear All,

I have a two text box and in one text box i have to display last month 20date and in another text box we have to show 19th of nextmonth.

For Example

if today is 09th June 2010
T1=05/20/2010
T2=06/19/2010

if today is 21st June 2010
T1=06/20/2010
T2=07/19/2010

Please suggest

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-09 : 10:19:23

What are the border cases?
If today is the 20th, which date range do you want returned?
If today is the 19th, which date range do you want returned?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-09 : 10:21:14

Here is a starter

SELECT	DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 19),
	DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 18)

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-09 : 10:22:20

declare @d datetime
set @d='20100609'
select dateadd(month,datediff(month,0,getdate())+case when day(@d)<19 then -1 else 0 end ,19),dateadd(month,datediff(month,0,getdate())+case when day(@d)<19 then 0 else 1 end,18)

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-09 : 10:28:29

quote:
Originally posted by madhivanan

declare @d datetime
set @d='20100609'
select dateadd(month,datediff(month,0,getdate())+case when day(@d)<19 then -1 else 0 end ,19),dateadd(month,datediff(month,0,getdate())+case when day(@d)<19 then 0 else 1 end,18)

Madhivanan

Failing to plan is Planning to fail

Hey Madhi,
why isn't your answer "do it in your front end"?

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-09 : 10:31:58

quote:
Originally posted by webfred

quote:
Originally posted by madhivanan

declare @d datetime
set @d='20100609'
select dateadd(month,datediff(month,0,getdate())+case when day(@d)<19 then -1 else 0 end ,19),dateadd(month,datediff(month,0,getdate())+case when day(@d)<19 then 0 else 1 end,18)

Madhivanan

Failing to plan is Planning to fail

Hey Madhi,
why isn't your answer "do it in your front end"?

No, you're never too old to Yak'n'Roll if you're too young to die.

Yes.

I re-read the question and OP is using a front end

To OP,

simulate the logic in front end

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources