I have below oracle to SQL conversion, but not wqorking , think something is wrong with my statement, any help would be great! The NVL stands for Null value logic which I replaced with ISNULL. the statment is part of select statement.
Thanks,
Pasi
Blockquote
,(TO_CHAR(NVL(ec.REHIRE_DT, ec.HIRE_DT ), 'YYYYMMDD') ) as HireDT ----Oracle
,convert(ISNULL(varchar(10),convert(datetime, ec.REHIRE_DT,ec.HIRE_DT)), 101) as HireDT -----SQL
Blockquote
convert(varchar(8), ISNULL(ec.REHIRE_DT,ec.HIRE_DT), 112) as HireDT -----SQL
Thanks Scott! looks better!
Pasi.
Scott, I have the following as well and looks like its working, any way to shorten this statement?: I am not sure if I really need the datetime in my statement?
Thanks
, (TO_CHAR(ec.BIRTHDATE, 'YYYYMMDD') ) As BDATE ---Oracle
,convert(varchar(10),convert(datetime, ec.BIRTHDATE), 101) as BDATE
In convert, code 101 will return mm/dd/yyyy. If you want yyyymmdd, you must use code 112 as I used in my earlier code.
You're right: you don't need the cast to datetime.
OK when I use below code I still get the date time format like mm/dd/yyyy I took away the datetime
convert(varchar(10),convert(datetime, ec.BIRTHDATE), 101) as BDATE --original
Convert(varchar(8),ec.BIRTHDATE, 112) as BDATE modified.---> this gives me below not in yyyymmdd format??
06/28/19
10/29/18
04/09/18
It's getting modified again somewhere else, probably in the app display. Format 112 is definitely YYYYMMDD.
Is there a reason why you convert to varchar(8) instead of char(8)? Format 112 will always return 8 characters - and if either of the date columns are NULL you will get NULL returned.
What datatype is ec.BIRTHDATE? The column name suggests it is already a Date, or perhaps a Datetime, therefore should be no need to convert to datetime (even if it is a DATE, rather than DATETIME, that will be fine for use in all Date / Datetime functions)
I've had issues repeatedly where a char(nn) causes truncation in a data value, either in the original code or in later changes. I've found, for me at least, thus that it's much safer to use varchar(8) rather than char(8).
Thanks Scott for education! I really appreciate.
Pasi
Interesting. I must admit that we don't have hardly any CHAR columns, because a) pretty much everything is variable width and b) because of that we forget to use CHAR when it IS fixed width!
So I tend to, incorrectly, use VARCHAR for fixed width.
Just maybe?? the ability to know if the user actually intended to have a SPACE on the end of something, or not, is helpful to me - rather than everything being space-padded (or no-space-padded)
But I'm clutching at straws, many/most? Browser Forms field types won't store trailing spaces, or even leading spaces in some circumstances, so in those even if the user wanted to have a trailing space ... they can't ...
For very short columns and/or known-width columns, such as ssn, use char(n) because it saves the overhead of varchar (varchar requires two extra bytes per column to store the actual length).
ssn char(9) --don't store dashes, wastes space
zip char(5) --or zip(9) if you (almost) always look up and use the full 9 digit (U.S.) zip; don't store the -, ditto
scac char(4) /* scac code can be only 2 or 3 bytes, but that's very rare */
country_code char(3) --or char(2) if you insist on using the shorter code (we use char(3))
etc.
Great post you guys always learning!
Thanks!
We use the ISO 2-character country code (You Americans are more insular than us!!) ...
... but we store it in char(3) column so that we can use [made up] 3-char codes where ISO doesn't have a code at all (3-char has no possible conflict with any future 2-char code)
varchar here ... we store foreign addresses 
and their postcodes ...
We use iso-standard 3-char country codes. Often easier to "guess" the correct country than 2-char.
Not sure what issues with truncation you would have when converting a date to its string representation. I have never seen any issues with converting to char(8) or char(10) for dates when all I want is the date in YYYY-MM-DD or YYYYMMDD format...
The only time I really use that anymore - since I can just cast as DATE which is much better than converting to a string is to get a monthly period for grouping the data. Then I will use either CHAR(6) or CHAR(7) depending on which format I need...
Hehehe ... yup, I'll never be able to tell you the 2-char codes codes for Lichtenstein and Lithuania without looking them up, but Austria and Australia would be OK 
Heck! that's what the database lookup SELECT list is for ...
Sorry, I wasn't clear. I've never had a problem with truncation, just through habit & laziness I use VARCHAR rather than CHAR
SELECT CONVERT(varchar(11), GetDate(), 113)
because I want a formatted date for presentation (without time)
Although i suppose, for presentation rather than storage, maybe it makes little or no difference?
No, obviously not, It's when you do additional concatenations to that fixed-length string. Perhaps you won't have any problems. Maybe it was a bug in an older version of SQL. For me personally, I will probably stick with using varchar() so I don't risk it, at least for now.
That's a good point, as a varchar-user and mis-user! I've never stumbled over that, but this for example could catch someone out:
DECLARE @string1 char(20), @string2 char(20)
SELECT @string1 = 'ABC123', @string2 = 'XYZ789'
SELECT [@string1]=@string1, [@string2]=@string2
--
SELECT @string1 = @string1 + @string2
SELECT [@string1]=@string1
@string1 @string2
-------------------- --------------------
ABC123 XYZ789
@string1
--------------------
ABC123
Whereas:
DECLARE @string1 varchar(20), @string2 varchar(20)
SELECT @string1 = 'ABC123', @string2 = 'XYZ789'
SELECT [@string1]=@string1, [@string2]=@string2
--
SELECT @string1 = @string1 + @string2
SELECT [@string1]=@string1
@string1 @string2
-------------------- --------------------
ABC123 XYZ789
@string1
--------------------
ABC123XYZ789
There would be a risk [here] that someone would do an @Variable concatenation without realising that it was a fixed-width CHAR