---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') | |