Here is what I was talking about, extracting the Date of Birth from the ID-number, e.g 8704066202022,, then take 870406 and form a date of birth out of that.:
txtdob.Text = txtUser_ID_Number.Text.Substring(4, 2) & "/" & txtUser_ID_Number.Text.Substring(2, 2) & "/" & "19" & txtUser_ID_Number.Text.Substring(0, 2)
Below here I have a stored proc
CREATE PROCEDURE [dbo].[spRegisterUser]
(
@User_ID [VARCHAR](50),
@User_Name [VARCHAR](50),
@User_Surname [VARCHAR](50),
@User_ID_Number [varchar](18),
@Email_Address [VARCHAR](50),
@Password [VARCHAR](20),
@Confirm_Password [VARCHAR](20),
@Financial_Advisor [CHAR] (50),
@Financial_Advisor_Email_Address [CHAR] (60),
@Gender [VARCHAR] (1),
@Age [VARCHAR](3),
@Profession [VARCHAR](50),
@Contact_Phone_Number [CHAR](15),
@Date_Of_Birth [DATE],
@Date_Created [DATE],
@Time_Created [TIME](7)
)
AS
BEGIN TRANSACTION
--select convert(varchar, Date_Of_Birth, 101) from User_table
INSERT INTO [User_Table] ([User_ID], [User_name], User_Surname, User_ID_Number, Email_Address, [Password], Confirm_Password, Financial_Advisor, Financial_Advisor_Email_Address,
Gender, Age, Profession, Contact_Phone_Number, Date_Of_Birth, Date_Created, Time_Created )
VALUES (@User_ID,@User_name,@User_Surname,@User_ID_Number, @Email_Address, @Password, @Confirm_Password, @Financial_Advisor,
@Financial_Advisor_Email_Address, @Gender, @Age, @Profession, @Contact_Phone_Number,
convert(nvarchar(20), @Date_Of_Birth, 103 ) , @Date_Created, @Time_Created);
COMMIT
and also the convert(nvarchar(20), getdate(), 103) does not work on my side
convert(nvarchar(20), @Date_Of_Birth, 103)
Take notice of the highlited area, this proc doesn't make a difference, and I have re-wrote it a couple of times,
it gives back an error if you DOb is "20/12/1995", because there are 12 months in a year and not 20, so it's like mssql's default date format is "mm/dd/yyyy"
and I want it to be "dd/mm/yyyy" with using "convert(nvarchar(20), @Date_Of_Birth, 103 )" of which it doesn't work
Paprikano, if this helps please login to Mark As Answer. | Alert Moderator