comparing dates in asp and sql server

Results 1 to 4 of 4

Thread: comparing dates in asp and sql server

  1. #1
    Join Date
    Dec 1969

    Default comparing dates in asp and sql server

    I find that sql server stores dates in the american format mm/dd/yy is there any way that I can turn any date into a particular format and use this format to store the dates and convert them back out into the format that I need. My problem seems to be that every possible misinterpretation of a date format that can happen does happen and I end up having to do string manipulation in order to get the right date format. Surely there must be an easier way?

  2. #2
    G Waddell Guest

    Default RE: comparing dates in asp and sql server

    hmm..<BR>here are a few formats that you can use with formatdatetime:<BR>FormatDateTime(date ,[NamedFormat])<BR>vbGeneralDate <BR>vbLongdate<BR>vbshortdate<BR>vbLongtime<BR>vbs horttime<BR>unfortunately they format based on the machine they are run on local settings in the case of asp your web server and in client side vb script the clients machine.<BR>Best thing I find to do is when looking for user input on a form say for a date value is to get them to enter the date into separate boxes for day month and year and then you can put it into the correct format that you need.

  3. #3
    MG Guest

    Default One solution

    Some use this method to overcome your problem. Force the user to enter the data in three separate text boxes. One for month, then day, then year. Set the max length to 2, 2, 4 respectively. Then in your asp code before you put it in the db do this<BR><BR>dim dt, m, d, y<BR>m = request.form("mth")<BR>d = request.form("d")<BR>y = request.form("yr")<BR>dt = cdate(mth & "/" & d & "/" & yr) &#039convert field to date<BR><BR>if isdate(dt) = false then &#039check for valid date<BR>response.write "error in date"<BR>else<BR>&#039continue here since the date is good<BR>end fi

  4. #4
    Join Date
    Dec 1969
    Los Angeles, CA

    Default Very easy actually.....

    Just save the dates like you normally would into the DB....easy so far huh.....When you retrieve the date from the DB use the convert function in SQL server to return the date in the format you want.<BR><BR>So however you save the value if you do a convert(varchar,&#060;Columnname&#062;,103) it will return it in the dd-mm-ccyy format the british format<BR><BR>I think there is a bug in the month() day() function...sometimes if the day is less than 12 the month function will return the day as the month and the day will return the other value...wierd huh<BR><BR><BR>

Posting Permissions

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