Dream DotNet: Important SQL Query

Important SQL Query

For selecting only date without time

select convert(varchar(10), column name, 120) from Table Name

2011-02-25 21:17:33.933

Result
2011-02-25(getting only date from datetime)

For select only time without date

select convert(varchar(10),getdate(),108)

Result
11:39:58(getting only time from datetime)

For selecting only year

select year(columnName) from TableName

Result
2011(getting only year from datetime)

For combine count of multiple tables

select(select count(*) from table_name1) + (select count(*) from table_name2)+(select count(*) from table_name3)

Result
getting the total count of tables

For selecting date with month name

SELECT DATENAME(MONTH, columnName) + RIGHT(CONVERT(VARCHAR(12), ColumnName, 107), 9) AS [Month DD, YYYY] from TableName

Result
April 04, 2016

For selecting date with month name without comma

SELECT DATENAME(MONTH, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2))
+ ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month DD YYYY]

Result
April 04 2016

For selecting date in different formates

SELECT CONVERT(varchar(11),getdate(),101) -- mm/dd/yyyy

Result
06/21/2016

SELECT CONVERT(varchar(11),getdate(),103) -- dd/mm/yyyy

Result
21/06/2016

select REPLACE(CONVERT(CHAR(10), getdate(), 103), '/', '-') --dd-mm-yyyy

Result
21-06-2016

For select particular character

SELECT LEFT (your_column, number) FROM your_table

Eg:
SELECT LEFT (your_column, 4) FROM your_table

Result
Getting the character based on the number

For select filename without extension

select FileName = REVERSE(SUBSTRING(REVERSE(FileName),
CHARINDEX('.', REVERSE(FileName)) + 1, 999)) from your Column Name

Result
Getting file.pdf as file

For select monthly date

SELECT * FROM table_Name WHERE MONTH(column)=(enter date) AND YEAR(column)=(Enter Year)

Example
SELECT * FROM table_Name WHERE MONTH(column)=4 AND YEAR(column)=2016
Here we are getting the datas from April 2016

For selecting monthly records

SELECT * FROM tablename WHERE Year(columnname) = Year(CURRENT_TIMESTAMP)
AND Month(columnname) = Month(CURRENT_TIMESTAMP)

The above query is used for getting all the records of the current month.

For select time as am,pm format(in railway time)

select columnname =CONVERT(VARCHAR(8), columnname, 108) + ' ' + RIGHT(CONVERT(VARCHAR(30), columnname, 9), 2)
from tablename

Result
13:19 pm

For select time as am,pm format(in normal time)

select CONVERT(varchar(15),CAST(columnname AS TIME),100) from tablename

Result
01:19 pm

For select total days of current month

DECLARE @sample datetime SET @sample=GETDATE() -- Sample date
select datediff(dd,dateadd(dd, 1-day(@sample),@sample), dateadd(m,1,dateadd(dd, 1-day(@sample),@sample)))

Here if the month is January we are getting the result as 31(Total days of January)

For select all sundays of a month

DECLARE @Year AS INT,
@FirstDateOfYear DATETIME,
@LastDateOfYear DATETIME
-- You can change @year to any year you desire
SELECT @year = 2016
SELECT @FirstDateOfYear = DATEADD(yyyy, @Year - 1900, 0)
SELECT @LastDateOfYear = DATEADD(yyyy, @Year - 1900 + 1, 0)
-- Creating Query to Prepare Year Data
;WITH cte AS (
SELECT 1 AS DayID,
@FirstDateOfYear AS FromDate,
DATENAME(dw, @FirstDateOfYear) AS Dayname
UNION ALL
SELECT cte.DayID + 1 AS DayID,
DATEADD(d, 1 ,cte.FromDate),
DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname
FROM cte
WHERE DATEADD(d,1,cte.FromDate) < @LastDateOfYear
)
SELECT FromDate AS Date, Dayname
FROM CTE
WHERE DayName IN ('Sunday')
/*
WHERE DayName IN ('Saturday,Sunday') -- For Weekend
WHERE DayName NOT IN ('Saturday','Sunday') -- For Weekday
WHERE DayName LIKE 'Monday' -- For Monday
WHERE DayName LIKE 'Sunday' -- For Sunday
*/
OPTION (MaxRecursion 370)

Result

Getting all the Sundays of current year

Select sum of time

select cast(SUM(DateDiff(MINUTE, CAST('0:00' as time), columnname))/60 as nvarchar(3)) + 'hr' + cast(SUM(DateDiff(MINUTE, CAST('0:00' as time), columnname))%60 as nvarchar(2))+ 'mins' from tablename

Result
21hr16mins