Skip to main content

Posts

Showing posts from November, 2011

To select last 'N' records in a table

This query will fetch you last N rows from the table-- select * from emp where empno not in (select top ( (select count(*) from emp) - 'N') empno from emp ) or  If you are using ORACLE SQL then the query for the same question is -- select * from ( select rownum as rank,empno,sal from emp ) where rank>((select max(rownum) from emp)-'N');

SQL DATENAME Function - Year, Quarter, Month, Day, Hour, Minute and Milisecond.

It return specifies the part of the date name and the DATENAME for Date Time such as Year, Quarter, Month, Day, Hour, Minute and Milisecond. SQL DATENAME Syntax DATENAME ( datepart , date ) Example SELECT GETDATE() = 2011-11-29 15:33:17.153 SELECT DATENAME(year, GETDATE()) SELECT DATENAME(yy, GETDATE()) SELECT DATENAME(yy, GETDATE()) - It will return value = 2011 SELECT DATENAME(quarter, GETDATE()) SELECT DATENAME(qq, GETDATE()) SELECT DATENAME(q, GETDATE()) -It will return value = 4 (because 1 quarter equal to 3 month,Detail see below table) Month Quarter Value January - March 1 April - June 2 July - September 3 October - December 4 SELECT DATENAME(month, GETDATE()) SELECT DATENAME(mm, GETDATE()) SELECT DATENAME(m, GETDATE()) - It will return value = November SELECT DATENAME(dayofyear, GETDATE()) SELECT DATENAME(dy, GETDATE()) SELECT DATENAME(y, GETDATE()) - It will return value = 333 (this is calculate total day from 1 jan 2007 until 28 nov 2011) SELECT DATENAME(day, GETDATE()) SEL

SQL Statement Example for SELECT, UPDATE and DELETE

Name Income Company Position Angle 4500 Ajax Tech Limited Manager Helen 6900 Programmingschools Limited CEO Micheal 4000 SQLTutorial Limited DBA Nick 5000 Programmingschools Limited Programmer Table Name : Testing SQL STATEMENT - Example Result with SQL Statement Select All data from a table (same as above table)- SELECT * FROM TESTING SELECT NAME,COMPANY FROM TESTING name company Angle Ajax Tech Limited Helen Programmingschools Limited Micheal SQLTutorial Limited Nick Programmingschools Limited SELECT DISTINCT COMPANY FROM TESTING company Ajax Tech Limited Programmingschools Limited SQLTutorial Limited SELECT COUNT(*) FROM TESTING 4 SELECT * FROM TESTING WHERE NAME='HELEN' Name Income Company Position Helen 6900 Programmingschools Limited CEO SELECT * FROM TESTING WHERE NAME='HELEN' AND COMPANY='Programmingschools Limited' Name Income Company Position Helen 6900 Programmingschools Limited CEO SELECT * FROM TESTING WHERE NAME='HELEN' OR COMPANY='P

SQL CAST and CONVERT

It converts an expression from one data type to another. CAST and CONVERT have similar functionality. SQL CAST and CONVERT Syntax Using CAST: CAST ( expression AS data_type ) Using CONVERT: CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) Example of SQL Cast and Convert SQL Cast and Convert - String SELECT SUBSTRING('CAST and CONVERT', 1, 3) Return Value = CAS (it get from index 1 to 3) SELECT CAST('CAST and CONVERT' AS char(3)) Return Value = CAS (it get 3 char only) SQL Cast and Convert - Date Time -Converting date time to character data(vachar) -The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the Without century year(yy). -Add 100 to a style value to get a four-place year that includes the century year(yyyy). -Below is example for converting 1 format of date time to different format of date time, so that it can be use in various condition. Value of current Date Time GETDATE() SELECT (GETDATE()) = 2007-06

MODULO function in SQL Server

SELECT EmployeeID , LastName , EmployeeID % 2 FROM Employees WHERE EmployeeID % 2 = '0' Also we can use Like Statement to achieve this :: Select * from Employees where ID Like '%[02468]' --for even number of records Select * from Employees where ID Not Like '%[02468]' --for Odd number of records