Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Location
    SA
    Posts
    25

    Unanswered: Insert statement

    Hi

    My insert statement follows

    INSERT INTO AppUser(Forename, Surname, Job_Title, DOB, Employee_Number, User_Type,Employee_Site_ID) values ('J', 'W', 'GMANAGER', '1955-07-20', 'xx', 'E', '2 ');

    When the record is inserted the DOB field gets populated as '1955/07/20'. The hyphen gets replaced by forward slash. I need the hyphen to be retained

    Any suggestions?
    Ta
    Nimisha

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, what you are seeing is some sort of default formatting for datetime values, because when the date is entered into the database, it is stored as two 4-byte integers, one integer for the day and another for the time

    there are several ways to get the date format you need, but one way is to apply formatting in the SELECT sql statement using the CONVERT function

    select convert(char(10),DOB,120) from ...

    see CAST and CONVERT

    style 120 is the yyyy-mm-dd format you want


    rudy
    http://r937.com/

  3. #3
    Join Date
    Mar 2003
    Location
    SA
    Posts
    25

    Insert statement

    Hi Rudy

    I had tried the convert function, but that does not seem to work. Hence I was a bit baffled.
    Is it a SQL server setting or something?
    Any clues?
    Ta
    Nimisha


    Originally posted by r937
    actually, what you are seeing is some sort of default formatting for datetime values, because when the date is entered into the database, it is stored as two 4-byte integers, one integer for the day and another for the time

    there are several ways to get the date format you need, but one way is to apply formatting in the SELECT sql statement using the CONVERT function

    select convert(char(10),DOB,120) from ...

    see CAST and CONVERT

    style 120 is the yyyy-mm-dd format you want


    rudy
    http://r937.com/

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    can i see your select statement?

  5. #5
    Join Date
    Mar 2003
    Location
    SA
    Posts
    25
    Hi

    This is my select statement: It gebereates an insert statement for me.

    SELECT 'INSERT INTO AppUser(Forename, Surname, Job_Title, DOB, Employee_Number, ,Group_ID, Profile_ID, Category_ID) values
    (''' + Initials + ''', ''' + Name + ''', ''' + Position + ''', ''' + rtrim(convert(char(10),Date_Of_Birth,21)) + ''',
    ''' + Employee_No + ''', ''' + 'E' + ''', ''' + @SID + ''' );'
    FROM Employee

    Ta
    Nimisha


    Originally posted by r937
    can i see your select statement?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    looks to me like your SELECT should work, but when you construct the INSERT statement, you are not providing enough values for the columns specified

    after the DOB, you have Employee_Number, ,Group_ID, Profile_ID, Category_ID, but you are only creating 2 values, and i think the single quotes around the E need escaping (doubling)

  7. #7
    Join Date
    Mar 2003
    Location
    SA
    Posts
    25

    i

    Hi I got it working. In th end it was just to use the syntax replace and convert.

    Thank you so much for the suggestion. Appreciate it.
    Nimisha


    [UOTE]Originally posted by r937
    looks to me like your SELECT should work, but when you construct the INSERT statement, you are not providing enough values for the columns specified

    after the DOB, you have Employee_Number, ,Group_ID, Profile_ID, Category_ID, but you are only creating 2 values, and i think the single quotes around the E need escaping (doubling)
    [/QUOTE]

Posting Permissions

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