Dates Explained

\ by

Here are some of the ways dates can be output.

declare @date datetime
set @date = getdate()
select 
	@date	as SampleDate,
	datepart(year,@date)	as 'year',
	datepart(month,@date) 	as 'month',
	datepart(day,@date) 	as 'day',
	datepart(week,@date) 	as 'week',
	datepart(quarter,@date) as 'quarter',
	datepart(dy,@date) 	as 'day of year',
	datepart(weekday,@date) as 'weekday',
	datepart(hour,@date) 	as 'hour',
	datepart(minute,@date) 	as 'minute' ,

	datename(month,@date) as 'month name',
	datename(weekday,@date) as 'weekday name',

	'fiscal year' =   
	case            
		when datepart(month,@date) >= 7
		then datepart(year,@date) + 1
		else datepart(year,@date)
	end,
	CONVERT(varchar(12), getdate() ,112) 	as 'today date as integer',
	CONVERT(varchar(12),DATEADD(d, -7 ,getdate()),112) 	as 'lastweek date as integer',
	CONVERT(varchar(12), getdate() ,108) 			as 'time',
	REPLACE(CONVERT(varchar(12), getdate() ,108),':','') 	as 'time as integer',
	dateadd(d,-1, cast(month(dateadd(mm,1,getdate())) as varchar) + 
		'/01/' + cast(year(getdate()) as varchar)) as [Last day of month],
	DATEADD(D,1, 
		cast(month( getdate() ) as varchar) + '-' +
		cast(day( getdate()) as varchar) + '-' +
		cast(year(getdate() ) as varchar) ) as [Midnight Tonight]

declare @tim varchar(8)
set @tim = REPLACE(CONVERT(varchar(12), getdate() ,108),':','')
select @tim,
case when len(@tim) >= 5 then
	substring(@tim,len(@tim)-5,2 ) + ':' +
	substring(@tim,len(@tim)-3,2 ) + ':' +
	substring(@tim,len(@tim)-1,2 )
else
	@tim
end
Posted in: Coding, SQL

Where are tables located in database files?

\ by

This uses indexes to locate the files.


SELECT  [o].[crdate],
        DATEDIFF(MINUTE, [o].[crdate], GETDATE()) AS [MinAgo],
        [o].[name] AS [Tablename],
        [i].[indid],
        [i].[name] AS [IndexName],
        [i].[rowcnt],
        [i].[groupid],
        [f].[name] AS [FileGroupName],
        [d].[file_id],
        [d].[physical_name],
        [s].[name] AS [DataSpace]
FROM    [sys].[sysobjects] [o]
JOIN    [sys].[objects] [so]
ON      [so].[object_id] = [o].[id]
JOIN    [sys].[sysindexes] [i]
ON      [i].[id] = [o].[id]
JOIN    [sys].[filegroups] [f]
ON      [f].[data_space_id] = [i].[groupid]
JOIN    [sys].[database_files] [d]
ON      [f].[data_space_id] = [d].[data_space_id]
JOIN    [sys].[data_spaces] [s]
ON      [f].[data_space_id] = [s].[data_space_id]
WHERE   [is_ms_shipped] = 0
        AND [i].[name] IS NOT NULL
ORDER BY [Tablename],
        [IndexName],
        [d].[file_id]
Posted in: Coding, Configuration, SQL

SQL: Where are the indexes located?

\ by

Where are the indexes located?

SELECT  'table_name' = OBJECT_NAME(i.id),
        i.indid,
        'index_name' = i.name,
        i.groupid,
        'filegroup' = f.name,
        'file_name' = d.physical_name,
        'dataspace' = s.name
FROM    sys.sysindexes i,
        sys.filegroups f,
        sys.database_files d,
        sys.data_spaces s
WHERE   OBJECTPROPERTY(i.id, 'IsUserTable') = 1
        AND f.data_space_id = i.groupid
        AND f.data_space_id = d.data_space_id
        AND f.data_space_id = s.data_space_id
        AND i.name LIKE 'ndx%'
ORDER BY f.name,
        OBJECT_NAME(i.id),
        groupid

g

Posted in: Coding, Configuration

SQL: Last Day Of Month

\ by

For any date go back a month, then add a month to it.

SELECT  [dtMonthLastDay] = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1)

Last day for any month etc. by Pinal Dave

----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth
----Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth
----Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
LastDay_NextMonth
Posted in: Coding