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)
==============================================================================================================
