Sql Query Help, to make start time from 6 AM

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)

Sql Query Help, to make start time from 6 AM

Author

Topic

saidev
Posting Yak Master

101 Posts

Posted - 2006-09-19 : 14:43:09

Hi Guys,

I have a query in Sql Server2000. When i run this query the time field is starting from 12 AM. I want this to Start from 6 AM. Can you guys
help me how to do this. Appreciate your help. Here is the Query
THanks,

SELECT *
FROM tblMovies INNER JOIN
tblAirdateRuns ON tblMovies.MovieID = tblAirdateRuns.AirdateRunID
ORDER BY tblAirdateRuns.[Time]

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-09-19 : 15:04:01

Ordering on time should give you the earliest first. So is that your whole query? Is it because you want to shome times for a specific date and want the date cut to happe at at different time than midnight?

-- The Heisenberg uncertainty principle also applies when debugging

saidev
Posting Yak Master

101 Posts

Posted - 2006-09-19 : 15:26:50

Hi,

No, I have to sort by time only.
Thanks

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-09-19 : 15:34:50

Then you have to explain (give table structure, data sample and desired result).

-- The Heisenberg uncertainty principle also applies when debugging

saidev
Posting Yak Master

101 Posts

Posted - 2006-09-19 : 15:44:55

Hi,
I have so many fields in these tables. But in the report i need only
year, time and title. I tried to sort by year but the time is not accurate( Ex: 6pm, 9am, 10pm like that). i want like 6am, 8am, 10am and so on..
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 15:57:12

Don't tell us you are storing dates as VARCHAR?

SELECT *
FROM tblMovies INNER JOIN
tblAirdateRuns ON tblMovies.MovieID = tblAirdateRuns.AirdateRunID
ORDER BY CONVERT(datetime, tblAirdateRuns.[Time])

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 16:04:53

Or this maybe?

SELECT      *
FROM        tblMovies
INNER JOIN  tblAirdateRuns ON tblMovies.MovieID = tblAirdateRuns.AirdateRunID
ORDER BY    DATEADD(hour, 6, tblAirdateRuns.[Time])

Peter Larsson
Helsingborg, Sweden

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-09-19 : 16:06:00

... no wonder I was puzzled, and of course '12am' comes before '6am' ... DUH !

-- The Heisenberg uncertainty principle also applies when debugging

saidev
Posting Yak Master

101 Posts

Posted - 2006-09-19 : 16:45:44

quote:
Originally posted by Peso

Or this maybe?
SELECT *
FROM tblMovies
INNER JOIN tblAirdateRuns ON tblMovies.MovieID = tblAirdateRuns.AirdateRunID
ORDER BY DATEADD(hour, 6, tblAirdateRuns.[Time])

Peter Larsson
Helsingborg, Sweden

Hi,
I tried both of them but still the time is starting from 12 AM. Any suggestions.?
Thanks

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-09-19 : 16:55:08

I still don't get what you're looking for. This?

SELECT *
FROM tblMovies M
INNER JOIN tblAirdateRuns R ON M.MovieID = R.AirdateRunID
ORDER BY CASE WHEN DATEPART(HH, R.[Time]) < 6 THEN DATEADD(DD, 1, R.[Time]) ELSE R.[Time] END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-19 : 17:02:19

I don't get what you're looking for either. Perhaps it's time for some sample data and expected results.

Sounds like you want to sort a datetime column by year and time (not date). But I don't get the "results starting from 6:00 AM" part.

Be One with the Optimizer
TG

saidev
Posting Yak Master

101 Posts

Posted - 2006-09-19 : 17:17:12

quote:
Originally posted by JoeNak

I still don't get what you're looking for. This?

SELECT *
FROM tblMovies M
INNER JOIN tblAirdateRuns R ON M.MovieID = R.AirdateRunID
ORDER BY CASE WHEN DATEPART(HH, R.[Time]) < 6 THEN DATEADD(DD, 1, R.[Time]) ELSE R.[Time] END

Thank You, appreciate your help. It did worked..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources