Ordering a selection by dates.. real easy

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)

Ordering a selection by dates.. real easy

Author

Topic

g_r_a_robinson
Starting Member

45 Posts

Posted - 2004-02-04 : 23:19:10

My dilema would probably be nothing to some of you guys but I'm still new to tsql. I have a bunch of info that I need to have ordered by date. I have tried the following:

ORDER BY Due_Date DESC

I have my dates returned in the format : dd/mm/yyyy

But it only orders the days not months or years.

heres the output:

28/02/2004
20/02/2004
19/02/2004
10/12/2003 // as you can see this one should be at the top
05/02/2004
05/02/2004
05/02/2004
13/02/2004
05/02/2004
01/02/2004

Any help would be well appreciated

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-02-04 : 23:24:19

They're stored as character data, not datetimes. Try this:

ORDER BY CONVERT(datetime, Due_Date, 102) DESC

g_r_a_robinson
Starting Member

45 Posts

Posted - 2004-02-05 : 01:28:01

I got this error. I did use a Union command.

Server: Msg 104, Level 15, State 1, Line 7
ORDER BY items must appear in the select list if the statement contains a UNION operator.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-02-05 : 08:45:25

so make sure you add that expression to your SELECT list. you don't need to use the value, just make sure it is calculated.

- Jeff

g_r_a_robinson
Starting Member

45 Posts

Posted - 2004-02-05 : 19:10:06

I'm sorry to be so slow. Could you modify the change to my select as needed. Here it is:

SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact,
dbo.DatePart(Master_Jobs.Due_Date) as Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-05 : 19:13:01

Where's the rest of your SELECT statement? Post the entire thing and then we'll see if we can fix it up.

Tara

g_r_a_robinson
Starting Member

45 Posts

Posted - 2004-02-05 : 20:03:01

--CREATE PROCEDURE spGetJobsByUnreadAndReadByUserID
DECLARE @UserID INT
-- AS
SET @UserID = 5

SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact,
dbo.DatePart(Master_Jobs.Due_Date) as Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who
FROM Master_Jobs INNER JOIN
Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN
Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN
User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN
Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN
Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN
Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND
Job_Assignments.UserID = Users.UserID
WHERE Users.UserID = @UserID AND BackUp_Read = 'Read'
AND Master_Jobs.JobID not in (
SELECT Master_Jobs.JobID
FROM Master_Jobs INNER JOIN
Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN
Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN
User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN
Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN
Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN
Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND
Job_Assignments.UserID = Users.UserID
WHERE Users.UserID = @UserID AND BackUp_Read = 'UnRead')

union

SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact,
dbo.DatePart(Master_Jobs.Due_Date) as Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who

FROM Master_Jobs INNER JOIN
Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN
Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN
User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN
Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN
Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN
Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND
Job_Assignments.UserID = Users.UserID

WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND
BackUp_Read = 'UnRead'

--ORDER BY BackUp_Read DESC, JobID DESC
ORDER BY CONVERT(datetime,Due_Date, 102) DESC

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-05 : 20:07:39

I don't receive an error when I compile your code in Query Analyzer 2000.

Tara

g_r_a_robinson
Starting Member

45 Posts

Posted - 2004-02-05 : 20:43:12

I dont receive an error when I click the blue tick but when I click the green play button I get:

Server: Msg 104, Level 15, State 1, Line 6
ORDER BY items must appear in the select list if the statement contains a UNION operator.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-05 : 20:51:07

Instead of "dbo.DatePart(Master_Jobs.Due_Date) as Due_Date" in both SELECTs, change it to:

CONVERT(datetime,Due_Date, 102) AS Due_Date

You aren't using DATEPART correctly anyway.

Tara

g_r_a_robinson
Starting Member

45 Posts

Posted - 2004-02-05 : 20:59:31

I'm going to give that a try. By the way that datepart is one of my own functions. Although I could have chosen a better name.

g_r_a_robinson
Starting Member

45 Posts

Posted - 2004-02-05 : 21:04:48

It worked!! Only problem is that I needed that DatePart function so that I would only get the date and not the time on the end. Is there anyway I could combine what you gave me with my datepart function.

This is it:

CREATE FUNCTION dbo.DatePart
( @fDate datetime )
RETURNS varchar(10)
AS
BEGIN
RETURN ( CONVERT(varchar(10),@fDate,103) )
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-06 : 12:04:53

DATEPART can't be one of your own functions. It is a SQL Server function already. Are you even using SQL Server?

DATEPART gives you the month, or the year, or the day, but not month-day-year. CONVERT gives you just the day. Look up CONVERT in SQL Server Books Online. You are interested in the styles. So remove DATEPART and put in:

CONVERT(varchar(10), Due_Date,103) AS Due_Date

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources