Different Date Formats in T-SQL and PL/SQL SQL Server

Here i have share different Date formats in T-SQL and PL/SQL you can use it to display your date according to your requirements .
Remember dates format should not be used in 'where' condition you must cast it as datetime (for T-SQL)

for example if you want to remove time part you can use  like this
SELECT CONVERT (VARCHAR,GETDATE(),101) it will show you only date
 but if you want to compare in 'where' condition you must cast it as datetime like this:

SELECT cast( CONVERT (VARCHAR,GETDATE(),101) as datetime)

Because without casting it will  act as varchar and in different scenarios like 'between'  , > <  it will not give you the correct result.


--Format 'MM/dd/yyyy'    2011-06-20 00:00:00.000
SELECT CONVERT (VARCHAR,GETDATE(),101) --t-sql
--select to_char(sysdate,'MM/dd/yyyy') from dual    --pl/sql

--Format 'yyyy.MM.dd' 2011-06-20 00:00:00.000
SELECT CONVERT (VARCHAR,GETDATE(),102)
--select to_char(sysdate,'yyyy.MM.dd') from dual   --pl/sql

--Format 'dd/MM/yyyy'  20/06/2011
SELECT CONVERT (varchar,GETDATE(),103)


--select to_char(sysdate,'dd/MM/yyyy') from dual  --pl/sql

--Format 'dd.MM.yyyy' 20.06.2011
SELECT CONVERT (varchar,GETDATE(),104)
--select to_char(sysdate,'dd.MM.yyyy') from dual   --pl/sql

--Format 'dd-MM-yyyy' 20-06-2011
SELECT CONVERT (varchar,GETDATE(),105)
--select to_char(sysdate,'dd-MM-yyyy') from dual  --pl/sql

--Format 'dd MMM yyyy' 20 Jun 2011
SELECT CONVERT (varchar,GETDATE(),106)

--Format 'MMM dd,yyyy' Jun 20, 2011
SELECT CONVERT (varchar,GETDATE(),107)


--Format  'hh:mm:ss' 21:42:43
SELECT CONVERT (varchar,GETDATE(),108)
--select to_char(sysdate,'hh:mm:ss') from dual   --pl/sql
--Format 'MMM dd yyyy hh:mm:ss' Jun 20 2011  9:42:52:133PM
SELECT CONVERT (varchar,GETDATE(),109)

--Format 'MM-dd-yyyy' 06-20-2011
SELECT CONVERT (varchar,GETDATE(),110)
--select to_char(sysdate,'MM-dd-yyyy') from dual   --pl/sql

--Format 'yyyy/MM/dd' 2011/06/20
SELECT CONVERT (varchar,GETDATE(),111)
--select to_char(sysdate,'yyyy/MM/dd') from dual   --pl/sql



--Format 'yyyyMMdd' 20110620
SELECT CONVERT (varchar,GETDATE(),112)
--select to_char(sysdate,'yyyyMMdd') from dual  --pl/sql

--Format 'dd MMM yyyy hh:mm:ss:mss' 20 Jun 2011 21:43:33:343
SELECT CONVERT (varchar,GETDATE(),113)

--Format 'hh:mm:ss:ss'  21:43:50:203
SELECT CONVERT (varchar,GETDATE(),114)
--select to_char(sysdate,'hh:mm:ss:ss') from dual     --pl/sql

Oracle(PLSQL) to SQL(T-SQL) server Migration

Currently I am working on Migration Project (oracle to sql),during this project I have faced different syntax issues,  here is some simple example where i showed different  queries in PLSQL and T-SQL.

inshALLAH i will add more queries .
                                 

Oracle	SQL SERVER
SELECT  ‘x’ FROM dual	SELECT ‘x’
SELECT  TRUNC(15.79,1) "Truncate" FROM DUAL;	SELECT ROUND(15.79, 0) rounded , ROUND(15.79,0,1)  SELECT FLOOR(ROUND(15.79, 0)), FLOOR(ROUND(15.79, 0,1) )
select isnull(anyvalue,0)	select NVL(anyvalue,0) from dual
SELECT to_char(123.45 ,99999999999999) from dual SELECT to_char(EXPIRY_DATE,'DDMONYYYY') from dual	SELECT STR(123.45, 14) SELECT STR(round(123.455 , 2),12,2) SELECT CAST(REPLACE((CONVERT(varchar(12) , EXPIRYDATE, 106 )),' ' , '') as varchar(9))
Select ‘Name’ || ‘Last Name’ From tableName	Select ‘Name’ + ‘Last Name’
SELECT sysdate -add_months(sysdate,12) FROM dual	SELECT  datediff(dd, GetDate(),dateadd(mm,12,getdate()))
Select decode(‘a’,’a’,’write a’)	Case when ‘a’ = ‘a’ then ‘write a’ end

Click here for Different date formates in oracle and sql server

Crystal Report toWords() function display amount in words with decimal values

I have to display amount in Words with decimal values in my report for achieving this i wrote small formula in Crystal Report 

here is the formula:

numbervar Value;

numbervar deciValue;

Value:={ES_RPT_SP_AdvPaymentPRC;1.RECEIVED_AMOUNT};

deciValue:= Value- Truncate(Value);  //separate decimal value

Value := truncate(Value);

deciValue:= deciValue* 100;

if deciValue= 0 then

propercase(ToWords (Value,0) +' only')

else

propercase(ToWords (Value,0) + ' And '+ToWords(deciValue,0) + ' Paisas Only ')

here is the output for 2222.44

Crytal Reports Date Format custom 01st April 2011

I have to display date in following format  02nd May 2011 for achieving this :

right click on datefield click on format object

click on datetime tab then press customize button

  

write this formula  on highlighted formula editor

IF          DATEPART("d",{Nast_Sp_Multiple;1.empDOB}) IN [1,21,31]  THEN "st "

ELSE IF     DATEPART("d",{Nast_Sp_Multiple;1.empDOB}) IN [2,22]     THEN "nd "

ELSE IF     DATEPART("d",{Nast_Sp_Multiple;1.empDOB}) IN [3,23]     THEN "rd "

ELSE "th "

Now you will get date in following format 

01st December 1990

02nd May 2009