year date cast - 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

year date cast

Author

Topic

kshitizgp
Starting Member

31 Posts

Posted - 2012-02-07 : 01:45:24

i want to create a stored procedure for a annul report
am using radmonth year grid which gives me two(2) dates

start_Date - Jan 2012

end_Date - DEC 2102

I WANNA my query to display data for jan ,feb ,march ...dec based on start_Date and end_Date

how should i date diff my query and use it to get the data for each months ?

sureshkk
Starting Member

21 Posts

Posted - 2012-02-07 : 02:19:23

If u want to display detail data then select the month of date field and use that field in the reports group section and use where condition in the query.

eg:
select cast(month(datefiled) as varchar)+'-'+ cast(year(datefield) as varchar) as [Month] ,* from tablename
where datefield between '01-jan-2012' and '31-dec-2012'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-07 : 07:18:55

To generate a monthly calendar that starts from your start date and goes through the end date, you can use the master..spt_values (or a numbers table if you have one in your database). For example:

DECLARE @start_date  DATETIME, @end_date DATETIME;
SET @start_date = '20120101';
SET @end_date = '20121231';

SELECT
	DATEADD(MONTH,Number,@start_date)
FROM
	MASTER..spt_values
WHERE
	[Type] = 'P' 
	AND DATEADD(MONTH,Number,@start_date) < @end_date

That may be required if you don't have data for all the months in between the start and end date and you still want to display those months.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources