DateTime Column Questions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

DateTime Column Questions

Author

Topic

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-04-07 : 11:59:24

I have two datetime columns, startdatetime and enddatetime.
I want to find the difference between the two columns.
When I try to use a simple subtract in my select query:

SELECT (startdatetime - enddatetime) AS MyTimings
FROM TableName
WHERE enddatetime between
'2004-05-06 00:00:00.000' and '2004-05-06 09:45:00.000'

I am getting the following:
MyTimings
1900-01-01 00:01:06.000
1900-01-01 00:01:18.000
1900-01-01 00:01:06.000
1900-01-01 00:01:06.000
1900-01-01 00:01:06.000
1900-01-01 00:01:25.000
1900-01-01 00:01:07.000

I wonder why I am getting 1900-01-01 as the date?
So, to find a solution to fix this is my first question here.

And the second question is, after I achieve this, I want to sum the MyTimings column. I mean I want to add all the records that are there for a particular date.

I will be here to answer all your questions if you did not understand my query here.

Any help will be appreciated.

Thank you.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-04-07 : 12:09:27

select dateDiff(day,startdatetime,enddatetime) --you can also use year, month, seconds, etc.
FROM TableName
WHERE enddatetime between
'2004-05-06 00:00:00.000' and '2004-05-06 09:45:00.000'

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-04-07 : 13:18:54

quote:
Originally posted by Vinnie881

select dateDiff(day,startdatetime,enddatetime) --you can also use year, month, seconds, etc.
FROM TableName
WHERE enddatetime between
'2004-05-06 00:00:00.000' and '2004-05-06 09:45:00.000'

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Thank you for your answer, but I am getting a column as an output after executing this query with all 0's (zeroes) in it. Can you please verify the query again and let me know. Meanwhile, I am also trying to figure out where the problem is.

Thank you once again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 13:23:33

Have a look here how DATEDIFF works
http://www.sqlteam.com/article/datediff-function-demystified

E 12°55'05.63"
N 56°04'39.26"

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-07 : 13:24:27

can you show some sample data from those two fields: startdatetime,enddatetime. They will return 0 if they are in the same date and you use dateDiff(day,startdatetime,enddatetime). if you want to be more granular you havr to specify what you want as Vinnie881 suggested: hours, minutes , seconds

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-04-07 : 13:29:13

Thank you for all your replies. Yes, I tried with hour and minutes and I am getting the desired output.

Thank you.

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-04-07 : 14:17:32

One more question.

I want to create a table in SQL 2005, which has the following:

DateTime
2009-04-01 08:30:00.000
2009-04-02 08:30:00.000
2009-04-03 08:30:00.000
2009-04-04 08:30:00.000
2009-04-05 08:30:00.000
2009-04-06 08:30:00.000
2009-04-07 08:30:00.000
2009-04-08 08:30:00.000
2009-04-09 08:30:00.000
2009-04-10 08:30:00.000
2009-04-11 08:30:00.000

I just have one row: 2009-04-01 08:30:00.000
I want to create the other rows as mentioned above.
The days should be changing everyday in the rows.
Is there a way that I can achieve such table for two years 2008 and 2009.

Can anyone please let me know about this?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-07 : 15:04:10

you want every day of the every month for both 2008 and 2009? or just April dates? Why not create an Calendar table? search for AuxCalendar in this here forum sir.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-04-07 : 15:29:50

Not just April Dates. I need a table for the whole year. Ok. I shall search on the AuxCalendar. I actually did not know if there is anything as such.

Thank you.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-07 : 15:33:25

this is what I use I found it on this forum from someone so no credit goes to me.

--<><><><><><>
if exists (select * from sysobjects where id = object_id('dbo.AuxCalendar') and sysstat & 0xf = 3)
DROP TABLE "dbo"."AuxCalendar"
GO

CREATE TABLE dbo.AuxCalendar (
-- This is the calendar table
[Date] datetime NOT NULL,
[Year] int NOT NULL,
[Quarter] int NOT NULL,
[Month] int NOT NULL,
[Week] int NOT NULL,
[Day] int NOT NULL,
[DayOfYear] int NOT NULL,
[Weekday] int NOT NULL,
[Fiscal_Year] int NOT NULL,
[Fiscal_Quarter] int NOT NULL,
[Fiscal_Month] int NOT NULL,
[KindOfDay] varchar(10) NOT NULL,
[Description] varchar(50) NULL,
PRIMARY KEY CLUSTERED ([Date])
)
GO

ALTER TABLE dbo.AuxCalendar
-- In Celkoish style I'm manic about constraints (Never use em ;-))
-- http://www.celko.com/

ADD CONSTRAINT [Calendar_ck] CHECK ( ([Year] > 1900)
AND ([Quarter] BETWEEN 1 AND 4)
AND ([Month] BETWEEN 1 AND 12)
AND ([Week] BETWEEN 1 AND 53)
AND ([Day] BETWEEN 1 AND 31)
AND ([DayOfYear] BETWEEN 1 AND 366)
AND ([Weekday] BETWEEN 1 AND 7)
AND ([Fiscal_Year] > 1900)
AND ([Fiscal_Quarter] BETWEEN 1 AND 4)
AND ([Fiscal_Month] BETWEEN 1 AND 12)
AND ([KindOfDay] IN ('HOLIDAY', 'SATURDAY', 'SUNDAY', 'BANKDAY')))
GO

SET DATEFIRST 1;
-- I want my table to contain datedata acording to ISO 8601
-- http://en.wikipedia.org/wiki/ISO_8601
-- thus first day of a week is monday
WITH Dates(Date)
-- A recursive CTE that produce all dates between 1999 and 2020-12-31
AS
(
SELECT cast('1999' AS DateTime) Date -- SQL Server supports the ISO 8601 format so this is an unambigious shortcut for 1999-01-01
UNION ALL -- http://msdn2.microsoft.com/en-us/library/ms190977.aspx
SELECT (Date + 1) AS Date
FROM Dates
WHERE
Date < cast('2021' AS DateTime) -1
),

DatesAndThursdayInWeek(Date, Thursday)
-- The weeks can be found by counting the thursdays in a year so we find
-- the thursday in the week for a particular date
AS
(
SELECT
Date,
CASE DATEPART(weekday,Date)
WHEN 1 THEN Date + 3
WHEN 2 THEN Date + 2
WHEN 3 THEN Date + 1
WHEN 4 THEN Date
WHEN 5 THEN Date - 1
WHEN 6 THEN Date - 2
WHEN 7 THEN Date - 3
END AS Thursday
FROM Dates
),

Weeks(Week, Thursday)
-- Now we produce the weeknumers for the thursdays
-- ROW_NUMBER is new to SQL Server 2005
AS
(
SELECT ROW_NUMBER() OVER(partition by year(Date) order by Date) Week, Thursday
FROM DatesAndThursdayInWeek
WHERE DATEPART(weekday,Date) = 4
)
INSERT INTO dbo.AuxCalendar
SELECT
d.Date,
YEAR(d.Date) AS Year,
DATEPART(Quarter, d.Date) AS Quarter,
MONTH(d.Date) AS Month,
w.Week,
DAY(d.Date) AS Day,
DATEPART(DayOfYear, d.Date) AS DayOfYear,
DATEPART(Weekday, d.Date) AS Weekday,

-- Fiscal year may be different to the actual year in Norway the are the same
-- http://en.wikipedia.org/wiki/Fiscal_year
YEAR(d.Date) AS Fiscal_Year,
DATEPART(Quarter, d.Date) AS Fiscal_Quarter,
MONTH(d.Date) AS Fiscal_Month,

CASE
-- Holidays in Norway
-- For other countries and states: Wikipedia - List of holidays by country
-- http://en.wikipedia.org/wiki/List_of_holidays_by_country
WHEN (DATEPART(DayOfYear, d.Date) = 1) -- New Year's Day
OR (d.Date = dbo.AuxComputus(YEAR(Date))-7) -- Palm Sunday
OR (d.Date = dbo.AuxComputus(YEAR(Date))-3) -- Maundy Thursday
OR (d.Date = dbo.AuxComputus(YEAR(Date))-2) -- Good Friday
OR (d.Date = dbo.AuxComputus(YEAR(Date))) -- Easter Sunday
OR (d.Date = dbo.AuxComputus(YEAR(Date))+39) -- Ascension Day
OR (d.Date = dbo.AuxComputus(YEAR(Date))+49) -- Pentecost
OR (d.Date = dbo.AuxComputus(YEAR(Date))+50) -- Whitmonday
OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 1) -- Labour day
OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 17) -- Constitution day
OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 25) -- Cristmas day
OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 26) -- Boxing day
THEN 'HOLIDAY'
WHEN DATEPART(Weekday, d.Date) = 6 THEN 'SATURDAY'
WHEN DATEPART(Weekday, d.Date) = 7 THEN 'SUNDAY'
ELSE 'BANKDAY'
END KindOfDay,
CASE
-- Description of holidays in Norway
WHEN (DATEPART(DayOfYear, d.Date) = 1) THEN 'New Year''s Day'
WHEN (d.Date = dbo.AuxComputus(YEAR(Date))-7) THEN 'Palm Sunday'
WHEN (d.Date = dbo.AuxComputus(YEAR(Date))-3) THEN 'Maundy Thursday'
WHEN (d.Date = dbo.AuxComputus(YEAR(Date))-2) THEN 'Good Friday'
WHEN (d.Date = dbo.AuxComputus(YEAR(Date))) THEN 'Easter Sunday'
WHEN (d.Date = dbo.AuxComputus(YEAR(Date))+39) THEN 'Ascension Day'
WHEN (d.Date = dbo.AuxComputus(YEAR(Date))+49) THEN 'Pentecost'
WHEN (d.Date = dbo.AuxComputus(YEAR(Date))+50) THEN 'Whitmonday'
WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 1) THEN 'Labour day'
WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 17) THEN 'Constitution day'
WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 25) THEN 'Cristmas day'
WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 26) THEN 'Boxing day'
END Description

FROM DatesAndThursdayInWeek d
-- This join is for getting the week into the result set
inner join Weeks w
on d.Thursday = w.Thursday

OPTION(MAXRECURSION 0)
GO

--<><><><>

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources