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
Nimishaactually, 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 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp)

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

rudy
http://r937.com/|||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 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp)

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

rudy
http://r937.com/|||can i see your select statement?|||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?|||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)|||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]