FORMAT date in a stored procedure

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 2005 Forums

Transact-SQL (2005)

FORMAT date in a stored procedure

Author

Topic

tenderfoot
Starting Member

4 Posts

Posted - 2007-07-31 : 11:11:23

Hi,

I have a stored procedure that pulls out many columns of data, and one of those columns a date is stored like so 1/10/2007.

The problem is that I want to ORDER BY these dates but when I run a query to ORDER BY StartDate the dates are ordered by the day like so:

1/10/2007
2/9/2007
3/8/2007
5/8/2007

Obviously this is not in date order, but as a noob to stored procedures I have no idea where to start to format these dates. I have looked at the CONVERT function briefly, but I am not sure if this what I need.

Could someone help?

This is the SP:


SELECT
	ceav_title.Value AS Title,
	ceav_startdate.Value AS StartDate,
	ceav_enddate.Value AS EndDate
	
	
FROM ContentElement ce 
LEFT JOIN ContentElementAttributeValue AS ceav_title ON ceav_title.AttributeID = 17 AND ceav_title.ContentElementID = ce.ID
	
LEFT JOIN ContentElementAttributeValue AS ceav_startdate ON ceav_startdate.AttributeID = 24 AND ceav_startdate.ContentElementID = ce.ID
LEFT JOIN ContentElementAttributeValue AS ceav_enddate ON ceav_enddate.AttributeID = 25 AND ceav_enddate.ContentElementID = ce.ID
	WHERE TypeID =1 
	ORDER BY StartDate ASC

Thanks in advance!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-07-31 : 11:32:34

1/10/2007
2/9/2007
3/8/2007
5/8/2007

Looks like that is ordered by date, it's in mm/dd/yyyy format. Are you saying you want it order by the day?
3/8/2007
5/8/2007
2/9/2007
1/10/2007

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 11:37:41

ORDER BY CAST(StartDate AS DATETIME) ASC

E 12°55'05.25"
N 56°04'39.16"

tenderfoot
Starting Member

4 Posts

Posted - 2007-07-31 : 11:54:54

Hi,

jimf, sorry, I should have mentioned that I am British, not from the US so the dates are not in date order for me.

Cheers Peso! It was easier than I expected!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-31 : 12:23:31

It might be helpful to mention: ALWAYS use the correct data types in your tables, then your dates will always not only be valid but you can sort them properly, use date functions on them, and format them any way you need.

- Jeff
http://weblogs.sqlteam.com/JeffS

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-07-31 : 17:06:10

I figured as much, I just didn't want to assume anything. So, does water drain counter-clockwise there?

Jim

(That's a joke)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources