Solved

# DATENAME showing same date across all rows

Posted on 2006-05-22
726 Views
Why is DateName showing all January when I know it's not.  If I remove DateName, I get numbers for each month like 1,4,2,5 which should be converted to varying month names but they're all coming out 'January'

SELECT  DISTINCT m.customer,
c.name,
c.customer,
(SELECT Top 1 fd.Fee1 FROM FSch fd
where c.FSch = fd.code)
AS FSch,
m.Branch,
CASE WHEN ph.bgg = 'U' OR ph.btype = 'R' OR ph.bgg = 'P' OR ph.bgg = 'D' Then
(-ph.tpaid + ph.oFee)
ELSE
(ph.tpaid - ph.oFee)
END AS [Amount],
DATENAME(m, ph.systemmonth) as systemmonth,
ph.systemyear
.....
0
Question by:dba123

LVL 28

Accepted Solution

Looks like your systemmonth only contains the numeric month value and not a whole date value.  If this is the case, you can do the following:

SELECT  DISTINCT m.customer,
c.name,
c.customer,
(SELECT Top 1 fd.Fee1 FROM FSch fd
where c.FSch = fd.code)
AS FSch,
m.Branch,
CASE WHEN ph.bgg = 'U' OR ph.btype = 'R' OR ph.bgg = 'P' OR ph.bgg = 'D' Then
(-ph.tpaid + ph.oFee)
ELSE
(ph.tpaid - ph.oFee)
END AS [Amount],
DATENAME(m, CAST(CAST(ph.systemmonth AS VARCHAR(2)) + '/01/2006' AS DATETIME)) as systemmonth,
ph.systemyear
0

LVL 50

Expert Comment

SELECT  DISTINCT m.customer,
c.name,
c.customer,
(SELECT Top 1 fd.Fee1 FROM FSch fd
where c.FSch = fd.code)
AS FSch,
m.Branch,
CASE WHEN ph.bgg in  ('U' ,'R', 'P',= 'D') Then
(-ph.tpaid + ph.oFee)
ELSE
(ph.tpaid - ph.oFee)
END AS [Amount],
DATENAME(m, '2006'+right('0'+convert(varchar(2),ph.systemmonth)+'01',2)) as systemmonth,
ph.systemyear
.....
0

LVL 1

Author Comment

lowfatspread..that now shows June all the way down...
0

LVL 1

Author Comment

>>>Looks like your systemmonth only contains the numeric month value and not a whole date value

yes, that's what I was saying..correct
0

LVL 50

Expert Comment

sorry

DATENAME(m, '2006'+right('0'+convert(varchar(2),ph.systemmonth),2)+'01') as systemmonth,
0

## Featured Post

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.