Convert MS SQL datetime to Oracle varchar(32) interval

Question

I have a query against an MS SQL database that takes the resulting dataset and stores it in an Oracle database table which then gets joined to another Oracle table using the column LABEL_YYYY_MM_DD_HH24_MI varchar(32). I have converted the MS SQL datetime column q.InTimeStart as per below.

Is there a more efficient way of writing this line which takes datetime and finds beginning of 15min interval and stores as varchar(32)?

left(convert(varchar(32),convert(smalldatetime,floor(cast(q.InTimeStart as float)\*(24/.25))/(24/.25)),120),
len(convert(varchar(32),convert(smalldatetime,floor(cast(q.InTimeStart as float)*(24/.25))/(24/.25)),120)) -3) as LABEL_YYYY_MM_DD_HH24_MI,

• Pimgd: what about convert(varchar(32),convert(smalldatetime,floor(cast(q.InTimeStart as float)\*(24/.25))/(24/.25)),120)? Don't you write that twice? Don't you wish you could do that just once?
• AM_Hawk: @Pimgd Using the code you posted in your comment gives seconds...I need it with out the seconds hence the left() function. Statically we can set a value but if your results are dynamic we would need len() as per my answer to my question. I guess I really should ahve included that bit in my question that I want YYYY_MM_DD_HH24_MI no seconds, it may not be so clear just by the alias.

Answer 1

As my data for LABEL_YYY_MM_DD_HH24_MI will always be the same length I can simply write:

left(convert(varchar(32),convert(smalldatetime,floor(cast(q.InTimeStart as float)*(24/.25))/(24/.25)),120),16) as LABEL_YYYY_MM_DD_HH24_MI, 

If the results were of a dynamic length and we needed to remove the last three we would need to use len(x,-3):

left(convert(varchar(32),convert(smalldatetime,floor(cast(q.InTimeStart as float)*(24/.25))/(24/.25)),120),
 len(convert(varchar(32),convert(smalldatetime,floor(cast(q.InTimeStart as float)*(24/.25))/(24/.25)),120)) -3) as LABEL_YYYY_MM_DD_HH24_MI,