SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 regarding date function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lakshmi04
Starting Member

India
8 Posts

Posted - 10/03/2012 :  13:55:33  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 10/03/2012 :  14:03:02  Show Profile  Reply with Quote
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/

Go to Top of Page

lakshmi04
Starting Member

India
8 Posts

Posted - 10/04/2012 :  03:52:21  Show Profile  Reply with Quote
thanku for the solution
but it is not working..


lakshmi
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/04/2012 :  10:35:28  Show Profile  Reply with Quote
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/

Go to Top of Page

lakshmi04
Starting Member

India
8 Posts

Posted - 10/04/2012 :  14:08:24  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/04/2012 :  15:11:57  Show Profile  Reply with Quote
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/

Go to Top of Page

lakshmi04
Starting Member

India
8 Posts

Posted - 10/04/2012 :  15:23:22  Show Profile  Reply with Quote
ya i checked it
but iam saying about start date.
i want start date as :


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



lakshmi
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/04/2012 :  15:43:51  Show Profile  Reply with Quote
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);
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/04/2012 :  16:17:36  Show Profile  Reply with Quote
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/

Go to Top of Page

lakshmi04
Starting Member

India
8 Posts

Posted - 10/05/2012 :  01:29:59  Show Profile  Reply with 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)

Please provide me solution for this..

thank you..

lakshmi
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/05/2012 :  06:59:14  Show Profile  Reply with Quote
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?
Go to Top of Page

lakshmi04
Starting Member

India
8 Posts

Posted - 10/05/2012 :  12:51:16  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/06/2012 :  00:09:33  Show Profile  Reply with Quote
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/

Go to Top of Page

pakhireddy
Starting Member

India
2 Posts

Posted - 10/06/2012 :  11:30:23  Show Profile  Reply with Quote
hi
lakshmi...u can goto below link for more date related information
http://www.gcreddy.net/2010/03/sql-for-testers.html
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000