Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Cincinnati, Ohio
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dates different in SQL and Access (2003/sql2000)

    I have a linked table that in SQL has a field with date format with 8 characters. The data appears as "4/11/2005". In access the same data appears as "2005-04-11 00:00:00.000". I cannot change this in my form regardless of using short or any other date format. I would like the dates to appear as mm/dd/yy. Why is this happening and how can I resolve it?
    Thank you.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates different in SQL and Access (2003/sql2000)

    Are the data left-aligned or right-aligned in Access?

  3. #3
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Cincinnati, Ohio
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates different in SQL and Access (2003/sql2000)

    They are left-aligned. I tried changing the alignment and it has no effect on the displayed date.
    Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates different in SQL and Access (2003/sql2000)

    Changing the alignment has no effect on the value. That the values are left-aligned means that they are text values, not date/time values, hence applying a format doesn't work. You'll have to calculate the dates in a query or in an expression on a form/report. Say that the field is named DateField. In a query you could use
    <code>
    ConvertedDate: DateSerial(Left([DateField],4),Mid([DateField],6,2),Mid([DateField],9,2))
    </code>
    or in the Control Source of a text box on a form or report
    <code>
    =DateSerial(Left([DateField],4),Mid([DateField],6,2),Mid([DateField],9,2))
    </code>
    You should be able to set the Format property for the calculated column or text box the way you want.

Posting Permissions

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