TypeMismatch when Converting number yyyymmdd to mm/dd/yyyy

Posted on 2012-08-28
Last Modified: 2012-08-28
I need to convert a number/date field from yyyymmdd to mm/dd/yy and I am getting a data type mismatch.


what am I missing
Question by:Karen Schaefer
    LVL 61

    Accepted Solution

    Try explicitly converting to a numeric datatype:

    HIRE_DATE: DateSerial(CInt(Left([EFFECT_HIRE_DATE],4)),CInt(Mid([EFFECT_HIRE_DATE],5,2)),CInt(Right([EFFECT_HIRE_DATE],2)))

    Open in new window


    Author Comment

    by:Karen Schaefer
    What needs to be modified to be able to handle null value -

    Author Closing Comment

    by:Karen Schaefer
    thanks that did it.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now