CODEWITHSUNDEEP
sqlsql-server
Sherendeep Kaur
Sherendeep Kaur

Reputation: 53

Convert nvarchar to Date type with one line of command

I have a column Number_Calc with data type of nvarchar(50) and the data in this column contains information of yyyymmdd. Below is the screenshot of the sample data of the said column.

Number_Calc Column

For example the first row contain this number F**20190531**1632824. 20190531 is actually the year (2019) month (05) and date (31).

Now, I'd like to update my dataset and add a new column called Submission_Date of datatype date in this format "yyyy-mm-dd". I am able to do this by creating a column called Submission_Date_0 and extract 8 characters from the string after the first character "F", after that only generated the desired column Submission_Date. I am using two commands to get this done as per the following: 

update Table1 
set Submission_Date_0 = substring(Number_Calc, patindex('%[0-9]%', Number_Calc), 8);

and from here I get the data in the following format:

Submission_Date_0

Then, I use the following command to convert the data type to the desired data type for the column Submission_Date:

update Table1 
set Submission_Date = convert(nvarchar, Submission_Date_0, 103) ;

And, I get the desired column in the format needed as per the below example:

Submission_Date

I know this can be done in much simpler way in SQL with one line of code. However, I am not able to figure it out. Could anyone please help me on this?

Thank you.

Upvotes: 0

Views: 57

Answers (1)

DarkRob
DarkRob

Reputation: 3833

You can directly write your these query in single query as 

 update Table1
     set Submission_Date = convert(nvarchar(255),
                                   substring(Number_Calc, patindex('%[0-9]%', Number_Calc), 8),
                                   103
                                  ) ;

Upvotes: 1

Related Questions