Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9

Thread: date function

  1. #1
    Regular Coder
    Join Date
    Jul 2002
    Location
    Bunbury W.A
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    date function

    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
    5/3/2003

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'd say don't, if for any reason you later sort on that text field you'll wish those zeroes were there again.

  • #3
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.

    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #4
    Regular Coder
    Join Date
    Jul 2002
    Location
    Bunbury W.A
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    the problem

    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??

  • #5
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.)
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #6
    Senior Coder
    Join Date
    Jun 2002
    Location
    41° 8' 52" N -95° 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    not to mention BETWEEN in sql
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #7
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    That too!
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #8
    Regular Coder
    Join Date
    Jul 2002
    Location
    Bunbury W.A
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Done that

    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 &"#"

    displays
    >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

  • #9
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    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:
    Code:
    '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)
    	Else
    		'add your error handling code here
    	End If
    End Function
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •