Skip to main content

Posts

Showing posts from June, 2011

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
SELECTCONVERT (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
SELECTCONVERT(VARCHAR,GETDATE(),102)
--select to_char(sysdate,'yyyy.MM.dd') from dual�…

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)

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