SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Dec 2004
    Location
    glossop
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Convert nvarchar to datetime

    I have a big problem i am trying to convert an nvarchar field to a date field in a table. When i try to do this in table design i get an arithmetic overflow error which i think is caused by the field being too long. the dates are all in the format mm/dd/yyyy. To fix this problem i think i need to convert the data to the format dd/mm/yy then i can alter the table and convert the field from nvarchar to a date field. I have tried using the convert and case functions but they dont seem to do what they are supposed to do!

    Any Ideas?

  2. #2
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you show us the convert code you used that didn't work?

    Mike

  3. #3
    SitePoint Member
    Join Date
    Dec 2004
    Location
    in my mind. well, physically in the Bay Area
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What do you mean by (the convert and cast) didn't do what they were suppossed to do? Can you give an example of your results? You put quotes around the date value so it was not interpreted as a numeric value, right? ( ie: convert( smalldatetime, 'mm/dd/yyyy') or cast ('mm/dd/yyyy' as smalldatetime)

  4. #4
    SitePoint Member
    Join Date
    Dec 2004
    Location
    glossop
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the data in the table is in the format mm/dd/yyyy as a varchar the only convert function that will work is CONVERT(datetime, comm_date, 101) this gives me the output dd/mm/yyyy but what i want is it to give me dd/mm/yy so i can change the field type from a varchar to a datetime for future inputs. when i use other styles in the convert i get an error message

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    CONVERT(datetime, comm_date, 101) is what you need to change the field type from varchar to datetime
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Dec 2004
    Location
    glossop
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But how do i change comm_date to be a datetime field rather than a varchar? Am i Doing this right i'm selecting my table from enterprise manager, opening a table query then running the sql code SELECT CONVERT(datetime, comm_date,101) from Project,
    This is giving the values i want to update comm_date to. So then i do UPDATE Project set comm_date = convert(datetime, comm_date, 101). This then gives me an error "Syntax error converting datetime from character string". I'm doing something wrong but i cant work out what it is?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    try this --

    alter table Project alter column comm_date datetime
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Dec 2004
    Location
    glossop
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your help rudy, My coding was right its the data in my tables it didnt like some of my date fields were not formatted correct.


Bookmarks

Posting Permissions

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