Converting string datetime to datetime EST from Oracle

Question

Hi All,
I am trying to convert datetime in string format from oracle to T SQL. The data is of the format
201808010415
In ORACLE I have the conversion as below
TO_DATE(TO_CHAR( FROM_TZ( CAST( TO_DATE(u1.P_DTM , 'YYYYMMDDHH24MI') AS TIMESTAMP ), 'UTC' ) AT TIME ZONE 'America/New_York', 'YYYYMMDDHH24MI' ) , 'YYYYMMDDHH24MI')) > ADD_MONTHS(TRUNC(SYSDATE,'MM'),-1)
I tried in T SQL as
CONVERT(VARCHAR(14),TRY_CAST(u1.P_TM AS DATETIME,102)
I am getting all NULLS.
Please advice.
Thanks

Answer 1

If you want to switch to a different time zone use "AT TIME ZONE", more info:
https://learn.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-ver16

for example (thnx to @NikoXu-msft for the script):

 declare @example varchar(max) = '201808010415'  
 set @example= SUBSTRING(@example, 1, 4)+'-'+ SUBSTRING(@example, 5, 2)+'-'+SUBSTRING(@example, 7, 2)+' '+SUBSTRING(@example,9,2)+':'+SUBSTRING(@example,11,2)  
 --select @example  
 select CONVERT(datetime, @example) AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' as dataconverted   

Answer 2

Hi @Beacon77 ,

Try this code:

declare @example varchar(max) = '201808010415'  
set @example= SUBSTRING(@example, 1, 4)+'-'+ SUBSTRING(@example, 5, 2)+'-'+SUBSTRING(@example, 7, 2)+' '+SUBSTRING(@example,9,2)+':'+SUBSTRING(@example,11,2)  
--select @example  
select CONVERT(datetime, @example) as dataconverted  

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 3

Thanks All
I have used this function
CONVERT(DATETIME,STUFF(STUFF(STUFF(STUFF(u1.P_DTM,5,0,'-'),8,0,'-'),11,0,' '),14,0,':'))
Thanks again for your time.