Type conversions

Let's start off with the single most annoying SQL Server feature... type conversions.

At first glance it doesn't look bad at all. We have typical type casting as well as some additional functions like Convert. Casting constants seem to be very useful.

Let's try it then:

select x, 
 cast(x as integer) as xn, 
 cast(x as numeric(8,2)) as xn2, 
 cast(x as float) as xf
from (
 select '1' as x
) a

ok... works nice...
let's try this now:

select x, 
 convert(integer, x) as xn, 
 convert(numeric(8,2), x) as xn2, 
 convert(float, x) as xf
from (
 select '1' as x
) a

So far so good.

Obviously, reverse conversion works fine as well:

select 
 cast(1 as varchar) as xv, 
 convert(varchar(10), 1 ) as xv2

Let's step up our game and convert some dates...

Typically, in Oracle, you would do this using to_char and to_date functions:

select
    to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') as cd,
    to_date('2019-01-13 11:22:33','yyyy-mm-dd hh24:mi:ss') as dd
from dual

To do the same in MS SQL you'll probably use convert as in other example above. This also seem to be working fine:

select
 convert( varchar, getDate(), 120 ) as cd,
 convert( datetime, '2019-01-13 11:22:33', 120 ) as dd

120????
What the hell is that?
Oh... Someone decided that using enumerated list is such a great idea! Oh yes. Such a wonderful idea. So... what if we want to use different date format? Oh, that's plenty to choose from! Just take a look:
https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017#date-and-time-styles

Ok, so... let's say we want to start with time, but only hours and minutes and we don't want to separate them at all and then to follow it with date in the format #YYYY#DD#MM# and separate both parts with a + sign?
For example for 08:25, 13th of January 2019 we would like to see something like 0825+#2019#13#01#. Stupid, yes, but why not?
Let's try Oracle:

select
    to_char(sysdate, 'hh24mi+#yyyy#dd#mm#') as stupiddate
from dual

And what have we got in the output?
Voila!
STUPIDDATE      
-----------------
1136+#2019#18#07#

Let's look for an appropriate format in the SQL Server reference...
hmm.... searching... hmm.... doesn't seem like there's anything like that there... No kidding!

Let's be creative then ;) - yeah, MS SQL teches you to be creative hahahahaha

There's many ways to achieve the goal in MS SQL, we can use other format (like 120 used before) and combine substrings, use some other functions extracting date and time components, sky is the limit.... Probably substring version is the easiest one but... it will look just plain ugly and, most likely, some functions will have to be called multiple times (unless you're allowed to convert to string in a subquery).

select
 SUBSTRING(convert( varchar, getDate(), 120 ),12,2)
 +SUBSTRING(convert( varchar, getDate(), 120 ),15,2)+'+#'
 +SUBSTRING(convert( varchar, getDate(), 120 ),1,4)+'#'
 +SUBSTRING(convert( varchar, getDate(), 120 ),6,2)+'#'
 +SUBSTRING(convert( varchar, getDate(), 120 ),9,2)+'#'

Just beautiful.

One thing, although in Oracle you may also run into conversion errors (yup, dot-comma NLS and stuff) you will run into them much, much more often in MS SQL. Guaranteed when dealing with random dates, especially when your stuff has to be executed on databases with random collation settings.

If you know how to deal with this stuff properly, let me know. Let me know also of your troubles with converting datatypes in SQL Server. Hating Oracle? Feel free to leave a comment also!

Best