Is there a better way, problems with dates

Hi

I have a bunch of info that I need to order by date. I have two requirements and I can get one or the other to work but not both. I need the date to be returned in the format:

dd/mm/yyyy

I have no problem with this I use a function called DatePart:

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

that removes the extra parts that I don't need.

So for eg the result for my select statement 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
<code
problem is this wont allow me to order on the following:
<code>
Order By Due_Date ASC
<code
because its not longer a datetime, ie it was converted to nvarchar by the function.
I can if I remove the above and use:
<code>
CONVERT(datetime,Due_Date, 102) AS Due_Date
<code
in my selects, and order by
<code>
ORDER BY CONVERT(datetime,Due_Date, 102) ASC

like this.

But then I lose the ability to format my date. I know some might say well why don't you format it out on the presentation layer but I don't want to do that. I simply want to have my dates formatted by ascending date and truncated to give dd/mm/yyyy. Sorry for the long story. Any help would be most appreciated.wanna dirty fix?

return (Month(@.fDate) + '/' + Day(@.fDate) + '/' + Year(@.fDate))

:)|||It should still work if you do not use the Alias name for ordering but the full qualified name, so
instead of:

Order By Due_Date ASC

do:

Order By Master_Jobs.Due_Date ASC

Further there is already a build in DatePart() function so you should use a different name for yours.

Hth,

Moon|||OK, this works:

Order By Master_Jobs.Due_Date ASC

but how do I get this formatted to dd/mm/yyyy before I return it?|||OK I have sql returning my dates in the fashion:

2002-02-21 00:00:00.000
2004-02-27 00:00:00.000
2002-02-16 00:00:00.000
2004-02-06 00:00:00.000
2004-02-06 00:00:00.000

which would be perfect if i could:

1. get it in the format dd/mm/yyyy

2. if I could lose the 00:00:00:000

But since nobody seems to know how, It looks like I'm going to have to format it at the presentation layer, something I wanted to avoid. I know this question was originally placed as a sql question but now that I'm having to format it asp.net side could someone tell me how I might set my datagrid to format this in dd/mm/yyyy.

Thanks for your help.|||On the SQL side:

CONVERT(nvarhcar(20),yourdatefield,103)

This will return the date as a string. That is the only way on the SQL side to "get rid" of the time part of a DateTime field.|||Thanks for that.

I tried it but it when I go to run it it throws the error:

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.

any idea why|||Exactly what it says. If you are using a UNION operator in the select clause, then the ORDER BY items need to appear in the SELECT list. So, even if you do not need it, you must include whatever is in the ORDER BY in the list of SELECTed fields.|||I don't understand. I get this error even though both my order items appear in both selects. I've posted my code just in case i've missed something obvious.

--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,
CONVERT(nvarhcar(20),Master_Jobs.Due_Date,103), 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,
CONVERT(nvarhcar(20),Master_Jobs.Due_Date,103), 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 Master_Jobs.Due_Date asc-- BackUp_Read DESC, Master_Jobs.Due_Date asc
GO

|||No, you are not.

Look at your select. It includes:

CONVERT(nvarhcar(20),Master_Jobs.Due_Date,103)

However, your ORDER BY includes:

Master_Jobs.Due_Date

These are two seperate things. Add Master_Jobs.Due_Date to your SELECT list in both SELECTS (yes, I know you do not really need it in this context) and I expect you will be fine.|||Thanks for that, that did it.

But could you quickly tell me why I need two references to Master_Jobs.Due_Date in my select statement when using an order by.

<code>
SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact,
CONVERT(nvarchar(20),Master_Jobs.Due_Date,103), Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who
<code
Why two:
<code>
CONVERT(nvarchar(20),Master_Jobs.Due_Date,103)
Master_Jobs.Due_Date
<code
I'm a bit slow!!|||:: It looks like I'm going to have to format it at the presentation layer, something I wanted to avoid

Why? As you can see there is no clean/convenient way to format a date in SQL-Server so ... doing it in the presentation layer is the right place to do this imo.
You can format dates in C# like this:

DateTime.Parse(myDataSetValue).ToShortDateString()

|||

CONVERT(nvarchar(20),Master_Jobs.Due_Date,103)

>> this column is a computed column which is not part of the original table and therefore a different thing.