How to use DateTime constants in DataTable.Compute or Filter

A

aw

There are some informations about it in Framework Help but they are
incomplete, I think. Some examples:
1. compute property: datetime is without apostrphes as MM/dd/yy
2. expression property: datetime is in hashes as MM/dd/yy
3. null datetime as ## doesn't work
4. when I use DateTime.ToString() results depends on international
settings or kind of operation system.
5. I couldn't find any information about such datetime format in msdn

datetime.ToString("u") often works but not always - on computers
having thesame international settings and time zone (I use it with Polish
settings).
myDate.ToString("u").Replace("Z","") - as well. Sometimes should be
YYYY-MM-DD format when in os I have DD-MM-YYYY
American format MM/DD/YYYY works - but I don't know if always.

May be someone knows how to use it?
Thx in advance
 
J

Jon Skeet [C# MVP]

Cor Ligthert said:
I think it is better as you ask question like this next time in the
newsgroup
microsoft.public.dotnet.languages.vb
Click to expand...

Why? It's an ADO.NET question.
However working with dates did look for me as well very complex, however the
possibilities are endless.

dim wp as string = Now.toString("dd-MM-yyyy")

gives 21-05-2004

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpre
f/html/frlrfSystemDateTimeClassToStringTopic.asp

Ma nadzieje, ze to pomoze?
Click to expand...

I think you've missed part of the point of the question, which is
whether the RowFilter expression's format is *always* meant to be in US
format (as #MM/dd/yyyy#) or whether it depends on culture. I suspect
it's always meant to be in the US format, given the documentation, but
it's not clear.

There's also the question of how times should be represented, as that
isn't covered (as far as I can see) in the documentation.

Basically the DataView.RowFilter/DataColumn.Expression etc
documentation is significantly lacking - but then, I believe the
mechanism is pretty significantly lacking in the first place, as it
would make a lot of sense to *also* allow row filters, computed
expressions etc to be written in actual code rather than just in text
expressions. I haven't checked whether or not this will be available in
ADO.NET 2.0, but I'm sure I'm not the only person to want it.
 
M

Miha Markic [MVP C#]

Hi,

I think that you should use #mm/dd/yyyy# notation as described in
DataColumn.Expression help topic.
 
C

Cor Ligthert

Hi Jon,
Why? It's an ADO.NET question.
Click to expand...
I did not say that it is not an ado.net question, I am not as good as you
that without people write one syllable about it I know that this is an
ADO.NET question about.

However what was asked would most probably gets better answers in the
language.vb newsgroup. Although when needed I can cover them all as well
here.

Cor
 
J

Jon Skeet [C# MVP]

I think that you should use #mm/dd/yyyy# notation as described in
DataColumn.Expression help topic.
Click to expand...

And should that be used regardless of culture? That's what isn't clear.

It's also not clear how the time component should be specified.
 
J

Jon Skeet [C# MVP]

Cor Ligthert said:
I did not say that it is not an ado.net question, I am not as good as you
that without people write one syllable about it I know that this is an
ADO.NET question about.
Click to expand...

Eh? It was fairly *clearly* an ADO.NET question, given the title. No
psychic powers required.
However what was asked would most probably gets better answers in the
language.vb newsgroup. Although when needed I can cover them all as well
here.
Click to expand...

But what on earth does it have to do with VB? From the post, the OP may
not even *be* a VB.NET programmer.

The post was much more on-topic here than it would have been in the VB
group, and here it reached everyone interested in ADO.NET, not just VB
programmers.
 
C

Cor Ligthert

Hi John,

Now I understand, you want to know how to do this.

By instance in this way roughly created using VB.net

Most is of course VB.net code.

AW
Nie zauwazylem tematu. :)

I hope this helps?

Cor

\\\
Dim dt As New DataTable
Dim dc As New DataColumn("OHM")
dc.DataType = GetType(System.Int32)
Dim dd As New DataColumn("OHH")
dd.DataType = GetType(System.DateTime)
dt.Columns.Add(dc)
dt.Columns.Add(dd)
For i As Integer = 0 To 24
dt.Rows.Add(dt.NewRow)
dt.Rows(i)(0) = 100
dt.Rows(i)(1) = CDate("01-01-2003").AddMonths(i)
Next
' This is the sample
Dim Thismonth As String = Now.ToString("#MM/dd/yy#")
Thismonth = Thismonth.Replace("-", "/")
MessageBox.Show(dt.Compute("Sum(OHM)", "OHH < " _
& Thismonth).ToString)
////
 
A

aw

Now I understand, you want to know how to do this.
By instance in this way roughly created using VB.net
Most is of course VB.net code.

AW
Nie zauwazylem tematu. :)

I hope this helps?

Cor

\\\
Dim dt As New DataTable
Dim dc As New DataColumn("OHM")
dc.DataType = GetType(System.Int32)
Dim dd As New DataColumn("OHH")
dd.DataType = GetType(System.DateTime)
dt.Columns.Add(dc)
dt.Columns.Add(dd)
For i As Integer = 0 To 24
dt.Rows.Add(dt.NewRow)
dt.Rows(i)(0) = 100
dt.Rows(i)(1) = CDate("01-01-2003").AddMonths(i)
Next
' This is the sample
Dim Thismonth As String = Now.ToString("#MM/dd/yy#")
Thismonth = Thismonth.Replace("-", "/")
MessageBox.Show(dt.Compute("Sum(OHM)", "OHH < " _
& Thismonth).ToString)
////
Click to expand...


Sorry, I use C#, but all Your messages were useful. Now we try to test
static public string ToStringCdate(object o)

{

DateTime dt=ToDateTime(o);

if(dt==DateTime.MinValue) return "''";

return "#"+dt.ToString("MM/dd/yyyy")+"#";

}

format in our application. We use MinValue as null.
 
C

Cor Ligthert

Sorry, I use C#, but all Your messages were useful. Now we try to test
static public string ToStringCdate(object o)
Click to expand...

I saw that it was possible that you was using C# however to let Jon win is
something I do not do that soon, however he did that this time. (Therefore
that Polish in between).

:)

Cor
 
M

Miha Markic [MVP C#]

Hi Jon,

Jon Skeet said:
And should that be used regardless of culture? That's what isn't clear.
Click to expand...

I think that the format is fixed regardless to culture.
 
A

aw

And should that be used regardless of culture? That's what isn't clear.
I think that the format is fixed regardless to culture.
Click to expand...


Unfortunately, NO! (Tested on about 20 computers on WinXP, 2000, 2003
Framework 1.1 and different time zones and cultures - almost US and Poland,
big COM+ application - above 100 projects - in C#, OleDb, Oracle, MSSQL,
DB2). May be it's an error because we can't find any logic in it.
 
D

Dan Tanzer

I was curious if there was an answer for 3. I am trying to do a check
for null dates as part of my expression but I havent quite got it
working yet!

my pseudo code for the string expression looks somthing like this

string myExpression = string.Format("dt_off >= #{0}# AND dt_off =
null", bucket.OnDate );
object myvalue = dt.Compute("Sum(Somecolumn)", myExpression );

where i would like to compute somecolumn where dt_off is somedate and
where dt_off is also null.

Any help would be great , thanks

Dan Tanzer
supportNOSPAMATzerama.net
 
J

Jon Skeet [C# MVP]

Dan Tanzer said:
I was curious if there was an answer for 3. I am trying to do a check
for null dates as part of my expression but I havent quite got it
working yet!
Click to expand...

Basically, null will never compare with anything - you need to use
ISNULL to check for it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

String to DateTime 1
Reverse the Day and Month position in DateTime structure? 5
DateTIme help 2
DateTime headache 1
How to enter a date in DD-MM-YYYY format 2
DateTime and localization 2
Pass through datetime 1
How to convert a date string to datetime value with custom date format? 1

Top