Sql Server

using datediff function in sql

Posted on May 14, 2008. Filed under: Sql Server |

 select datediff(year,‘Nov 30 2001′,getdate()) as diffyear 
select datediff(month,‘Nov 30 2001′,getdate())as diffmonth
 select datediff(day,‘Nov 30 2001′,getdate()) as diffdaydiffyear
 diffyear
———–
 7 (1 row(s) affected)
diffmonth
———–
78(1 row(s) affected)
diffday
———-
2357(1 row(s) affected)

using datename function for name of month,day

Posted on May 14, 2008. Filed under: Sql Server |

select DATENAME(w, GETDATE()) AS ‘ Day Name’
day name
———
Wednesday
SELECT DATENAME(m, GETDATE()) AS ‘ month Name’
month name
———
may
 
 
 

Using Dateadd function adding day,month,year in date

Posted on May 14, 2008. Filed under: Sql Server |

select

dateadd(datepart,number,date)
eg-

select

DATEADD(month, 3, getdate()) 

Datepart

|Abbreviations
year| yy,yyyy 
quarter| qq,q 
month| mm,m 
dayofyear| dy,y 
day| dd,d 
week| wk,ww 
weekday| dw, w 
hour| hh
minute| mi,n 
second| ss, s

using datepart function get different output

Posted on May 6, 2008. Filed under: Sql Server |

selectdatepart(year,getdate())
selectdatepart(month,getdate())
select datepart(day,getdate())
select datepart(hour,getdate())
select datepart(minute,getdate())
select datepart(second,getdate())
select datepart(millisecond,getdate())
select datepart(weekday,getdate())
select datepart(dayofyear,getdate())
select datepart(quarter,getdate())

using convert function filter time or date only from datetime column

Posted on May 6, 2008. Filed under: Sql Server |

SELECT
 

convert(char(11), getdate(),109)
May  6 2008
SELECT
 

convert(char(8), getdate(),10 12:51:32
and so on u can change according input different char length and style value
 
 

converting Date into string in different style

Posted on May 6, 2008. Filed under: Sql Server |

hi
i submit for converting date into string in different style by help of sql server help?
select

CONVERT(varchar,CONVERT(datetime,getdate()),106) as date
output
06 May 2008
other style u want help like this
0 or 100 | Default |mon dd yyyy hh:miAM (or PM)
 101 | U.S.| mm/dd/yyyy
 102 | ANSI |yy.mm.dd
 103|British/French|dd/mm/yy
 104|German|dd.mm.yy
 105|Italian|dd-mm-yy
 106 (1)|-|dd mon yy
 107 (1)|-|Mon dd, yy
 108|-  |hh:mm:ss
 9 or 109 (1, 2) |Default + [...]

display custum text using case in sqlserver

Posted on April 11, 2008. Filed under: Sql Server |

SELECT FirstName, LastName,
       CASE WHEN sex =’M’ THEN ‘MALE’
            WHEN sex = ’F’ THEN ‘FEMALE’
            ELSE ‘Undefined’
       END As Sex
FROM Employees

find Nth highest salary of employee by procedure

Posted on April 11, 2008. Filed under: Sql Server |

Create proc GetNthmaxsalary
(
@high int
)
as
set nocount on
SELECT TOP 1 emp1.salary FROM

(

SELECT DISTINCT TOP @high salary FROM

emp

ORDER BY salary DESC) as

emp1

ORDER BY emp1.salary

go