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