time extraction - 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 2000 Forums

Transact-SQL (2000)

time extraction

Author

Topic

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2003-08-12 : 04:04:32

how to extract time from a datetime field and sort it ?
regards,
harshal

The Judgement of the Judge is as good as the Judge.

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-08-12 : 04:45:42

This is how I do it. You have to modify it a little bit

SELECT DISTINCT convert(varchar(12),MsgDateReceived,108)AS A FROM mails_header ORDER BY convert(varchar(12),MsgDateReceived,108) DESC

HTH

Cheers,
Frank

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2003-08-12 : 05:03:17

thnxs

The Judgement of the Judge is as good as the Judge.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-08-12 : 05:18:04

dt - DATEADD(dd, DATEDIFF(dd, 0, dt), 0)

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-08-12 : 05:54:44

ahem, I might be wrong, but doesn't this set only all time values to 0 ?

Cheers,
Frank

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-08-12 : 06:06:49

No, it returns the time part of dt on the origin date (19000101). i.e. the same value as you would get with
CONVERT(datetime, CONVERT(varchar(12), dt, 114), 114)
only rather faster.

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-08-12 : 06:12:09

still struggling. can you post a full statement?

Cheers,
Frank

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-08-12 : 06:23:18

Well DATEADD(dd, DATEDIFF(dd, 0, dt), 0) gives you the start of the day in which dt lies, and subtracting two datetimes gives the duration of the difference as a datetime value from the origin
However... having thought about it a bit more, it can be simplified to
DATEADD(dd, DATEDIFF(dd, dt, 0), dt)

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-08-12 : 06:27:53

What I meant was, how do you place this into your statement.
This way


SELECT DATEADD(dd, DATEDIFF(dd, MsgDateReceived, 0), MsgDateReceived) FROM mails_header ORDER BY
DATEADD(dd, DATEDIFF(dd, MsgDateReceived, 0), MsgDateReceived) DESC

?

Cheers,
Frank

mr_mist
Grunnio

1870 Posts

Posted - 2003-08-12 : 06:35:17

Like this...

select '20031212 10:59' - DATEADD(dd, DATEDIFF(dd, 0, '20031212 10:59'), 0)

-------
Moo. :)

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-08-12 : 06:40:32

what is not clear to me is, how your solutions extract the time values from a datetime ???

but maybe that's the hot weather here in germany which is boiling the brain :)

Cheers,
Frank

leahsmart
Posting Yak Master

133 Posts

Posted - 2003-08-12 : 08:52:17

I have a similar problem, below is my code. All i want to do is list the results in date order. How do I do this??? At the moment it lists it as if the date was a string, which it is as I have used convert.

SELECT convert(varchar, DateTime, 103) as Date,
Sum (1) as Used

FROM tbl_Wizard_Usage

WHERE Wizard = 'DPM'

GROUP BY
convert(varchar, DateTime, 103)

ORDER BY
convert(varchar, DateTime, 103) ASC

mr_mist
Grunnio

1870 Posts

Posted - 2003-08-12 : 09:06:55

SELECT convert(varchar, DateTime, 103) as Date,
Sum (1) as Used

FROM tbl_Wizard_Usage

WHERE Wizard = 'DPM'

GROUP BY
convert(varchar, DateTime, 103)

ORDER BY
DateTime ASC

Is your datetime field actually called "datetime"? Do you not find that this causes you problems?

-------
Moo. :)

leahsmart
Posting Yak Master

133 Posts

Posted - 2003-08-12 : 10:15:14

I have tried that but I get the below error:

Column name 'tbl_Wizard_Usage.DateTime' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

mr_mist
Grunnio

1870 Posts

Posted - 2003-08-12 : 10:23:30

Sorry...

SELECT convert(varchar, DateTime, 103) as Date,
Sum (1) as Used

FROM tbl_Wizard_Usage

WHERE Wizard = 'DPM'

GROUP BY
DateTime

ORDER BY
DateTime ASC

You will get the same result grouping by the column than grouping by a conversion of that column.

-------
Moo. :)

leahsmart
Posting Yak Master

133 Posts

Posted - 2003-08-12 : 10:41:57

Nope, thats no good. That returns an entry for each time the wizard was used. I want a sum of how many times the wizard was used each day. For example the below is what I get, I just want it to put it in date order.

04/08/2003 3
05/08/2003 227
06/08/2003 17
07/08/2003 26
08/08/2003 85
09/08/2003 11
10/08/2003 126
11/08/2003 452
12/08/2003 432
16/07/2003 11
20/07/2003 136
21/07/2003 1
22/07/2003 131
24/07/2003 5

mr_mist
Grunnio

1870 Posts

Posted - 2003-08-12 : 10:53:03

I don't understand. I have not altered your code other than to order it by date. How can it be any wronger than what you started with? Have you tried count(*) instead of sum(1) ?

-------
Moo. :)

leahsmart
Posting Yak Master

133 Posts

Posted - 2003-08-12 : 11:03:27

What is the difference between count and some. I ahve tried it and I get the same result.

mr_mist
Grunnio

1870 Posts

Posted - 2003-08-12 : 11:16:14

Count will count the number of records, sum will sum up the values of the records. So I suppose count will be identical to sum (1). However, the code should work in that case, unless your dates actually have time values.

-------
Moo. :)

leahsmart
Posting Yak Master

133 Posts

Posted - 2003-08-12 : 11:36:14

Ahh I know, just being stupid for a second!

leahsmart
Posting Yak Master

133 Posts

Posted - 2003-08-12 : 11:37:56

Yep, thats my problem, all my dates have times values, and if I group it by date i get one for each time.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources