Function ? - 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

Function ?

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-06-13 : 07:43:26

Hi how to take previous month endate
Declare @cEnd_Date datetime
Declare @pEnd_Date datetime
set @cEnd_Date='30 jun 2008'
set @pEnd_Date=dateadd(m,-1,@cEnd_Date)

output should be '31 may 2008'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-13 : 07:46:21

SELECT CONVERT(CHAR(11), @pEnd_Date, 106)

E 12°55'05.25"
N 56°04'39.16"

ranganath
Posting Yak Master

209 Posts

Posted - 2008-06-13 : 07:57:41

Hi,

peso your query shows '30 May 2008' but output should be '31 may 2008'

try with this

SELECT CONVERT(CHAR(11), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@cEnd_Date),0)), 106)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-13 : 08:11:17

Oh, OP means last day of previous month?
I thought OP just wanted the FORMAT to be correct.

SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @cEnd_Date, '18991231')

E 12°55'05.25"
N 56°04'39.16"

ranganath
Posting Yak Master

209 Posts

Posted - 2008-06-13 : 08:23:12

Hi,

this is incorrect

SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @cEnd_Date, '18991231')

try this

SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @cEnd_Date), '18991231')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-13 : 08:33:30

Great spot!
I missed a closing paranthesis after @cEnd_Date.

E 12°55'05.25"
N 56°04'39.16"

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-06-13 : 09:31:15

Txs peso and renganath

quote:
Originally posted by Peso

Great spot!
I missed a closing paranthesis after @cEnd_Date.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources