July 20, 2017 at 8:17 am
Hi
I have a column that's in Datetime format and I want to update and convert that column to varchar.
So I use the following SQL Query below and it says 51 rows was update but when I take a look the column is still in datetime format and has not changed to Varchar. Please let me know what has gone wrong. Thank you!
Update [dbo].[MDWSAMPLE2]
SET MBR_BIRTH_DT = CONVERT(VARCHAR(8), [MBR_BIRTH_DT], 112)
This is how it is in the database:
2014-01-13 00:00:00.000
2014-01-13 00:00:00.000
2014-01-23 00:00:00.000
2014-01-23 00:00:00.000
2014-01-23 00:00:00.000
2014-01-23 00:00:00.000
2014-01-11 00:00:00.000
2014-01-11 00:00:00.000
2014-01-11 00:00:00.000
2014-01-11 00:00:00.000
1992-06-12 00:00:00.000
1992-06-12 00:00:00.000
1974-07-23 00:00:00.000
This is how I want it:
20140113
20140113
20140123
20140123
20140123
20140123
20140111
20140111
20140111
20140111
19920612
19920612
July 20, 2017 at 8:36 am
You're updating your existing column with the values which is still a date column. If you pass it a varchar, it will be converted to a datetime and be stored as one. You'll need to create a new column of varchar type, or a computed value column.
Why would you want to store datetimes as a varchar though? There are so many reasons why this is a bad idea.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 20, 2017 at 8:50 am
Thom A - Thursday, July 20, 2017 8:36 AMYou're updating your existing column with the values which is still a date column. If you pass it a varchar, it will be converted to a datetime and be stored as one. You'll need to create a new column of varchar type, or a computed value column.Why would you want to store datetimes as a varchar though? There are so many reasons why this is a bad idea.
Ok. How do I make a computed value column?
I want to store datetimes as a varchar because I want to match it with another data source(SP) who's date column is saved as a varchar(previous analyst did this). I can't change the data source (SP)from varchar to date time till I get approval from the governance board and since this table(MD) I'm playing with is a sample, I can it all I want.
July 20, 2017 at 9:13 am
juelz3682 - Thursday, July 20, 2017 8:50 AMOk. How do I make a computed value column?
This will add a new computed column called "MBR_BIRTH_VC", which will provide a varchar value for your date. It will also leave your existing column intact, which I strongly recommend.ALTER TABLE [dbo].[MDWSAMPLE2] ADD MBR_BIRTH_VC AS CONVERT(varchar(8), [MBR_BIRTH_DT], 112);
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 20, 2017 at 9:25 am
Thom A - Thursday, July 20, 2017 9:13 AMjuelz3682 - Thursday, July 20, 2017 8:50 AMOk. How do I make a computed value column?This will add a new computed column called "MBR_BIRTH_VC", which will provide a varchar value for your date. It will also leave your existing column intact, which I strongly recommend.
ALTER TABLE [dbo].[MDWSAMPLE2] ADD MBR_BIRTH_VC AS CONVERT(varchar(8), [MBR_BIRTH_DT], 112);
Thank you
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply