Sql date time

To show current date in Sql
-------------------------------
SELECT GETDATE()
-------------------------------
To Show only the date part
---------------------------------------------------------------------------------------------------------
Select CAST(DATEDIFF(Day, 0, GETDATE()) AS DATETIME)
---------------------------------------------------------------------------------------------------------
OR
--------------------------------------------------------------------------------------------------

SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))

--------------------------------------------------------------------------------------------------

Works on All Sql Versions

To Show Difference between dates
---------------------------------------------------------------------------------------------
DATEDIFF ( datepart , startdate , enddate ) 
---------------------------------------------------------------------------------------------

datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

If the return value is out of range for int (-2,147,483,648 to +2,147,483,647), an error is returned

For different Format : http://msdn.microsoft.com/en-us/library/ms186724.aspx

SQL Server used Cast or Convert function to Format DateTime value or column into a specific date format.Both function are used to convert datetime to varchar or string.

CAST function Syntax: CAST(expression as data_type)

Let's convert current date time to varchar

select cast(getdate() as varchar)

CONVERT function is used to change or convert the DateTime formats.By using convert function you can get only Date part or only Time part from the datetime.

CONVERT Function Syntax: CONVERT(data_type,expression,date Format style)

Let's take Sql Server DateTtime styles example:

Format	Query
USA mm/dd/yy	select convert(varchar, getdate(), 1)
ANSI yy.mm.dd	select convert(varchar, getdate(), 2)
British/French dd/mm/yy	select convert(varchar, getdate(), 3)
German dd.mm.yy	select convert(varchar, getdate(), 4)
Italian dd-mm-yy	select convert(varchar, getdate(), 5)
dd mon yy	select convert(varchar, getdate(), 6)
Mon dd, yy	select convert(varchar, getdate(), 7)
USA mm-dd-yy	select convert(varchar, getdate(), 10)
JAPAN yy/mm/dd	select convert(varchar, getdate(), 11)
ISO yymmdd	select convert(varchar, getdate(), 12)
mon dd yyyy hh:miAM (or PM)	select convert(varchar, getdate(), 100)
mm/dd/yyyy	select convert(varchar, getdate(), 101)
yyyy.mm.dd	select convert(varchar, getdate(), 102)
dd/mm/yyyy	select convert(varchar, getdate(), 103)
dd.mm.yyyy	select convert(varchar, getdate(), 104)
dd-mm-yyyy	select convert(varchar, getdate(), 105)
dd mon yyyy	select convert(varchar, getdate(), 106)
Mon dd, yyyy	select convert(varchar, getdate(), 107)
hh:mm:ss	select convert(varchar, getdate(), 108)
Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)	select convert(varchar, getdate(), 109)
mm-dd-yyyy	select convert(varchar, getdate(), 110)
yyyy/mm/dd	select convert(varchar, getdate(), 111)
yyyymmdd	select convert(varchar, getdate(), 112)
Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)	select convert(varchar, getdate(), 113) or select convert(varchar, getdate(), 13)
hh:mi:ss:mmm(24h)	select convert(varchar, getdate(), 114)