Link to home
Start Free TrialLog in
Avatar of motioneye
motioneyeFlag for Singapore

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
Avatar of HugoHiasl
HugoHiasl

Since the standard formats do not support your needed date format, you need to concatenate it by yourself

CAST(employeeid as varchar(6)) + DATEPART(yyyy,[activity]) + DATEPART(mm,[activity])

This would create your needed string.
Avatar of motioneye

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
Avatar of Dale Fye
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.
SOLUTION
Avatar of Sara bhai
Sara bhai
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial