Year only data from daily dates

Try:
select distinct
Year (obsDate) as 'Year'
from
tblObs
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Web learner" <beginner@.learning.edu> wrote in message
news:OSTG4DFWGHA.3492@.TK2MSFTNGP05.phx.gbl...
I have daily data from year 1995 to 2004. The following query
SELECT [obsDate] FROM [tblObs]
returns 3653 datevalue records for each day.
1995-01-01 00:00:00
1995-01-02 00:00:00
1995-01-03 00:00:00
--
--
2004-12-29 00:00:00
2004-12-30 00:00:00
2004-12-31 00:00:00
I just want a recordset having years. That means with only 10 records as
follow.
1995
1996
1997
--
--
2004
What should be the SQL query?
I do not know anything about objectDataSource of ADO.NET. Is it worthwhile
or better for this kind of problem?
Thanks for reading this ...
Regds,
Web learner
Yes, it worked. This is what I wanted for the moment.
Thanks.....
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ufjVPHFWGHA.4132@.TK2MSFTNGP04.phx.gbl...
> Try:
> select distinct
> Year (obsDate) as 'Year'
> from
> tblObs
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Web learner" <beginner@.learning.edu> wrote in message
> news:OSTG4DFWGHA.3492@.TK2MSFTNGP05.phx.gbl...
> I have daily data from year 1995 to 2004. The following query
> SELECT [obsDate] FROM [tblObs]
> returns 3653 datevalue records for each day.
> 1995-01-01 00:00:00
> 1995-01-02 00:00:00
> 1995-01-03 00:00:00
> --
> --
> 2004-12-29 00:00:00
> 2004-12-30 00:00:00
> 2004-12-31 00:00:00
> I just want a recordset having years. That means with only 10 records as
> follow.
> 1995
> 1996
> 1997
> --
> --
> 2004
> What should be the SQL query?
> I do not know anything about objectDataSource of ADO.NET. Is it worthwhile
> or better for this kind of problem?
> Thanks for reading this ...
> Regds,
> Web learner
>

Year function in sql query

What's the error you're getting?

Error source:Microsoft Jet Database Engine

Error Message:Undefined function '[Year]' in expression

please note it adds [ ] Brakets to year

Than that's the problem: the adding of brackets to "Year" makes it a column identifier instead of a function name.

Are you passing that SQL statement to Access via a OleDbCommand object? And if so, can you please paste that code here?

The sql statement is as I stated earlier

Year([Created_Date]) in the query grid

But you said, "please note it adds [ ] Brakets to year"

That means it looks like this: [Year]([Created_Date])

Or like this: [Year([Created_Date])]

Correct? Or does it add the brackets ONLY to the error message and NOT to the query itself?

That means it looks like this: [Year]([Created_Date]

It adds it to query grid and error message as well.

Than that's the problem: the adding of brackets to "Year" makes it a column identifier instead of a function name.

So the question is why does it added and cannot evaluate

Year([Created_Date])

I just try it on the same table in access and it works well

Are you passing that SQL statement to Access via a OleDbCommand object? And if so, can you please paste that code here?
I have the same problem. Did you find a solution?
To work around this bug I'm going to make the query in the access mdb file that calculates and lists in columbs the Year, Month and Day. Then I'll set the SQLDataSource to use that view of the data instead of the table directly. However, there should be a way to use functions inside of the query designer build into the express.

Year portion of date is incorrect

Never mind...I figured it out. Their was a problem between my client code and my pl/sql code.

Yes I have no Money

Use String.Format() to format the output. If you are in a DataGrid, you can
use Format() as well to make sure it displays correctly.
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Bob" wrote:
> Hello:
> VB.NET 2003
> Windows App
> SQL 2k Backend
> Typed DataSet
> I have a bound TextBox to a Money column in SQL Server 2k. When the dataset
> loads, it shows 4 decimals. The data type in the schema designer is Decimal.
> I realy don't care which type I use but I realy do need it to show 2 decimal
> places even if the value is 5.20. I created a user but the data coming from
> the DB is still giving me 4 decimals. I tried changing the data type but
> that gives me 5.2 for 5.20.
> Can someone point me to a good article. I've already seen soem on code
> project and a few other places but none of them are designed to work with
> databound. I can't believe .NET doesn't have that setting in the properties
> window anymore. more and more I use this, it seems always something lost
> from VB6
> TIA
> Bob
>
>
Cowboy:
It's progress.
I tired this
FormatCurrency(TextBox1.Text, 2, TriState.False, TriState.True,
TriState.True)
The error is Cast from string to type double is not valid
TIA
Bob

Yes/No Field Problem

Yes is -1 in Access, although True (without quotes etc) should also work. As indeed should Yes.

EDIT

Another thing - you should put the field names in [ ] brackets when performing a VBA function on them:

SELECT CAMERA_ID, RTRIM([MAKE]) + ' ' + RTRIM([MODEL]) AS CAMERA, ENABLED
FROM CAMERAS
WHERE (ENABLED = -1)
ORDER BY CAMERA

Thanks for your help.

After much messing I realised the error was caused by the ORDER BY statement. Got that sorted and it all worked.

Yes/No field in Stored Procedure (Query)

hi
look at the code below,In command.parameter.add parametername and datatype has to be specified,which is missing in your code.Also mention the direction.in store procedure side give properly the two input parameter.
OleDbCommand cmdGetPointer = new OleDbCommand("Storeprocedurename", cn);
OleDbParameter PointerOutParam = cmdGetPointer.Parameters.Add("@.Pointer", OleDbType.VarBinary, 100);
PointerOutParam.Direction = ParameterDirection.Output;

Thank you. I also had to make one other change. The Stored Procedure returned the values with the names Photos.AlbumID (instead of just AlbumID) and Photos.Caption (instead of just Caption). Now I am getting the data back. But I get another error.
Specified cast is not valid.
Line 126: Photo temp = new Photo(
Line 127: (int)reader["PhotoID"],
Line 128: (int)reader["Photos.AlbumID"],

--BEGIN CODE--
public static List<Photo> GetPhotos(int AlbumID) {
using (OleDbConnection connection = new OleDbConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
{
using (OleDbCommand command = new OleDbCommand("GetPhotos", connection))
{
command.CommandType = CommandType.StoredProcedure;

OleDbParameter paramAlbumID = command.Parameters.Add("@.AlbumID", OleDbType.SmallInt);
paramAlbumID.Direction = ParameterDirection.Input;
command.Parameters["@.AlbumID"].Value = AlbumID;

bool filter = !(HttpContext.Current.User.IsInRole("Friends") || HttpContext.Current.User.IsInRole("Administrators"));

OleDbParameter paramIsPublic = command.Parameters.Add("@.IsPublic", OleDbType.Boolean);
paramIsPublic.Direction = ParameterDirection.Input;
command.Parameters["@.IsPublic"].Value = filter;

connection.Open();
List<Photo> list = new List<Photo>();
object obj = command.ExecuteNonQuery();
using (OleDbDataReader reader = command.ExecuteReader()) {
while (reader.Read()) {
Photo temp = new Photo(
(int)reader["PhotoID"],
(int)reader["Photos.AlbumID"],
(string)reader["Photos.Caption"]);
list.Add(temp);
}
}
return list;
}
}
}
--END CODE--
--BEGIN CLASS--
public class Photo {

private int _id;
private int _albumid;
private string _caption;

public int PhotoID { get { return _id; } }
public int AlbumID { get { return _albumid; } }
public string Caption { get { return _caption; } }

public Photo(int id, int albumid, string caption) {
_id = id;
_albumid = albumid;
_caption = caption;
}

}
--END CLASS--

Figured it out. The datatype coming back with Photos.AlbumID was not an int.

Try this linkhttp://forums.asp.net/1043012/ShowPost.aspx

YES/NO and asp.net

drw("someField")=myCheckBox.Checked

I tried that alredy and I get:
Exception Details:System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.

Are you letting the DataAdapter generate it's update statements foryou? If so, it's generating them incorrectly and you'll have towrite your Sql by hand.