count records using a max value in another field

Please help. I am struggling.

table

cards year org

Q123 Q MO

Q236 Q MO

Q569 Q MO

R456 R MO

Q789 Q MO

R482 R MO

First I need to find the max value of the year field then use that max value to count the cards associated with that max value where org is a parameter from the user.

So the result would be 2. I need a SQL query to give me these results.

R year is the max value and there are two of those records. I hope this makes sense.

Something like this (works in SQL 2000 and SQL 2005):

Code Snippet

SELECT TOP 1

Year,

Count = count( Year )

FROM MyTable

GROUP BY Year

ORDER BY Year DESC

|||

Code Snippet

SELECT max(activeyear)AS activeyear, COUNT(cardnum) AS cardCnt
FROM camcards

WHERE org = @.org)

This is my original query but it gives me the count for all the years regardless of my max(year). The user would be entering the value in for org. I should get back one line so there is not need to to have an order by.

so in my original message the result should be

activeyear = R and cardCnt = 2

I am sorry Arnie, but your suggestion does not work or make sense to me. I don't think you can put an equal sign in the SELECT part of the statement.

|||

Try:

select a.year, count(*)

from dbo.t1 as a inner join (select c.org, max(c.year) from dbo.t1 as c where c.org = @.org group by org) as b

on a.org = b.org and a.year = b.year

group by a.year

go

select year, count(*)

from dbo.t1

where org = @.org and year = (select max(b.year) from dbo.t1 as b where org = @.org)

order by year

go

select top 1 year, count(*)

from dbo.t1

where org = @.org

gropu by year

order by year desc

go

AMB

|||

Select top 1 year, (SELECT count(1) from table where year = t.yearr and org = t.org)

from table t

where org = 'mo'

order by year desc

|||

Actually, Theresa, the code I provided you does indeed work. I understand that you my not yet have the exprience to follow how it works, and I'll try to help. I've even added the extra WHERE clause to filter for the Org by user input.

Below is demonstration code that creates a sample table (named @.MyTable), and then populates the table with your data (I tossed in a couple of extra rows to verify that they would not be included in the result.) I suggest that you copy and paste code into your query window and execute it to verify that it works, THEN change the code, substituting the table name for your tablename.

I don't think you can put an equal sign in the SELECT part of the statement.

You are a little 'confused' about using equals signs in a SELECT statement, and that is just from inexperience. Actually, there are a couple of ways that one may choose to use equals signs in the SELECT. First, to assign a column value to a variable (e.g., SELECT @.ClientID = ClientID FROM ... ), and also to assign an 'alias' for a 'missing' column name or to rename a column in the resultset (e.g, Count = count( Year ) ). Without the 'alias', the column representing the aggragate value COUNT() would not have a name. That is often OK, and sometimes for illustration purposes, it is useful.

At the bottom is the actual results from executing all of this sample code in my query window. I think that matches what you said that you wanted.

As you progress in your learning SQL Server, you will discover that it is considered a big faux paux to use reserved words as table and column names (e.g., Year). When you do that you may 'confuse' matters, so to prevent such 'confusion', it is necessary to use square brackets ( [ ] ) around those inappropiately named objects. You may wish to refer to Books Online, Topic: Reserved Words.

I hope that this helps you, and good luck with your travels down the SQL Server 'path'.

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
Cards varchar(10),
[Year] char(1),
Org char(2)
)

INSERT INTO @.MyTable VALUES ( 'Q123', 'Q', 'MO' )
INSERT INTO @.MyTable VALUES ( 'Q236', 'Q', 'MO' )
INSERT INTO @.MyTable VALUES ( 'Q569', 'Q', 'MO' )
INSERT INTO @.MyTable VALUES ( 'R456', 'R', 'MO' )
INSERT INTO @.MyTable VALUES ( 'Q789', 'Q', 'MO' )
INSERT INTO @.MyTable VALUES ( 'R482', 'R', 'MO' )
INSERT INTO @.MyTable VALUES ( 'ABC1', 'S', 'NO' )
INSERT INTO @.MyTable VALUES ( 'XYZ2', 'T', 'NO' )

DECLARE @.Org char(2)
SET @.Org = 'MO'

SELECT TOP 1
ActiveYear = [Year],
CardCnt = count( [Year] )
FROM @.MyTable
WHERE Org = @.Org
GROUP BY [Year]
ORDER BY [Year] DESC

ActiveYear CardCnt
- --
R 2

|||Thanks to all who have replied to thread.

Count of Conditonal Formatting

Hi,

I have a report where conditional formatting is applied based on being compared to a fixed value in a second dataset. There are several columns where this is applied, and I would like to be able to count all where the condition is true (ie >= corresponding column in dataset2, background green) and all that are false (ie < dataset2, background red) and use that data in the same report and create a percentage.

Not sure whether the reporting layer is the place to do this, or whether to create a separate table and populate that with 1's and 0's beforehand and then use that data additionally in the report?

Many thanks

The usual way to do this is to use SUM() instead of COUNT(), like this:

Code Snippet

SUM(IIF(<condition is true>,1,0))

HTH,

>L<

|||

Hi,

For backgroud coloring, add to this to the column BackgroundColor property:

Code Snippet

=iif(<condition> = 0, "Green", "Red")

|||

Have a look at Brian Welcker's Blog for his post on Custom Aggregates:

http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

In the Code block (available from the Report Properties dialog), you would add:

Dim orderIDs As System.Collections.Hashtable
Dim total As Double

Function MyFunc(ByVal orderID As Object, ByVal freight As Obect) As Double
If (orderIDs Is Nothing) Then
orderIDs = New System.Collections.Hashtable
End If
If (orderID Is Nothing) Then
MyFunc = total
Else
If (Not orderIDs.Contains(orderID)) Then
total = total + freight
orderIDs.Add(orderID, freight)
End If
MyFunc = total
End If
End Function

In your report, you add a hidden textbox with the value expression to compute the value:

=Sum(Code.MyFunc(Fields!OrderID.Value, Fields!Freight.Value))

In the footer of the table, you add a textbox with the value expression:

=Code.MyFunc(Nothing, Fields!Freight.Value)

to return the total value.

I believe it provides the functionality you need.

Larry

|||

Thanks, I think this will work within a column, therefore I get a total at the bottom of each column.

I also need a total at the end of each row (crosstab style), but obviously each column has a separate textbox and separate conditional formatting expression. It still boils down to whether that condition is true or false - do you know if this is possile or is it better suited to the database level?

eg for all condition true is >= value in dataset 2

dataset2 - baseline comparison table

col1, col2, col3

40,2,0

dataset1 - data

col1, col2, col3

20,2,4

45,0,0

what i'd like is to be able to include on the report that 2 true conditions were met and also include the actual data

col1, col2, col3, SumTrue

20,2,4,2

45,0,0,2

Appreciate everyone's help so far - i've got the formatting (ie red, green) working - just wondering if the sum of conditions across columns will work

|||

Actually I think it will still work, and without any exciting code. but possibly I'm not clear from your examples what your SumTrue column means, because it says "2" on each of the rows and I only see 1 match in each row against your baseline table?

>L<

|||

Hi..

The condition for true in this case is where the result is >= to the comparison table, in the example there are 2 on each row that are >= to the comparison table.

I have just got back to looking at this again and tried the following in the group footer of one of the columns

=Sum(IIF(Fields!col1.Value >= First(Fields!col1.Value, "BaseTable"),1,0))

and get an error saying aggregate functions cannot be nested in other aggregate functions.

|||

>>the result is >= to the comparison table

OIC. I thought it had to be = to be true. I apologize, I didn't see the > in your original post, although I see now that you said so! I will have to repro this to test, which is why I am noticing the > now . Hang on, doing that...

Well, it does work, as I said, to do the thing you asked about ( "adding across").

IOW, assuming DataSet2 is your real values and DataSet1 is the baseline, this works fine to get your fourth column across, as I expected:

Code Snippet

=IIF(Fields!col1.Value >= First(Fields!col1.Value, "DataSet1"),1,0) +

IIF(Fields!col2.Value >= First(Fields!col2.Value, "DataSet1"),1,0) +

IIF(Fields!col3.Value >= First(Fields!col3.Value, "DataSet1"),1,0)

For your group footer, though, you would have to do the custom aggregate bit.

That really seems like the long way around, though. Are you absolutely sure you shouldn't be doing this at the data level?

To illustrate: I did this to represent your the baseline data in my repro:

Code Snippet

SELECT 40 AS col1, 2 AS col2, 0 AS col3

and then this to represent the real data:

Code Snippet

SELECT 20 AS col1,2 AS col2,4 AS col3
UNION
SELECT 45 AS col1,0 AS col2, 0 AS col3

Ignoring the UNION, for a moment, because it was just there to give me two dummy rows in the real data, wouldn't we be better off if we did the following at the SQL level? What we're after is a Cartesian join with the one-row baseline data, which you can get with any dummy-true condition; I used 1=1 here:

Code Snippet

SELECT col1, col2, col3, basecol1, basecol2,basecol3,
CASE WHEN col1 >= basecol1 THEN 1 ELSE 0 END AS col1Comp,
CASE WHEN col2 >= basecol2 THEN 1 ELSE 0 END AS col2Comp,
CASE WHEN col3 >= basecol3 THEN 1 ELSE 0 END AS col3Comp
FROM (SELECT 20 AS col1,2 AS col2,4 AS col3
UNION
SELECT 45 AS col1,0 AS col2, 0 AS col3) real
JOIN (SELECT 40 AS basecol1, 2 AS basecol2, 0 AS basecol3) baseline
ON 1= 1

(Again just treat the UNIONed set as a placeholder for your real data and the literals on the right side of the join as a placeholder for however you get your single row of baseline data. )

Now you have everything you need in one dataset.

I added the col1Comp, col2Comp, and col3Comp columns just to make life easier on the reporting end, you really could do without them and do the >= comparisons against col1 versus basecol1 etc, because you would no longer be nesting aggregates. I included the three calculated columns because I don't see any reason to do any of this work on the reporting level, frankly...

For colors you could then write simple expressions like

=IIF(Fields!col1Comp.Value=1,"Green","Red")

Going across, you would only need to write this:

=Fields!col1Comp.Value + Fields!col2Comp.Value + Fields!col3Comp.Value

Going down is a simple =Sum(Fields1!col1Comp.Value) and of course the bottom right is a simple sum of the addition you see above.

>L<

|||

Absolutely spot on !! Works a treat.

Appreciate your help on this.

Thanks,

Mike

|||

Yea verily, data is a beautiful thing <g>.

Enjoy,

>L<

Count of Columns <> 0

How would you count the number of columns with a value not equal to 0 for
each row in a table?

Thanks!

Joe"Joe User" <joe@.user.com> wrote in message
news:c4s5vp$6je$1@.tribune.mayo.edu...
> How would you count the number of columns with a value not equal to 0 for
> each row in a table?
> Thanks!
> Joe

Here's one way:

select PrimaryKeyColumn,
case when col1 = 0 then 0 else 1 end +
case when col2 = 0 then 0 else 1 end +
case when col3 = 0 then 0 else 1 end +
...
case when coln = 0 then 0 else 1 end as 'NonZeroColumns'
from
dbo.MyTable

Simon|||Excellent!

Thanks!

Next question....
How does someone relatively new to tsql learn this sort of thing?

TIA

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:4071a1c0$1_3@.news.bluewin.ch...
> "Joe User" <joe@.user.com> wrote in message
> news:c4s5vp$6je$1@.tribune.mayo.edu...
> > How would you count the number of columns with a value not equal to 0
for
> > each row in a table?
> > Thanks!
> > Joe
> Here's one way:
> select PrimaryKeyColumn,
> case when col1 = 0 then 0 else 1 end +
> case when col2 = 0 then 0 else 1 end +
> case when col3 = 0 then 0 else 1 end +
> ...
> case when coln = 0 then 0 else 1 end as 'NonZeroColumns'
> from
> dbo.MyTable
>
> Simon|||"Joe User" <joe@.user.com> wrote in message
news:c4sa4g$c6p$1@.tribune.mayo.edu...
> Excellent!
> Thanks!
> Next question....
> How does someone relatively new to tsql learn this sort of thing?
> TIA

<snip
Get a good book or two - there are some suggestions here:

http://vyaskn.tripod.com/sqlbooks.htm

But don't forget Books Online itself - it's very helpful to read through the
TSQL reference part. I don't mean read every word (unless you have a lot of
time on your hands...), but it helps to have an idea of what's available in
the language. Even if you only vaguely remember what a keyword does, or if
you only remember the name, you can always look it up. The list of functions
is another useful page to review, for the same reason. The
SELECT/INSERT/UPDATE/DELETE entries are very important, as are the CREATE
XXXX entries - all of them are linked to lots of related information, so you
can go into as much detail as you want.

Simon|||>> How would you count the number of columns with a value not equal to
0 for each row in a table? <<

SELECT keycol,
ABS(SIGN(col1)) +
ABS(SIGN(col2)) +
ABS(SIGN(col3)) + .. AS non_zero_tally
FROM Foobar;

Count Occurances Of Given Value

Hello All,

I have a question that has been vexing me for some time now. It keeps coming up when I'm trying to write queries for SSRS reports. Lets say I have a table that has 3 columns to keep track of people's gender in a annonomys survay (very basic example):

Month (varchar) | Year (smallint) | Gender (bool)

I want to return a dataset that is grouped by Month and Year and that contains a count of each Gender which would look something like this:

Month | Year | [Male Count] | [Female Count]

January | 2006 | 100 | 120
February | 2006 | 130 | 110
March | 2006 | 120 | 145
April | 2006 | 105 | 125

How would I acheive a dataset like this? Is it possible? Do I need to join the table to itself? If so do I use an Inner Join, an Outer Join, or a Left/Right Join? Any help would be extremely appreciated.

Thanks!

Tennyson

There are several possibilities. One option:

SELECT [Month], [Year],
COUNT(CASE WHEN Gender = 0 THEN 1 END) AS MaleCount,
COUNT(CASE WHEN Gender = 1 THEN 1 END) AS FemaleCount
FROM YourSurveyTable

GROUP BY [Month], [Year]

-Sue

|||

Thank you Sue! Your help was much appreciated.

Thanks,

Tennyson

Count Occurances Of Given Value

Hello All,

I have a question that has been vexing me for some time now. It keeps coming up when I'm trying to write queries for SSRS reports. Lets say I have a table that has 3 columns to keep track of people's gender in a annonomys survay (very basic example):

Month (varchar) | Year (smallint) | Gender (bool)

I want to return a dataset that is grouped by Month and Year and that contains a count of each Gender which would look something like this:

Month | Year | [Male Count] | [Female Count]

January | 2006 | 100 | 120
February | 2006 | 130 | 110
March | 2006 | 120 | 145
April | 2006 | 105 | 125

How would I acheive a dataset like this? Is it possible? Do I need to join the table to itself? If so do I use an Inner Join, an Outer Join, or a Left/Right Join? Any help would be extremely appreciated.

Thanks!

Tennyson

There are several possibilities. One option:

SELECT [Month], [Year],
COUNT(CASE WHEN Gender = 0 THEN 1 END) AS MaleCount,
COUNT(CASE WHEN Gender = 1 THEN 1 END) AS FemaleCount
FROM YourSurveyTable

GROUP BY [Month], [Year]

-Sue

|||

Thank you Sue! Your help was much appreciated.

Thanks,

Tennyson

Count Invisible ReportItems in Page Footer?

I have a textbox in my page footer with an expression like this: Sum(iif(
(ReportItems!textbox56.Value = "Complete"),1,0)). Basically it counts how
many times "Complete" comes up in a page. I also have a parameter which can
filter out any "Complete" values using the visibility of the detail row. My
problem is that I still want a count of the Complete values, even if they are
filtered out on the page. When I run the report with no filters, it shows me
exactly how many Completes there are per page. However, it will not count
the Completes when they are filtered out by page. How can I get the page
footer to count them even when they are hidden?Sorry, this is not supported in RS 2000. The good news is that this will
change in RS 2005 and conditionally (expression-based) hidden items won't be
taken into account in page header/footer calculations.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"dachrist" <dachrist@.discussions.microsoft.com> wrote in message
news:D32037E2-4E92-4BB0-B622-09794DEAF21F@.microsoft.com...
>I have a textbox in my page footer with an expression like this: Sum(iif(
> (ReportItems!textbox56.Value = "Complete"),1,0)). Basically it counts how
> many times "Complete" comes up in a page. I also have a parameter which
> can
> filter out any "Complete" values using the visibility of the detail row.
> My
> problem is that I still want a count of the Complete values, even if they
> are
> filtered out on the page. When I run the report with no filters, it shows
> me
> exactly how many Completes there are per page. However, it will not count
> the Completes when they are filtered out by page. How can I get the page
> footer to count them even when they are hidden?|||Though in simple terms its not possible. But there is work around-
Make custom dll which has a method which takes filter expression as input
and connects to same datasource using ado.net and fetches the same records.
Then calculate no of "complete"s in table and return the value to report.
Please see using custom assemblies in Reporting Services. Its very powerful!
Suneet Moha
-----
"dachrist" wrote:
> I have a textbox in my page footer with an expression like this: Sum(iif(
> (ReportItems!textbox56.Value = "Complete"),1,0)). Basically it counts how
> many times "Complete" comes up in a page. I also have a parameter which can
> filter out any "Complete" values using the visibility of the detail row. My
> problem is that I still want a count of the Complete values, even if they are
> filtered out on the page. When I run the report with no filters, it shows me
> exactly how many Completes there are per page. However, it will not count
> the Completes when they are filtered out by page. How can I get the page
> footer to count them even when they are hidden?

Count All Things Happened Today

Ive got a table of notes people have created, with a field called "timecreated" which has a default value of "GETDATE()" Im trying to write an SQL statement that will count up all of the notes that people have created today/ yesturday etc. i could do it if the timecreated value was a "short date string" styled date, but its set up like : 11/11/2007 18:51:46 is there way of converting it before counting? if theres a simple way of doing this i would appricate any help thanks John

check out the year, month and day or datepart functions in t-sql.

You can assemble the date anyway you want.

|||

forgot to add:

SET DATEFORMAT

CONVERT

|||

i found this : http://support.microsoft.com/kb/q186265/

which helps me get stuff like secounds etc :

 Ms for Milliseconds
 Yy for Year
 Qq for Quarter of the Year
 Mm for Month
 Dy for the Day of the Year
 Dd for Day of the Month
 Wk for Week
 Dw for the Day of the Week
 Hh for Hour
 Mi for Minute
 Ss for Second

how do i get it as a short date string, like "11/10/2007" please John

|||

Try this:

WHERE timecreated>=DATEADD(dd,DATEDIFF(dd, 0,GETDATE()), 0)AND timecreated<DATEADD(dd,DATEDIFF(dd, 0,GETDATE()), 1)

|||

that worked perfectly, is it "read" by like this :

WHERE TimeCreated is equalto or greater than today + 0 days. AND TimeCreated is LessThan today + 1day.

just wondering becuase i wish to adjust this code to count things happening this week / month etc.

Thanks John

|||

Both DATEADD and DATEDIFF are very useful functions to work with datetime in TSQL. You can search to find more information on these two key words. You can modify the code with the same logic to get what you want, but i have read some good articles with a lot usages for these two functions.

|||

yeh they seem to be invalueable when attempting to work with dates / times! thanks for this! John

count 2 value in a table

hi all pls kindly help
need to know how to do a count for 2 value in a column

example :
my table(entries)
has the field name 'selection'
under this selection, data inside have is e.g(apple,apple,pear,pear,pear,orange)
i need to do a COUNT on how many apple and pear there is inside this table

but i seriously have no idea, pls kindly help thanksThis sounds like homework from an "Introduction to databases" course, but I'm willing to give you the benefit of the doubt...SELECT Count(*), selection
FROM entries
GROUP BY selection
ORDER BY Count(*) DESC -- gratis-PatP|||SELECT Count(*), selection
FROM entries
GROUP BY selection
ORDER BY Count(*) DESC -- gratis

sorry for my ignorance..
wat u meant by DESC over here??

wat i want is to count how many pears and apple there is in that table but not orange|||DESC means descending, and you don't have to use it if you don't want to

did you want the total of apples and pears? that's different --

SELECT count(*) as applesandpears
FROM entries
WHERE selection in ('apple','pear')|||thanks so much...
really so basic

need to read up my books again :(|||Sorry, my background is showing :rolleyes:

Gratis is a Latin vulgate term that means "free of charge" or "complimentary". When I used it in a comment, I meant that I expected it to make things easier to use, without actually impacting the solution at all.

As Rudy pointed out, in this case it simply orders the result set so that the most frequently occuring items appear at the top of the list. You can safely ignore it if it doesn't help you.

-PatP|||ok got it ..
but still appreciated
:)

Count / SysIndex

Hi,
I have a problem in that when I perform a "Select Count(*) from table1" I
receive a different value than "SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table1') AND indid < 2".
Is this to be expected? Can anyone help?
Thanks,
JoeJoe
The system sysindex table can contain let say it incorrect info about rows
count .
Run update statistcs and see if result is correct.
If you want to know how many rows your table has , then rin select count(*)
from table
Also look at mu example
CREATE TABLE TT
(
COL INT NULL
)
INSERT INTO TT VALUES (NULL)
INSERT INTO TT VALUES (1)
SELECT COUNT(COL) FROM TT
What do you think how many rows will return this statement?
"Joe Doherty" <joeydocherty2404@.hotmail.com> wrote in message
news:OPTPY9rzDHA.3116@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have a problem in that when I perform a "Select Count(*) from table1" I
> receive a different value than "SELECT rows FROM sysindexes WHERE id => OBJECT_ID('table1') AND indid < 2".
> Is this to be expected? Can anyone help?
> Thanks,
> Joe
>|||Sysindexes is only updated at checkpoint. Never trust systindexes as it is
an approximation since SQL 7.0.
This was done for performance reasons.
Cheers
--
--
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.NOSPAMepprecht.net
Specialist SQL Server Solutions and Consulting
"Joe Doherty" <joeydocherty2404@.hotmail.com> wrote in message
news:OPTPY9rzDHA.3116@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have a problem in that when I perform a "Select Count(*) from table1" I
> receive a different value than "SELECT rows FROM sysindexes WHERE id => OBJECT_ID('table1') AND indid < 2".
> Is this to be expected? Can anyone help?
> Thanks,
> Joe
>|||Hi Joe,
run "DBCC UPDATEUSAGE ... WITH COUNT_ROWS" on the database.
This will report and corrects inaccuracies in the sysindexes table, which may
result in incorrect values returned by sysindexes table.
But it is always safer to use count(*) rather than leaning on sysindexes
table.
-- Vishal

Could I Resolve a KPI's Data Value in a SSIS Script Task?

Hi, thanks.

I could rosolve a KPI's Data Value by ADOMD.net from any .net application. Now I want to do the same thing from the SSIS Script Task. Could I do that?

SSIS Script Task use a VBA Script. I could use ADO.net in it, by imports the XML.dll.

Thanks.

ivanchain wrote:

Hi, thanks.

I could rosolve a KPI's Data Value by ADOMD.net from any .net application. Now I want to do the same thing from the SSIS Script Task. Could I do that?

SSIS Script Task use a VBA Script. I could use ADO.net in it, by imports the XML.dll.

Thanks.

Yep. If its .Net then you can use it in a Script Task!

-Jamie

|||

But How? I don't know how to use any ADOMD.NET functions in SSIS Script Task. Because in the common VS, we need to imports the ADOMD.NET.dll into the projects if we want to use it. But in SSIS Script Task's imports list, I cound not find the ADOMD.NET.dll.

Anyone know this? Thanks!

|||

ivanchain wrote:

But How? I don't know how to use any ADOMD.NET functions in SSIS Script Task. Because in the common VS, we need to imports the ADOMD.NET.dll into the projects if we want to use it. But in SSIS Script Task's imports list, I cound not find the ADOMD.NET.dll.

Anyone know this? Thanks!

Aha. Yes, you're absolutely right. I should have realised this before - sorry.

Read this:

VSA requires DLLs to be in the Microsoft.Net folder (but not all the time)
(http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2341.aspx)

-Jamie

|||Thanks, Jamie. You saved my life.

Corrupted FormatDateTime Results

Using SQL Server 2005 Developer Edition and reporting services.

My report pulls a datetime field from the database, for example holding a value of 9/9/2006 12:00:00. It places this field by itself in a textbox.

I want it to display as long date (Saturday, September 09, 2006) so I set the format of the textbox to:

=FormatDateTime(Fields!Contest_Dt.Value , 1 )

This displays the results quite oddly as:

SaAur9a6, SepAe0ber 09, 2006.

Any ideas as to what is going on here and how to fix it?

Thanks in advance!

When I try this, it works as expected. Are you sure the value in the database is stored as a datetime value not as a string?

Can you also try this expression: =FormatDateTime(CDate(Fields!Contest_Dt.Value, 1))

-- Robert