motioneye
asked on
How to update this datetime to new table ?
I have one column in our table [activity] with storing datetime like 2014-01-01 00:00:00.000
But now we have a request to convert this value to another new column [MthID] with the values will be like 201401 for each rows we have in the table
This MthID column will have to combine the vaule from employeeid, so it should look like
123456201401
where 123456 is employeid and 201401 is after converted from [activity] column
But now we have a request to convert this value to another new column [MthID] with the values will be like 201401 for each rows we have in the table
This MthID column will have to combine the vaule from employeeid, so it should look like
123456201401
where 123456 is employeid and 201401 is after converted from [activity] column
ASKER
Hi,
I received error message like below.
select CAST(employeeid as varchar(50)) + DATEPART(yyyy,[activity]) + DATEPART(mm,[activity]) from MyTable
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '2.00109e+007' to data type int.
But when I run only below
select DATEPART(yyyy,[activity month]) + DATEPART(mm,[activity month])
from My_Catw
the results really weird it just show 2015 for all rows
I received error message like below.
select CAST(employeeid as varchar(50)) + DATEPART(yyyy,[activity]) + DATEPART(mm,[activity]) from MyTable
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '2.00109e+007' to data type int.
But when I run only below
select DATEPART(yyyy,[activity month]) + DATEPART(mm,[activity month])
from My_Catw
the results really weird it just show 2015 for all rows
Personally, I'd advise against combining data (EmployeeID and YrMonth) into a single field. What is the reason you think you need to do that? You can always concatenate them in a query, But once you combine them you now have to maintain them as well. If you need to be able to reference these three fields in your new table, put all three fields there, don't concatenate them.
I can understand the desire to format the year and month as a single field, but I would generally just recommend creating a view of the data with that computed field, rather than adding another field to your table.
I can understand the desire to format the year and month as a single field, but I would generally just recommend creating a view of the data with that computed field, rather than adding another field to your table.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
CAST(employeeid as varchar(6)) + DATEPART(yyyy,[activity]) + DATEPART(mm,[activity])
This would create your needed string.