String as date in Table

[mansii]

Mates,

One field's type of my ms access table is string, and it is used to store date:

myDate (string, 10)

it holds "08/15/2005"

Now I have problem comparing the field's value to a datetimepicker.

Any ideas?

TIA

 

[earthandfire]

Try using CDate(mydate)


Hope this helps.

 

[Juice05]

Try converting your string value to a date using

cDate(StringValue)

 

[chiph]

Should have used a datetime column type when defining the table.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[earthandfire]

 

[mansii]

Thank's mates for your prompt reply.

But I didn't think that I made myself clear since my boss is knocking my head :=)

The problem is that I want to retrieve data based on the "pretended as date" field (myDate):

Dim UsrCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("Select Count(MyUser) from UserData where myDate >= '" & StartDate.Value.ToShortDateString & "'", rConn)

Perhaps this has something to do with SQL statement.

Regards

 

[Ruffnekk]

You have to make sure that the string generated from StartDate.Value.ToShortDateString is exactly like the format of the string used in your database. ToShortDateString is culture specific (!!) and may results in dd-MM-yyyy or MM-dd-yyyy or dd/MM/yyyy and so on. The best way would probably be (assuming StartDate is a Date):

Dim UsrCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("Select Count(MyUser) from UserData where myDate = '" & Format(StartDate,"dd/MM/yyyy") & "'", rConn)

Regards, Ruffnekk
---
Is it my imagination or do buffalo wings taste just like chicken?

 

[mansii]

Ruffnekk,

That didn't help. :-(
Gotta take another route.

Anyways, thank's for your time.

Dismiss.

 

[chrissie1]

Dim UsrCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("Select Count(MyUser) from UserData where myDate = '" & Format(StartDate,"MM/dd/yyyy") & "'", rConn)

perhaps, but take chiph's advice, he really is a smart man.


Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]

 

[chiph]

Actually, I was being a clown.

But there is a nugget of truth to it -- if your dates are stored in a string column type, there's nothing preventing dates being stored as mm/dd/yy and dd/mm/yy and yy/mm/dd. Once they're in the database like that, you're screwed as there's no easy way to query against it. You can't tell the difference between 07/01/05 and 07/01/05 (first one is July 1st, second is January 7th).

Thus, my recommendation to always use a datetime column type to store date-time values.

If your values in the database are consistently formatted, then Ruffnekks approach is valid -- you need a CONVERT or Format statement (whichever your database supports in it's implementation of SQL) to convert the date-as-string to a date-as-date value.

If you absolutely must store dates as strings, I recommend you use the ISO-8601 format: yyyy-mm-ddThh:nn:ss (the T is a literal). This has the advantage of automatically sorting correctly, and nearly everyone recognizes the format.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[chiph]

On a separate issue, I also recommend that all date-times be stored as UTC (GMT). If you ever have people accessing the data from different time zones, this will save your bacon.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[Ruffnekk]

chiph: the ISO8601 format is very similar to the WMI format, which MSDN states is of type DateTime. However when I query a WMI class and retrieve a property in DateTime, it's not compatible with the .NET Date/DateTime type. I have to store it in a string, which will be of format:

yyyyMMddhhmmss.ffffff(+/-)zzz

y = year, M = month, d = day, h = hour, m = minute, s = second, f = fraction of second, + or - zzz = offset from GMT in minutes.

While we're at it, does anyone know what (database) type this is compatible with? I wrote my own function to convert it to a Date, but I'm just curious.

Regards, Ruffnekk
---
Is it my imagination or do buffalo wings taste just like chicken?

 

[Ruffnekk]

UTC and GMT are not always the same (daylights saving time may alter GMT, but not UTC)

Regards, Ruffnekk
---
Is it my imagination or do buffalo wings taste just like chicken?

 

[earthandfire]

Ruffnekk, Daylight Saving time does not alter GMT, BST (British Summer Time) is GMT + 1 hour (GMT remains unchanged).


Hope this helps.

 

[Ruffnekk]

What I meant to say is that in the summer my time is GMT +02:00, but in the winter it's GMT +01:00 and the point I was trying to make is that UTC is not always the same as GMT.

Regards, Ruffnekk
---
Is it my imagination or do buffalo wings taste just like chicken?

 

[earthandfire]

Ruffnekk, a quick search on google brought up the following:

http://www.dxing.com/utcgmt.htm

http://www.aldridge.com/client_serv/resources/utc.html

http://en.wikipedia.org/wiki/UTC

Hope this helps.

 

[Ruffnekk]

ok I surrender. I may be confusing stuff here =) My apologies!

Regards, Ruffnekk
---
Is it my imagination or do buffalo wings taste just like chicken?

 

[earthandfire]

I tend to get pedantic when I'm in a bad mood.

I'm working an an Access 97 project again = very bad mood.

 

[mansii]

Wow!

Okay, case is back on the table.
About a year ago, I use:

Cast(SomeNumber [blue]As Double[/blue])

to convert SomeNumber, which is a string field, to Double.
I then google around to find any possibility in using:

Cast(SomeDate [blue]As Date[/blue])

with no luck. I mean, yep, it's not only me.

I'm with you, chrissie. Chiph's suggestion is the best thing that I should have done. Thank's to E&F and Ruffnekk for such detail infos.

Any light?

 

[chiph]

the ISO8601 format is very similar to the WMI format, which MSDN states is of type DateTime.

I haven't done much work with WMI, so I haven't encountered this.

But without a space or the literal 'T' between the date & time portions, it's unlikely to be recognized as a ISO-8601 datetime. You'd probably have to write your own converter method (which it sounds like is what you've done). Or insert a 'T' in there yourself, after which conversion becomes much easier.

Also - There are some differences between GMT and UTC. The important one is that UTC was promulgated by the French. ;-)
But there are some minor technical differences too, mainly dealing with leap seconds. Not enough to worry about, IMO.

Chip H.



____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first