To get Time like 20:30, 18:00.
SELECT Convert(char(2),DATEPART(hour, GETDATE())) + ':'+convert(char(2),datepart(mi,getdate()))
===============================================================================================
i am working in sqlser2000. i have one doubt in qry. For Example one table
having ten records.in this table one date column.for example i want group by
through date column.
id ,date
1, 01-01-2005
2, 01-01-2005
3, 07-01-2005
4, 07-01-2005
5, 07-01-2005
6, 10-01-2005
in this stored the records.
Now my questions is
i am searching a data in 01-01-2005 to 10-01-2005 the count of records based up
on date
i want following results
Date Count
01-01-2005 2
02-01-2005 0
03-01-2005 0
04-01-2005 0
05-01-2005 0
06-01-2005 0
07-01-2005 3
08-01-2005 0
09-01-2005 0
10-01-2005 1
i am not expected in the following result
01-01-2005 2
07-01-2005 3
10-01-2005 1
i want remaing date fully count 0
CREATE TABLE #TEMP (dATE DATETIME,CNT INT)
DECLARE @I INT
SET @I = 1
WHILE @I < 11
BEGIN
DECLARE @DTT DATETIME
DECLARE @CNT INT
SELECT @CNT = count(dt) FROM DT WHERE month(DT)= @I GROUP BY DT
SELECT @dtt = DT FROM DT WHERE month(dt) = @I
INSERT INTO #TEMP
SELECT CONVERT(VARCHAR,@I)+ '-01' + '-2005',CASE WHEN MONTH(@dtT) = @I THEN
@CNT ELSE 0 END AS C
seT @I = @I + 1
END
SELECT convert(VARCHAR,MONTH(date)) + '-' + CONVERT(VARCHAR,DAY(date)) + '-' +
convert(varchar,year(date)),cnt FROM #TEMP
DROP TABLE #TEMP
==============================================================================================================
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
-- to Get last date of a month
Declare @Enddate Datetime
set @Enddate = getdate()
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Enddate)+1,0)) as lastday
how to write a query for getting the first day of the month?
exeample : oct - 1 - 2007 means monday i want to get like this.
SELECT MONTH(getdate()) as month1, DAY(0) as date, YEAR(getdate()) as year1
==============================================================================================================
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)
==============================================================================================================
To get the day name of a given date:
SELECT DATENAME(WEEKDAY, GETDATE())
-----------------------------------
the syntax is:
DATENAME ( datepart , date )
Arguments
datepart
Is the parameter that specifies the part of the date to return. The table lists dateparts and abbreviations recognized by Microsoft® SQL Server™.
Datepart Abbreviations
-------------------------------------
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw
hour hh
minute mi, n
second ss, s
millisecond ms
It gives time gap between two times
CREATE TABLE [dbo].[Table_1](
[EmpID] [int] IDENTITY(1,1) NOT NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[Timegap] AS
(stuff(CONVERT([char](8),dateadd(second,datediff(second,[startdate],[enddate]),(0)),(108)),(1),(2),left(CONVERT([char](8),dateadd(second,datediff(second,[startdate],[enddate]),(0)),(108)),(2))+datediff(day,(0),dateadd(second,datediff(second,[startdate],[enddate]),(0)))*(24)))
) ON [PRIMARY]
==============================================================================================================
CREATE PROCEDURE [dbo].[usp_test]
@S_Date [datetime],
@E_Date [datetime]
AS
BEGIN
SET NOCOUNT ON
DECLARE @S_UTCDate DATETIME
DECLARE @E_UTCDate DATETIME
-- Convert to GMT dateTime
SET @S_UTCDate = DATEADD(Hour, DATEDIFF(Hour,GETUTCDATE(), GETDATE()),
@S_Date)
SET @E_UTCDate = DATEADD(Hour, DATEDIFF(Hour,GETUTCDATE(), GETDATE()),
@E_Date)
INSERT INTO TABLE_1 (StartDate,EndDate)
VALUES (@S_UTCDate,@E_UTCDate)
END
exec usp_test '6/02/2007 10:12:00', '6/25/2007 12:20:10'
select * from table_1