Date Conversion

Well it seems converting to/from various date formats appears regularly in the forum.

I have my app working as I wish so far, but would appreciate any knowledgable folks looking over the following :

In my SQLite database I have a date column, stored as type DATE. It has the following test entries :

2014/01/27
2014/02/24
2014/03/24
2014/04/21
2014/05/19
2014/06/16
2014/07/14
2014/08/11
2014/09/08
2014/10/06
2014/11/03
2014/12/01
2014/12/29

When retrieving said field through a normal SQLSelect statement, I can’t for the life of me get it to work using RecordSet.IdxField(2).DateValue.xxx, instead of RecordSet.IdxField(2).StringValue.

So intead I have done this:

lb.AddRow(DateToFriendly(RecordSet.IdxField(2).StringValue))

The DateToFriendly method takes a string and also returns a string like so:

[code]
Dim FriendlyDate, Month As String

Select Case Val(Mid(MyString,6,2))
Case 1
Month = " January "
Case 2
Month = " February "
Case 3
Month = " March "
Case 4
Month = " April "
Case 5
Month = " May "
Case 6
Month = " June "
Case 7
Month = " July "
Case 8
Month = " August "
Case 9
Month = " September "
Case 10
Month = " October "
Case 11
Month = " November "
Case 12
Month = " December "
End

FriendlyDate = Right(MyString,2) + Month + Left(MyString,4)

Return FriendlyDate[/code]

Is this a reasonable approach, or is there a more efficient method I could use ?

Thanks.

Hhmm…your database should be storing them in SQLDateFormat which is in the form of YYYY-MM-DD. I suspect you’re saving it in the database as text (short date) versus SQL format date?

If this is the way it has to be (i.e. you’re not in charge of the data), you can use the ParseData function to see if the framework can figure out the date. Then you don’t have to go through the hassle of parsing it yourself.

If you have the power, save the date in SQLdate format. It will make life easier all the way around.

Actually I’ve just realised I’ve used / instead of - as the separator.

Its been a long day :confused:

How embarrassing. Nobody else here has EVER done anything like that and spent hours tracking down something so trivial, even writing methods to compensate for some perceived flaw, EVER.

No sir, not nobody…

Then RecordSet.IdxField(2).DateValue should just work. I would do something like this:

dim d as date = RecordSet.IdxField(2).DateValue
if d <> nil then
iMonth = d.month
end

Ive just changed the / to -, and then used RecordSet.IdxField(2).DateValue.AbbreviatedDate, and that works quite well, as do the other DateValue.xxx options.

The only thing I cant see it how to get the month name in full text i.e ‘December’ instead of ‘Dec’.

Im guessing that would mean either using a custom method or changing the locale settings ?