View Full Version : date function

11-21-2003, 10:22 PM
My date function froma calendar routine returns 05/03/2003
how can I strip the zeroz out of the 05 and 03 so its just

Roy Sinclair
11-21-2003, 10:50 PM
I'd say don't, if for any reason you later sort on that text field you'll wish those zeroes were there again.

11-22-2003, 03:55 AM
Well, if you're only doing this when displaying it to the user, then there's no problem (although I don't like it).

Here's how to do it in a tidy, reusable, modular way:
1) Create a function called something sensible like StripLeadingChar(Str, Char) that does what it says on the tin Loop through the string a character at a time, and when you find a character which ISN'T the specified character to strip, return the rest of the string using Mid()

2) Create another function called something like StripLeadingZerosFromDate() which uses Split() on your date to get the three component parts, then uses your StripLeadingChar() function on each of the first two values in the array (storing the result back in the array each time), then finally uses Join() to reassemble the date.

I suggest this approach simply because stripping a given character from the start of a string is handy in many situations, so it's worth making this part reusable.


11-22-2003, 04:08 AM
the problem is that I get a date srting from the calendar function then I search the table via SQL for records matching that date however the date returned from the calendar function has these zero's, and the data stored in the table has no zeros' the LIKE function does not seem to work in this instance either, am I making hard work of something simple??

11-22-2003, 04:18 AM
The easiest solution is to modify your date function to produce the date in the same format as the DB.

As an aside, you should also be using a date/time field in your DB, since this allows optimally efficient storage, and direct date comparisons using the usual operators (<>, >=, etc.)

11-22-2003, 07:09 AM
not to mention BETWEEN in sql

11-22-2003, 12:39 PM
That too! ;)

11-25-2003, 02:18 PM
Ok I have got the format as you suggested now the bloody thing is very particular about what it returns

this SQL staments works great for october and b4 but anything in november or december and it cant find any results

SQL = "Select * from NMcontact Where "& tblField &" = #"& srchval &"#"

>Select * from NMcontact Where conDate like #5/11/2003#

there are definently data in the d/b for 5/11/2003 but it cant find it
yet it displays dates for 30/10/2003

11-25-2003, 02:24 PM
So you're reformatting the data just so that you can use it in an SQL statement? Why didn't you say so!!!?

You should use a non-ambiguous date format like this:
'Convert date to unambiguous format (e.g. 10-Jan-2001)
Function LongDate(ByVal tmpDate)
If IsDate(tmpDate) then
LongDate = Day(tmpDate) & "-" & MonthName(Month(tmpDate), True) & "-" & Year(tmpDate)
'add your error handling code here
End If
End Function