InterviewSolution
Saved Bookmarks
InterviewSolution
| 1. |
some good sql query |
|
Answer» ---How to get number of days in a month select CASE WHEN MONTH(getdate()) IN (1, 3, 5, 7, 8, 10, 12) THEN 31 WHEN MONTH(getdate()) IN (4, 6, 9, 11) THEN 30 ELSE CASE WHEN (YEAR(getdate()) % 4 = 0 AND YEAR(getdate()) % 100 != 0) OR (YEAR(getdate()) % 400 = 0) THEN 29 ELSE 28 END end ---How to get number of row in each table in database SELECT obj.NAME, ind.rowcnt FROM sysindexes AS ind INNER JOIN sysobjects AS obj ON ind.id = obj.id WHERE ind.indid < 2 AND OBJECTPROPERTY(obj.id, 'IsMSShipped') = 0 ORDER BY obj.NAME ---How to select all duplicate records from a table select Coloumn_name, count(Coloumn_name) from Table_Name group by field having count(*) > 1 ---How to get first DAY of current week (Sunday) select DATEADD(DD, 1 - DATEPART(DW, getdate()),getdate()) ---How to get first day of current week (Monday) select DATEADD(DD, 1 - DATEPART(DW, getdate()),getdate()+1) ---How to get number of MAXIMUM connection can be establish to SQL SELECT MAX_CONNECTIONS ----query to convert all the letters in a word to upper case SELECT UPPER('test') ----round up the values of a number SELECT CEILING (7.1) ---How to get first day of Month SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1,GETDATE())) AS FirstDay ---How to know how many tables contains column_name as a column in a database SELECT COUNT(*) AS Counter FROM syscolumns WHERE (name = 'column_name') |
|