regarding date 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

regarding date function

Author

Topic

lakshmi04
Starting Member

8 Posts

Posted - 2012-10-03 : 13:55:33

Hello Sir,

They are manual reports.I want to change to run automatically.So I want to take variables and store the date values and give that variables as Inputs.

Now i have end date as quarter end date for example:

In September , Iam running the quarter Month end reports.

And now I want start date as last (Previous year's) date that is , December month date.So please provide me solution regarding this.

Thank you..

lakshmi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-03 : 14:03:02

you can use expression like below

dateadd(DateInterval.Day,datediff(DateInterval.Day,CDate("01/01/1900"),Now())-1,CDate("01/01/1900"))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lakshmi04
Starting Member

8 Posts

Posted - 2012-10-04 : 03:52:21

thanku for the solution
but it is not working..

lakshmi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-04 : 10:35:28

quote:
Originally posted by lakshmi04

thanku for the solution
but it is not working..

lakshmi

are you asking for ssrs expression or t-sql query?

in t-sql its done as


SELECT DATEADD(mm,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) AS StartDate,
DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)-1 as EndDate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lakshmi04
Starting Member

8 Posts

Posted - 2012-10-04 : 14:08:24

ya its ok..thanku...
and one more thing i want is
I want start date as dec 31st 2011.so for the query you have sent dont want as 2011-12-01 but I want as 2011-12-31.
Please give function for this.

lakshmi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-04 : 15:11:57

quote:
Originally posted by lakshmi04

ya its ok..thanku...
and one more thing i want is
I want start date as dec 31st 2011.so for the query you have sent dont want as 2011-12-01 but I want as 2011-12-31.
Please give function for this.

lakshmi

did you check end date expression? thats exactly what it will return

SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)-1 as EndDate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lakshmi04
Starting Member

8 Posts

Posted - 2012-10-04 : 15:23:22

ya i checked it
but iam saying about start date.
i want start date as :

2011-12-31
means day as 31(31st).

lakshmi

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-04 : 15:43:51

That is the same as the end date that Visakh had posted, so you could use that.

Take a few minutes to study the queries below and you will see how easy it is to calculate the beginning or end of any month, quarter, or year. Once you get the hang of it, it is a question of just changing the numbers.

-- beginning of current month
SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE()),0);
-- beginning of current quarter
SELECT DATEADD(qq, DATEDIFF(qq,0,GETDATE()),0);
-- beginning of current year
SELECT DATEADD(yy, DATEDIFF(yy,0,GETDATE()),0);


-- beginning of the month 3 months ago
SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE())-3,0);
-- beginning of the quarter 3 quarters ago
SELECT DATEADD(qq, DATEDIFF(qq,0,GETDATE())-3,0);
-- beginning of the year 3 years ago
SELECT DATEADD(yy, DATEDIFF(yy,0,GETDATE())-3,0);


-- END of the month 3 months ago
SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE())-2,-1);
-- END of the quarter 3 quarters ago
SELECT DATEADD(qq, DATEDIFF(qq,0,GETDATE())-2,-1);
-- END of the year 3 years ago
SELECT DATEADD(yy, DATEDIFF(yy,0,GETDATE())-2,-1);

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-04 : 16:17:36

and here is a links which will help you in understanding basis of those queries

http://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lakshmi04
Starting Member

8 Posts

Posted - 2012-10-05 : 01:29:59

I was asking for the date function na.So I want the date function format for :

'2011-12-15 00:00:00.000' --- start date

and '2012-06-14 23:59:00.000' -- Q2 2012 (Quarter end date)

Please provide me solution for this..

thank you..

lakshmi

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-05 : 06:59:14

quote:
I was asking for the date function na.So I want the date function format for :

'2011-12-15 00:00:00.000' --- start date

and '2012-06-14 23:59:00.000' -- Q2 2012 (Quarter end date)

I am not 100% clear on what you are asking. Is the issue that you are running into a) the format of the input data, or b) how to calculate the beginning and end of a quarter automatically? Are you trying to create SSRS reports but provide parameters required automatically? Start Date in the example you posted - December 15, 2011 is not the start or end date of a quarter; so what is the rule for calculating that date?

lakshmi04
Starting Member

8 Posts

Posted - 2012-10-05 : 12:51:16

Ya ok..For now Iam working on Reports generation that is
Daily Reports ,Weekly Reports,Account Month end Reports , Account Month Quarter End Reports.

So in Sql scripts , dates are there manually.i.e every time we run Reports we should change dates accordingly based on given conditions.That is we are doing manually
So now I am changing that is Iam trying to take variables and storing dates in that variables;that is AccountyearDate,Sart Date,End Date like this.
So for one Report , I want '2011-12-15 00:00:00.000' --- as start date

and '2012-06-14 23:59:00.000' -- Q2 2012 (Quarter end date)

Here Q2 is nothing but every 3 months we will run Account Month Quarter Reports.So just naming as Q1,Q2,Q3,and Q4.

So please provide me the Exact date function for this Report.

Thank you..

lakshmi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-06 : 00:09:33

quote:
Originally posted by lakshmi04

Ya ok..For now Iam working on Reports generation that is
Daily Reports ,Weekly Reports,Account Month end Reports , Account Month Quarter End Reports.

So in Sql scripts , dates are there manually.i.e every time we run Reports we should change dates accordingly based on given conditions.That is we are doing manually
So now I am changing that is Iam trying to take variables and storing dates in that variables;that is AccountyearDate,Sart Date,End Date like this.
So for one Report , I want '2011-12-15 00:00:00.000' --- as start date

and '2012-06-14 23:59:00.000' -- Q2 2012 (Quarter end date)

Here Q2 is nothing but every 3 months we will run Account Month Quarter Reports.So just naming as Q1,Q2,Q3,and Q4.

So please provide me the Exact date function for this Report.

Thank you..

lakshmi

did you even bother to try what me and Sunita posted?
That exactly does what you're asking for

First try and then post why you think it doesnt work the way you want. also see link if you want to understand the underlying logic

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pakhireddy
Starting Member

2 Posts

Posted - 2012-10-06 : 11:30:23

hi
lakshmi...u can goto below link for more date related information
http://www.gcreddy.net/2010/03/sql-for-testers.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources