How do I manipulate the date format and read first 6 chars in ID Number on vb.net?

Posted by Paprikano under VB.NET on 8/20/2012 | Points: 10 | Views : 1146 | Status : [Member] | Replies : 2
How do I manipulate the date format and read first 6 chars in ID Number and then use them to create a Date of birth,or if a user has selected date of birth first then, to select an Id number, but only using the year, month and date?

and also Date format in SQL is mm/dd/yyyy, I am trying to have this on client side as dd/mm/yyyy, but if a user does it this way, it gives back an error saying "Error converting nvarchar to date"




Responses

Posted by: tapangupta-17555 on: 8/21/2012 [Member] Starter | Points: 25

Up
0
Down
Dear

Your first question is not clear

But for "and also Date format in SQL is mm/dd/yyyy, I am trying to have this on client side as dd/mm/yyyy, but if a user does it this way, it gives back an error saying "Error converting nvarchar to date"

convert(nvarchar(20), getdate(), 103)

TAPAN

Paprikano, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Paprikano on: 8/23/2012 [Member] Starter | Points: 25

Up
0
Down
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

Login to post response