Results 1 to 7 of 7
  1. #1
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121

    How to covert yyyymmdd to mm/dd/yyyy

    I have tried many times but not succeed, I have pick up date as text 20171020
    When I use isdate function it recognize as date shows 1
    I want to covert this date to 10/20/2017

    Kindly help me out . Thanks in advance.

    SELECT

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Along the lines of:

    DateSerial(Left(FieldName, 4), Mid(FieldName, 5, 2), Right(FieldName, 2))
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121
    Quote Originally Posted by pbaldy View Post
    Along the lines of:

    DateSerial(Left(FieldName, 4), Mid(FieldName, 5, 2), Right(FieldName, 2))

    Not-working
    Error mesg: 'Mid' is not a recognized built-in function name.
    it works in Excel not in SQL.

  4. #4
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Look at this video and see if you can do what you need after watching it.
    http://www.datapigtechnologies.com/f...anipulate.html
    Also the mid function is an Access function. Look at this link

    https://www.techonthenet.com/access/...string/mid.php

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by adnancanada View Post
    Not-working
    Error mesg: 'Mid' is not a recognized built-in function name.
    it works in Excel not in SQL.
    Works for me, in a query or in VBA:

    ?DateSerial(Left("20171020", 4), Mid("20171020", 5, 2), Right("20171020", 2))
    10/20/2017
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    This is a query built in Access query designer? Not a query in SQLServer or MySQL or T-SQL? Will Right() work - test it by itself.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Oh, I missed that this was in SQL Server forum. If in T-SQL simply:

    convert(date,FieldName)

    In a test:

    select convert(date,'20171020')

    returned

    2017-10-20
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Bulk convert DDMMYYYY to YYYYMMDD?
    By abm in forum Access
    Replies: 13
    Last Post: 08-20-2015, 01:06 PM
  2. How to Covert Date field to string
    By kassem in forum Programming
    Replies: 2
    Last Post: 06-05-2014, 09:06 AM
  3. covert special text to number
    By masoud_sedighy in forum Programming
    Replies: 1
    Last Post: 05-13-2013, 10:39 AM
  4. Replies: 1
    Last Post: 09-06-2011, 05:24 AM
  5. Convert date format yyyymmdd for datediff
    By TEN in forum Programming
    Replies: 1
    Last Post: 06-17-2009, 09:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums