CODEWITHSUNDEEP
sqldatedatetimesybase
user898082
user898082

Reputation:

How to get the last day of the prior quarter?

How can I get the last day of the prior quarter?

Select DateAdd(day, -1, dateadd(qq, DateDiff(qq, 0, @Date), 0))

I found this SQL-Script, but it doesn't work for me.

Upvotes: 1

Views: 4128

Answers (2)

Simi
Simi

Reputation: 11

I have created a function to utilize it in my project

CREATE FUNCTION preQtr
(@pQtr DATE)
RETURNS Date
AS

BEGIN
DECLARE @OUTPUT DATE

SET @OUTPUT = (select DATEADD(D, -1, DATEADD(qq, DATEDIFF(qq, 0,@pQtr), 0)))
RETURN @OUTPUT
END

select dbo.preQtr('2016-06-30')
select dbo.preQtr('2016-03-31')

Also, you can get datetime when you use like this: enter image description here

select DATEADD(s, -1, DATEADD(qq, DATEDIFF(qq, 0,'2016-03-31'), 0)) As PreviousQuarterLastdayTime
select DATEADD(s, -1, DATEADD(qq, DATEDIFF(qq, 0,'2016-03-31'), +1)) As QuarterFirstdayTime

Upvotes: 0

user898082
user898082

Reputation:

The Script doesn't work in Sybase, because it can't compute the '0' you need the Unix-Time.

for example:

DECLARE @LastDay datetime
SELECT @LastDay = DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, '01.01.1970', GETDATE()), '01.01.1970'))
SELECT @LastDay

Today (11/07/2013) This will Output:

30.09.2013 00:00:00

the Date-format can change, depending on how your Server ist configured.

If you don't want the Time, you have to change the datatype of @LastDay to date

Upvotes: 2

Related Questions