Conerting Nvarchar(50) to DateTime2

Question

Hi Guys

I have a table called 2019
in it a column named APPT_START_DATE which is currently nvarchar(50)

i would like to convert APPT_START_DATE to datetime2 and i was able to do that with

INSERT INTO [dbo].[2019]
SELECT CAST(APPT_START_DATE AS datetime2) as NewDate
FROM [2019]

and now the fun starts
NewDate was never added to the actual table and I have no idea why and though I see it on the screen it's not accessible

the question is how do I split APPT_START_DATE to two APP_Start_Date and App_Start_Time

Have I mentioned I was a total newbie ?

Thanks

Answer 1

Hi @Debilon ，

Welcome to the microsoft tsql Q&A forum!

Splitting Date into 2 Columns (Date + Time):

select convert(date, getdate()) as [Date],   
convert(varchar(8), convert(time, getdate())) as [Time]  

Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)? So that we’ll get a right direction and make some test.

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Hi Echo and thank you so much for your previous reply

Table structure is simple: One column no key datatype is nvarchar(50)

The data in the column is

2015-04-27 13:30:00.0000000
2015-04-27 14:30:00.0000000
2015-04-30 14:00:00.0000000
2015-04-30 14:00:00.0000000
2016-04-12 11:00:00.0000000

Answer 3

Hi @Debilon ，

If you convert the data type of a column in a SELECT statement, in fact, you can only temporarily change the data type of the column during the SELECT process, and you cannot change the actual data type of the column in the table.
If you want to change the actual data type of the column, you need to change the column definition.(ALTER TABLE (Transact-SQL))

Inserting data is normal:

CREATE TABLE [2019]([APPT_START_DATE] nvarchar(50));  
  
INSERT INTO [2019] VALUES  
('2015-04-27 13:30:00.0000000'),  
('2015-04-27 14:30:00.0000000'),  
('2015-04-30 14:00:00.0000000'),  
('2015-04-30 14:00:00.0000000'),  
('2016-04-12 11:00:00.0000000');  
  
INSERT INTO [2019]  
SELECT CAST(APPT_START_DATE AS datetime2) as NewDate  
FROM [2019];  
  
SELECT * FROM [2019];  

Output:

Splitting Date into 2 Columns (Date + Time):

ALTER TABLE [2019] ADD App_Start_Time time NULL;  
  
UPDATE t  
SET APPT_START_DATE=convert(date, APPT_START_DATE),  
App_Start_Time=convert(varchar(8), convert(time, APPT_START_DATE))  
FROM [2019] t;  
  
SELECT *  
FROM [2019];  
  
DROP TABLE [2019];  

Output:

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".