Monday, June 20, 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
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





Thursday, June 16, 2011

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

Wednesday, June 8, 2011

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




Asp Classic Upload File Issue

Recently I have migrated asp classic application from old server to new server . I found file upload is not working in new IIS 7 Server . I ...