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.
| Author |
Topic |
|
g_r_a_robinson
Starting Member
45 Posts |
 Posted - 2004-02-05 : 23:37:35
|
| HiI 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/yyyyI have no problem with this I use a function called DatePart:<code>CREATE FUNCTION dbo.DatePart ( @fDate datetime )RETURNS varchar(10)ASBEGIN RETURN ( CONVERT(varchar(10),@fDate,103) )END</code>that removes the extra parts that I don't need.So for eg the result for my select statement is :<code>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</code>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. |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-02-06 : 00:54:07
|
| Have you tried ordering it by Master_Jobs.Due_Date? or did I miss something? |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-02-06 : 08:09:37
|
| your function should return a DateTime, NOT a varchar. SQL doesn't care that your varchar looks like a date value -- it will order it the way any other text string should be ordered.your function, if it returns varchar's, is basically a formatting function and probably shouldn't be done in SQL server. but if you return a date value, to use for sorting or grouping or further calculations, then it makes more sense.My advice: never work with dates as anything other than a datetime until the very last second, when you are formating output. and even then, try not to do it in SQL server, use the presentation layer.- Jeff |
|
|
|
|
|
|
|
|
new problems ordering dates