Getting First and Last Date of the Current Week in sql.

This Post will Explain how we can get the First Date and Last Date of the Current Week or for a given random date if Supplied in sql server.
Here in the Example Below getdate() function is used to get the  todays date. You can make it input field in stored procedure to generalize this procedure for any date and not just current date.

DateADD(interval,increment int,date) Function adds the increment int no of days to the date times the interval, here interval is d which days. Hence 1-datepart(dw,getdate()) no of days are added to the current date.

DatePart(interval,date) gets the date part in interval provided. In our example interval is week day dw hence, datepart gets the what date (1-7) in week is for the given date.


in our example we add the number of day of the week for the date subtracted from 1 for first day which will result in subtracting 2 (1-3) days if today is 3rd day of the week (Tuesday) resulting in sunday's date.
for last day of the week 7-3 will be 4 a positive no which will be added to todays date resulting in saturdays date.

Example:

DECLARE @Startdt Date
DECLARE @Enddt Date
DECLARE @temptbl Table(StartDate Date, EndDate Date)
SET @Startdt= dateadd(d,1-datepart(dw,getdate()),getdate())
SET @Enddt =dateadd(d,7-datepart(dw,getdate()),getdate())

INSERT INTO @temptbl VALUES(@Startdt,@Enddt)
SELECT * FROM @temptbl

Inserting Data in SQL from XML file using Stored Proceduer.

This Post will explain how we can upload data in sql server after reading an XML file.
uses:
1. Importing of data to sql server for application like Excel can be done with help of XML conversion of Excel Data and then Inserting it using this process to upload in the sql database.

2.Other XML Data Generated by application can be inserted in to the data base at once hence reducing network traffic for slow networks.

example:
-- below is the syntax to Create a Stored Procedure for Insertion of data in to the table with XML data
Create Procedure prUpload_XML
@P_XML XML=null
AS
BEGIN
insert into table_Name
(
Column1,
Column2,
Column3
)
SELECT
T.Item.value('(@Column1)[1]','nvarchar(50)'),
T.Item.value('(@Column2)[1]','nvarchar(10)'),
T.Item.value('(@Column3)[1]','nvarchar(50)')
 FROM    @P_XML.nodes('/items/item') AS T ( Item )

--Here items is the name of XML file uploaded and item is the tag for each row in the XML.