Thread: Sql Query help

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    1
    Rep Power
    0

    Sql Query help


    Issue:
    Trying to bypass programming limitations to solve a customers problem. They are trying to import DOB into our system as mmddyyyy. Our system does not understand this value and its hardcoded to accept mm-dd-yyyy or mm/dd/yyyy. So to bypass I am importing this into the Country field instead of the DOB field

    I then run this query
    update Patrons set Country = LEFT(country,2)+'/'+substring(country,3,2)+'/'+RIGHT (country,4)

    This turns the raw data into the format I need. What I cant seem to figure out myself is the query to say update patrons set country = dob

    Heres what I have so far

    update Patrons set DateOfBirth = cast(country as daTE) where LEN(Country) =10

    Conversion failed when converting date and/or time from character string.

    I know I am close but I am missing something. Any ideas?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    try converting to yyyy-mm-dd first, instead of mm/dd/yyyy, before doing the CAST
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo