Nearest Time in sqlserver

/*CREATE FUNCTION RoundToNearest15
(
@Date SMALLDATETIME
)

RETURNS SMALLDATETIME
AS
*/
DECLARE @Date SMALLDATETIME=GETDATE()
BEGIN

DECLARE @Miniute TINYINT
DECLARE @Mult SMALLINT
DECLARE @Value TINYINT

SET @Miniute = DATEPART(MI,@Date)
SET @Value = 0
SET @Mult = 1

IF @Miniute < 8 BEGIN SET @Mult = -1 SET @Value = @Miniute END IF @Miniute BETWEEN 16 AND 22 BEGIN SET @Mult = -1 SET @Value = @Miniute - 15 END IF @Miniute BETWEEN 31 AND 37 BEGIN SET @Mult = -1 SET @Value = @Miniute - 30 END IF @Miniute BETWEEN 46 AND 52 BEGIN SET @Mult = -1 SET @Value = @Miniute - 45 END IF @Miniute BETWEEN 8 AND 14 SET @Value = 15 - @Miniute IF @Miniute BETWEEN 23 AND 29 SET @Value = 30 - @Miniute IF @Miniute BETWEEN 38 AND 44 SET @Value = 45 - @Miniute IF @Miniute BETWEEN 53 AND 59 SET @Value = 60 - @Miniute --SELECT DATEADD(MI,-15,DATEADD(MI,@Value * @Mult,@Date)) --SELECT DATEADD(MI,@Value * @Mult,@Date) --SELECT DATEPART(MINUTE,@Date ) END --select convert(varchar(10),getdate(),108) --SELECT Convert( VarChar( 10 ), GETDATE(), 114 ) --SELECT substring(convert(varchar(10), GETDATE(), 108),1,2) SELECT cast( case when cast(substring(convert(varchar(10),DATEADD(MI,-15,DATEADD(MI,@Value * @Mult,@Date)), 108),1,2) as integer) > 12 then
CAST(cast(substring(convert(varchar(10),DATEADD(MI,-15,DATEADD(MI,@Value * @Mult,@Date)), 108),1,2) as integer)-12 AS VARCHAR)+
substring(convert(varchar(10),DATEADD(MI,-15,DATEADD(MI,@Value * @Mult,@Date)), 108),3,3)
when
substring(convert(varchar(10),DATEADD(MI,-15,DATEADD(MI,@Value * @Mult,@Date)), 108),1,2)='00' then
CAST(12 AS VARCHAR)+substring(convert(varchar(10),DATEADD(MI,-15,DATEADD(MI,@Value * @Mult,@Date)), 108),3,3)
else
substring(convert(varchar(10),DATEADD(MI,-15,DATEADD(MI,@Value * @Mult,@Date)), 108),1,5)
end
as varchar(10))+
case
when
cast(substring(convert(varchar(10),DATEADD(MI,-15,DATEADD(MI,@Value * @Mult,@Date)), 108),1,2) as integer) > 12 then
' pm'
else
' am'
end
as time1,
cast(
case
when
cast(substring(convert(varchar(10),DATEADD(MI,@Value * @Mult,@Date), 108),1,2) as integer) > 12 then
CAST(cast(substring(convert(varchar(10),DATEADD(MI,@Value * @Mult,@Date), 108),1,2) as integer)-12 AS VARCHAR)+
substring(convert(varchar(10),DATEADD(MI,@Value * @Mult,@Date), 108),3,3)
when
substring(convert(varchar(10),DATEADD(MI,@Value * @Mult,@Date), 108),1,2)='00' then
CAST(12 AS VARCHAR)+substring(convert(varchar(10),DATEADD(MI,@Value * @Mult,@Date), 108),3,3)
else
substring(convert(varchar(10),DATEADD(MI,@Value * @Mult,@Date), 108),1,5)
end
as varchar(10))+
case
when
cast(substring(convert(varchar(10),DATEADD(MI,@Value * @Mult,@Date), 108),1,2) as integer) > 12 then
' pm'
else
' am'
end
as time2

Dynamic field name and data type selection in sqlserver 2005/2008

DECLARE @ListXML VARCHAR(MAX),@TableName VARCHAR(50)
SET @ListXML=''
SET @TableName='T_SAMPLE'-- MENTION UR TABLE NAME HERE

SELECT @ListXML = (SELECT CASE WHEN DATA_TYPE IN('datetime','smalldatetime') THEN 'ISNULL(CONVERT(VARCHAR(10),'+CONVERT(VARCHAR,COLUMN_NAME)+',101),''''),'
WHEN DATA_TYPE IN('bit','int') THEN 'ISNULL(CAST('+CONVERT(VARCHAR,COLUMN_NAME)+' AS VARCHAR),''''),'
ELSE 'ISNULL(CAST('+CONVERT(VARCHAR,COLUMN_NAME) +' AS VARCHAR('+CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')),''''),' END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN(@TableName) FOR XML PATH(''))

SELECT @ListXML

Find PRIMARY KEY column name and table name in sqlsever

select a.Table_Name, a.Column_Name from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE a,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
where b.constraint_type='PRIMARY KEY' and a.constraint_name = b.constraint_name
--and a.table_name='T_8KCompany'
order by a.table_name, a.column_name

Pivot sample Query in sqlserver

create table #temp
(CustomerID int ,[date] datetime ,
SubTotal varchar(20))

select * from #temp
--insert into #temp values(4,getdate(),34)
SELECT * FROM

(SELECT CustomerID, DATEPART(m, date) OrderMonth, SubTotal

FROM #temp

WHERE date between '20030101' and '20101231'

and CustomerID IN (2,4,6,7,8,9)) src

PIVOT (SUM(SubTotal) FOR OrderMonth

IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS pvt

GO

month of week of first day and last date using sqlserver

declare @end_week datetime,@week_number int
declare @start_week datetime,@today datetime
set @today=GETDATE()
set @week_number=2
set @today = dateadd(wk, @week_number- datepart(wk,@today), @today)
set @start_week = dateadd(d, 1 - DATEPART(dw, @today), @today)
set @end_week = dateadd(d,6,@start_week )
select @start_week,@end_week