No One is Perfect

Know your Identity

Often we create tables that have identity columns. An identity column is a special column in SQL Server that can automatically take on values when an insert operation is done on the table. What if you want to find out whether a table contains an identity column?? SQL Server stores this information in the syscolumns table, but you can access this information in many ways. In this article, we will see the various ways to access information about whether a table has an identity column.

Before we start off with the methods, let's first create a table that has an identity column, like this:

CREATE TABLE samp_test
(
colA INT IDENTITY,
colB VARCHAR(10)
)

Method A

In the first method, we will fetch this information from the syscolumns table. This table contains a column called status that contains various values. If the value in this column contains hexadecimal 80, then it means that the corresponding column contains an identity column. Here is how we can check this:

IF (EXISTS (SELECT name FROM syscolumns WHERE
OBJECT_NAME(id) = 'samp_test' AND status = 0x80))
PRINT 'Table has an identity column'
ELSE
PRINT 'Table does not have an identity column'

Method B

In the second method, we will see whether a table has an identity column using the COLUMNPROPERTY function. This function takes an argument IsIdentity that indicates whether the column is an identity column. Here is how we can do the check using this function:

IF (EXISTS (SELECT myTemp.IsIdentity FROM
(SELECT IsIdentity=COLUMNPROPERTY(id, name, 'IsIdentity')
FROM syscolumns WHERE OBJECT_NAME(id) = 'samp_test') AS myTemp
WHERE myTemp.IsIdentity = 1))
PRINT 'Table has an identity column'
ELSE
PRINT 'Table does not have an identity column'

This method is slightly convoluted since the COLUMNPROPERTY function takes the name of the column as the parameter. Thus, if we want to make a generic sort of a call, we need to iterate through each column of a table using the syscolumns table and then make a call to the COLUMNPROPERTY function. Finally we then run an EXISTS check over this derived table to check for identity.

Method C

In the final method, we will use the OBJECTPROPERTY function to check whether a table has an identity column. Here is how we can do the check using this function:

IF (OBJECTPROPERTY(OBJECT_ID('samp_test'), 'TableHasIdentity') = 1)
PRINT 'Table has an identity column'
ELSE
PRINT 'Table does not have an identity column'

This is the easiest of the lot. The OBJECTPROPERTY function takes as parameter the name of the object to check (a table in this case) and then the name of the property. We use the OBJECT_ID function to get the ID of the object. We then use the TableHasIdentity property to check if the table has an identity column. One difference between this method and the others is that this method does not give the name of the column which is the identity column, which is possible using the other two methods.

DECLARE @Date datetime
SET @Date = '2001/08/31'
SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 1),@Date) AS 'First day of the week'
SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 7),@Date) AS 'Last day of the week'
SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,@Date)),DATEADD(m,1,@Date))) AS 'Last day of the month'

==============================================================================================================

--To get the first day of month and day name by any date
Declare @pInputDate Datetime
set @pInputDate=getdate()- 40
set @pInputDate= CAST(CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' +
CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01' AS DATETIME)
--Will give First date of the month

--Will give day of the date
select @pInputDate AS First_Day_of_mth, datename(dw,@pInputDate) AS Day_Name
==============================================================================================================
Hi,

here is the Table:

Id Saledate Sales
1 2 Jan 2006 100
1 13 Jan 2006 200
1 14 Feb 2006 300
2 3 Feb 2006 150
2 4 Feb 2006 200
3 5 Jan 2006 300
3 13 Feb 2006 400

I need the following output?

Id Jan Feb Mar Apr
1 300 300 0 0
2 0 350 0 0
3 300 400 0 0

--Here is the result:

SELECT ID, SUM(CASE WHEN MONTH(saledate) = 1 THEN sales ELSE 0 END) AS 'Jan',
SUM(CASE WHEN MONTH(saledate) = 2 THEN ISNULL(sales,0) ELSE 0 END) AS 'Feb',
SUM(CASE WHEN MONTH(saledate) = 3 THEN ISNULL(sales,0) ELSE 0 END) AS 'Mar',
SUM(CASE WHEN MONTH(saledate) = 4 THEN ISNULL(sales,0) ELSE 0 END) AS 'Apr'
FROM sale
GROUP BY ID
--Here it is shown only for 4 months. You can extend this query for complete year.

-- It gives maximum sales in each month
select id as ID ,
MAX(case when month(saledate) =1 then isnull(sales,0) else 0 end) as 'Jan',
MAX(case when month(saledate) = 2 then isnull(sales,0) else 0 end) as 'Feb',
Max(case when month(saledate) = 3 then isnull(sales,0) else 0 end) as 'Mar',
Max(case when month(saledate) = 4 then isnull(sales,0) else 0 end) as 'Apr',
Max(case when month(saledate) = 5 then isnull(sales,0) else 0 end) as 'May',
Max(case when month(saledate) = 6 then isnull(sales,0) else 0 end) as 'June',
Max(case when month(saledate) = 7 then isnull(sales,0) else 0 end) as 'July',
Max(case when month(saledate) = 8 then isnull(sales,0) else 0 end) as 'Aug',
Max(case when month(saledate) = 9 then isnull(sales,0) else 0 end) as 'Sept',
Max(case when month(saledate) = 10 then isnull(sales,0) else 0 end) as 'Oct',
Max(case when month(saledate) = 11 then isnull(sales,0) else 0 end) as 'Nov',
Max(case when month(saledate) = 12 then isnull(sales,0) else 0 end) as 'Dec'
from sale
group by month(saledate),id

SELECT
DAY('5/5/2007'),
MONTH('5/1/2007'),
YEAR('5/1/2007'),
DATEPART(DAY, '1/5/2007'),
DATEPART(MONTH, '5/1/2007'),
DATEPART(YEAR, '1/1/2007')

Day MOnth year datepart_Day datepart_MOnth datepart_year
======-- ======-- ======-- ======--- ======----- ======----
5 5 2007 5 5 2007

SELECT
GETDATE() AS local_date,
GETUTCDATE() AS UTC_date

local_date UTC_date
============----- ============-----
2007-05-08 19:12:50.687 2007-05-08 13:42:50.687

SELECT DATEADD(MONTH, 6, GETDATE())AS '6_months_from_now'

6_months_from_now
============-----
2007-11-08 19:12:50.687
==============================================================================================================
declare @datevar datetime
select @datevar = getdate()

/*Example for getdate() : getting current datetime*/
select getdate() [Current Datetime]

/*Example for dateadd : getting date 7 days from current datetime*/
select dateadd(dd, 7, @datevar) [Date 7 days from now]

/*Example for datediff : getting no of days passed since 01-01-2004*/
select datediff(dd,'20040101',@datevar) [No of days since 01-01-2004]

/*Example for datename : getting month name*/
select datename(mm, @datevar) [Month Name]

/*Example for datepart : getting week from date*/
select datepart(wk, @datevar ) [Week No]

/*Example for day : getting day part of date*/
select day (@datevar) [Day]

/*Example for month : getting month part of date*/
select month(@datevar) [Month]

/*Example for year : getting year part of date*/
select year(@datevar) [Year]

/* Getting the Day Name like monday tuesday... */
SELECT DATENAME(dw, GETDATE())
-- or
/* 0-monday,1-tuesday,2-wednesday ....7-monday,8-tuesday... */
SELECT DATENAME(dw, 0)

==============================================================================================================