March 12, 2010 at 6:50 am
Hi I am getting the dates as 3/8,3/9/,3/10 and 3/11 into string variable.
Can some one please let me know how to order by these dates, since i am getting the unorder format
as 3/10,3/11,3/9,3/8 etc...
Here is my sample SQL
select * from
(select distinct
CAST(DATEPART(month, datecolumn) as varchar) + '/' + CAST(DATEPART(dd,datecolumn) as varchar) days,
CAST(DATEPART(month,datecolumn) as varchar) + '/' + CAST(DATEPART(dd,datecolumn) as varchar) DayOrd
from tablename where datecolumn between dateadd(week, datediff(week, 0, (getdate())), -1) and
dateadd(week, datediff(week, 0, (getdate())), 5))TAB
ORDER BY CAST(replace(TAB.DayOrd,'/','') as datetime) DESC
Thanks much for any qucik help
Thanks
KP
March 12, 2010 at 7:07 am
First of all .... buy huge servers to be able to cope with the little load you need !
Use the correct data type for your columns and make it mandatory.
Only then you can be a little bit at comfort with the performance measures you take or must guarantee !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 12, 2010 at 7:08 am
I might be missing something here, but I took your SQL and created a test table and populated with the date values you suggested:
CREATE TABLE #data ([datecolumn] datetime)
INSERT INTO #data VALUES ('Mar 11 2010')
INSERT INTO #data VALUES ('Mar 10 2010')
INSERT INTO #data VALUES ('Mar 8 2010')
INSERT INTO #data VALUES ('Mar 9 2010')
Then I ran your SELECT (which failed with a date converson error).
I then simply changed the ORDER BY to :
ORDER BY CAST(replace(TAB.DayOrd,'/','') as INT)
3/10 becomes 310
3/11 becomes 311
3/8 becomes 38
3/9 becomes 39
ordering by INT might be the answer?
March 12, 2010 at 7:23 am
Still ... NO NO NO convert to the correct datatype !!!!
Declare @data varchar(MAX)
, @Delimiter char(1)
Select @data= '3/8,3/9,3/10,3/11'
, @Delimiter = ','
;
WITH cteConvert ( IdNr , Item, RemainderData )
AS (
SELECT 1 as IdNr
, SUBSTRING(@Data, 1, CHARINDEX(@Delimiter, @data, 1) - 1) as Item
, SUBSTRING(@Data, CHARINDEX(@Delimiter, @data, 1) + 1, LEN(@Data)) + @Delimiter as RemainderData
UNION ALL
SELECT IdNr + 1
, SUBSTRING(RemainderData, 1, CHARINDEX(@Delimiter, RemainderData, 1) - 1) as Item
, SUBSTRING(RemainderData, CHARINDEX(@Delimiter, RemainderData, 1) + 1, LEN(RemainderData)) as RemainderData
FROM cteConvert
WHERE LEN(RemainderData) > 0
)
SELECT IdNr, Item + '/2010' , convert(datetime, Item + '/2010' , 101 ) as itemDateTime
FROM cteConvert
order by itemDateTime
And don't return it as strings ! (formatting is a front end application task !)
Convert as soon as possible and only use the correct data type later on !!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 12, 2010 at 7:24 am
Thanks much it works
March 12, 2010 at 10:28 pm
Just in case ALZDBA left any room for ambiguity: do not store or process dates as strings in SQL Server. Use one of the date/time data types. Always.
😛
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply