Datepart function

Question

Can someone explain this to me ..

Answer 1

The table RETAILER is altered to add a check constraint on the column CASH_EXP_DATE so that the day of the date component is 1 for the data being entered. For e.g. DATEPART(DAY, '2012-01-31') is 31.

The column would not allow dates that do not have 1 as the day. For better understanding, go through the links below.

CHECK CONSTRAINT

DATEPART

---

- Sanbhaumik ---------------------------------------------------- Please Mark as Answered if you find my post helpful.

Answer 2

DatePart function returns a part of the datetime , the case here it will will return date

declare @dt datetime =getdate();
select datepart(day,@dt)

Here this is adding a constraint on the table and saying the date should be 1 always (1-jan-14, 1-feb-14 etc...)

---

Satheesh
My Blog | How to ask questions in technical forum

Answer 3

The Check constraint ensures that any value inserted into the CASH_EXP_DATE column must be a 1 (First day of the month) and nothing else.

Answer 4

Ashish,

the check constraint mandates that the datetime value inserted into the CASH_EXP_DATE will always have the date as to be 1(first of month). In case of anything else, the insert will not happen and a check constraint violation error will be thrown.

create table #retailer(cash_Exp_date datetime)

alter table #retailer add constraint cash_Exp_date_first_of_month
check(Datepart(day,cash_exp_Date)=1)

insert into #retailer select '2013-12-01' --works fine
insert into #retailer select '2013-12-31' --error
/*
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "cash_Exp_date_first_of_month". The conflict occurred in database "tempdb", table "dbo.#retailer___________________________________________________________________________________________________________0000000003B8", column 'cash_Exp_date'.
The statement has been terminated.
*/

---

Thanks,
Jay
<If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

Answer 5

The name of the check constraint says it all

CASH_EXP_DATE_FIRST_OF_MONTH

it only allows first date of month to be inserted into the field

the WITH NOCHECK will not cause check constraint  to be evaluated for existing data.

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Answer 6

Visit the following link

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/907e1139-6b9d-4707-a279-d8330e238a8c/is-there-a-reason-for-these-with-nocheck-check-constraint?forum=transactsql