February 27, 2014 at 4:24 am
Hi,
I want to take Report from every month 26th(it may be last or current month) to Day-1 on daily basis.
For ex. StartDate='2014-01-26 00:00:00.000'
EndDate='2014-01-31 23:59:59.999'
OR
EndDate='2014-02-10 23:59:59.999'
OR
EndDate='2014-02-25 23:59:59.999'
My Query is i want to generate this date automatically everyday. Anyone help me??
February 27, 2014 at 4:56 am
Will CURRENT_TIMESTAMP work for you? If not, how do you determine what "Day-1" is?
John
February 27, 2014 at 5:21 am
John's right about defining day 1.
As a word of caution, you're not going to have a datetime data type with 999 as the decimal portion of the time like this: 2014-01-31 23:59:59.999. This is going to round and give you fits if you don't know what to look for. Use 2014-01-31 23:59:59.997 instead.
February 27, 2014 at 11:27 am
Data type of DATETIME2(3) will get you the 999 in the decimal portion if you need to get to that level of precision.
February 27, 2014 at 11:41 am
Maybe some calculations like this?
DECLARE @Datedatetime = '2014-02-25' --Test with any date
SELECT @Date,
CASE WHEN DAY(@Date) > 26 THEN DATEADD( DD, 26 - DAY(@Date), DATEADD( DD, DATEDIFF( DD, 0, @Date), 0))
ELSE DATEADD( DD, 25, DATEADD( MM, DATEDIFF(MM, 0, @Date) - 1, 0)) END,
DATEADD( MS, -3, DATEADD( DD, DATEDIFF( DD, 0, @Date), 0))
Do you understand how it works?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply