sql date format - 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

sql date format

Author

Topic

craigmacca
Posting Yak Master

142 Posts

Posted - 2009-01-11 : 11:23:39

Hi i need to pass 2 ints to another stored proc

@month int
@year int

i have a date variable @NewDate which i need to get the month and year from.

i am not sure how to do this

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-01-11 : 11:43:34

set @month = datepart(month,@NewDate);
set @year = datepart(year, @NewDate);

So, you could call your stored procedure using:

exec yourStoredProc @month = datepart(month,@NewDate), @year = datepart(year, @NewDate);

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-11 : 11:45:18

set @month = Month(@NewDate);
set @year = YEAR(@NewDate);

craigmacca
Posting Yak Master

142 Posts

Posted - 2009-01-11 : 12:46:05

ok thanks that worked, just one more thing, i have this query

SELECT sum(SmsAmountSent) as rcount, Month
FROM dbo.Sms_Archive_Month
WHERE SmsYear = 2009
GROUP BY Month

which returns

3000, 1

what i need is to return every month with rcount = 0 ie

3000, 1
0, 2
0, 3
0, 4
0, 5
0, 6
0, 7
0, 8
0, 9
0, 10
0, 11
0, 12

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-11 : 12:51:59

quote:
Originally posted by craigmacca

ok thanks that worked, just one more thing, i have this query

SELECT sum(SmsAmountSent) as rcount, Month
FROM dbo.Sms_Archive_Month
WHERE SmsYear = 2009
GROUP BY Month

which returns

3000, 1

what i need is to return every month with rcount = 0 ie

3000, 1
0, 2
0, 3
0, 4
0, 5
0, 6
0, 7
0, 8
0, 9
0, 10
0, 11
0, 12

for that you need a tally table. something like


SELECT d.Month,COALESCE(rcount,0) AS countvalue
FROM
(
SELECT number AS Month
FROM master..spt_values 
WHERE type='p'
AND number BETWEEN 1 AND 12
)m
LEFT JOIN 
(
SELECT sum(SmsAmountSent) as rcount, Month
FROM dbo.Sms_Archive_Month
WHERE SmsYear = 2009
GROUP BY Month
)d
ON d. Month=m.Month

master..spt_values is an internal count table of sql server table which i've used for purpose

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-11 : 13:06:11

If you have month column with entries from 1-12

Select t.Month,coalesce(rcount,0)as rcount
from
(select distinct Month from dbo.Sms_Archive_Month)t
left outer join
(SELECT Sum(SmsAmountSent) as rcount, Month
FROM dbo.Sms_Archive_Month
WHERE SmsYear = 2009
GROUP BY Month
)m
on t.Month = m.Month

craigmacca
Posting Yak Master

142 Posts

Posted - 2009-01-11 : 13:26:11

thanks visakh16

i have tried your surgestion, which works but i need the month column to show 1 - 12 currently it shows

3000, 1
0, NULL
0, NULL
0, NULL
0, NULL
0, NULL
0, NULL
0, NULL
0, NULL
0, NULL
0, NULL
0, NULL

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-11 : 13:33:51

Did you use coalesce function as visakh suggested?

craigmacca
Posting Yak Master

142 Posts

Posted - 2009-01-11 : 13:53:05

yes i copied it totally

but the month column shows Null values

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-11 : 14:08:42

quote:
Originally posted by visakh16

quote:
Originally posted by craigmacca

ok thanks that worked, just one more thing, i have this query

SELECT sum(SmsAmountSent) as rcount, Month
FROM dbo.Sms_Archive_Month
WHERE SmsYear = 2009
GROUP BY Month

which returns

3000, 1

what i need is to return every month with rcount = 0 ie

3000, 1
0, 2
0, 3
0, 4
0, 5
0, 6
0, 7
0, 8
0, 9
0, 10
0, 11
0, 12

for that you need a tally table. something like

SELECT m.Month,COALESCE(rcount,0) AS countvalue
FROM
(
SELECT number AS Month
FROM master..spt_values
WHERE type='p'
AND number BETWEEN 1 AND 12
)m
LEFT JOIN
(
SELECT sum(SmsAmountSent) as rcount, Month
FROM dbo.Sms_Archive_Month
WHERE SmsYear = 2009
GROUP BY Month
)d
ON d. Month=m.Month

master..spt_values is an internal count table of sql server table which i've used for purpose

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 02:17:10

ah...i had alias wrong
thanks for catch Sodeep

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources