July 18, 2017 at 9:53 am
Hello Everyone,
I'm having trouble with converting a column to a date format. This column datatype is a float. In it are a bunch of numbers, for example, '11834' '7041' '12060' etc. These number represent the days between the year January 1, 1960 and an individuals birthday. I have a command to see the birthdays:
SELECT DATEADD(DD, M_DOB, '1960-01-01'), 102) FROM TABLE
When I run this command for example, '11854' becomes '1992-05-26' which I want
But I also want to update the column with the new date format. I tried using the convert function :
Update table
SET = CONVERT(VARCHAR(10), DATEADD(DD, M_DOB, '1960-01-01'), 102) FROM table
But I keep getting this error message:
Error converting data type varchar to float.
Can anyone help me convert this data type and still get the correct date format.
Thanks!
July 18, 2017 at 10:00 am
juelz3682 - Tuesday, July 18, 2017 9:53 AMHello Everyone,I'm having trouble with converting a column to a date format. This column datatype is a float. In it are a bunch of numbers, for example, '11834' '7041' '12060' etc. These number represent the days between the year January 1, 1960 and an individuals birthday. I have a command to see the birthdays:
SELECT DATEADD(DD, M_DOB, '1960-01-01'), 102) FROM TABLE
When I run this command for example, '11854' becomes '1992-05-26' which I wantBut I also want to update the column with the new date format. I tried using the convert function :
Update table
SET = CONVERT(VARCHAR(10), DATEADD(DD, M_DOB, '1960-01-01'), 102) FROM tableBut I keep getting this error message:
Error converting data type varchar to float.Can anyone help me convert this data type and still get the correct date format.
Thanks!
That's not really how it works! A column can only be one datatype. During the update you describe, your column would contain old values of FLOAT and new values of VARCHAR.
Why not add a new DATETIME column to the table, then populate it with the calculated values? You could also consider using a calculated column.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 18, 2017 at 10:07 am
Looks like you're trying to update the existing (float) column with a date, which won't work. you'll need a new column, and then (if you want) you can drop the old.
For example:USE DevDB
GO
--Create sample table
CREATE TABLE Birthday (M_DOB float);
GO
--Some random dates
INSERT INTO Birthday
VALUES (11243),(15431),(12344),(16573),(16573);
GO
--Using an expression.
SELECT *, DATEADD(DAY, M_DOB,'19600101') AS M_DOB_Date
FROM Birthday;
GO
--Add a new column
ALTER TABLE Birthday ADD M_DOB_Date date;
GO
--update the column
UPDATE Birthday
SET M_DOB_Date = DATEADD(DAY, M_DOB,'19600101');
GO
--Then you could remove the column and rename the new one (commented out as the next statement will not work otherwise)
--ALTER TABLE Birthday DROP COLUMN M_DOB;
GO
GO
--Or you could create a computed column
ALTER TABLE Birthday ADD M_DOB_Computed AS CAST(DATEADD(DAY, M_DOB,'19600101') AS date);
GO
--Checkt he data
SELECT *
FROM Birthday;
GO
--Clean up
DROP TABLE Birthday;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 18, 2017 at 10:12 am
So first, you'll need a new column on the table with a datetime datatype, you will not be able to store a date formatted like '1992-05-26' in a float column. This command is already returning a datetime, DATEADD(DD, M_DOB, '1960-01-01'), so simply add your new column and set it to that. If you need to retrieve it format it as the desired format at that time.
July 18, 2017 at 10:15 am
Hi guys,
Thank you for your help so far. I thought about adding anew column and just replacing the old column but I thought maybe there was a way to see if I could just change the data type of the float column to a date column or any date type column that I can then convert it to the date format using the date add function.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply