CODEWITHSUNDEEP
sqlsql-servert-sql
Dr. Ikjyot Singh Kohli
Dr. Ikjyot Singh Kohli

Reputation: 217

SQL Server sort by date

I have a table of transactions in SQL Server 2016 with a column called mthweekyr where each row in this column has as a string, the month number, week number of the month, and year of the transaction, where week number is determined by day of the month / 7.

For example, if the transaction date was: 2018-09-28, this would appear as 9-4-2018.

The problem is that the original transaction date column is not actually in the table, and I would like to sort these transactions by the mthweekyr column, but am unable to do so because this column is not a date type, it is a string type. (I don't make the tables, I just use them!)

Any suggestions on how to proceed?

Thanks.

Upvotes: 0

Views: 609

Answers (5)

Bozhidar Stoyneff
Bozhidar Stoyneff

Reputation: 3634

Since you know what the algorithm for date hashing is you can restore the original dates into a new column and then sort on it. Something like:

SELECT *, CONVERT(datetime2(1), 
    RIGHT(@mthweekyr, 4) + '-' + 
    LEFT(@mthweekyr, CHARINDEX('-', @mthweekyr) - 1) + '-' + 
    SUBSTRING(@mthweekyr, CHARINDEX('-', @mthweekyr) + 1, 
        CHARINDEX('-', @mthweekyr, CHARINDEX('-', @mthweekyr) + 1)
            - CHARINDEX('-', @mthweekyr) - 1)) AS EstimatedDate
FROM theTable
ORDER BY EstimatedDate

UPDATE

So the above is the long, harder way.

A smarter approach would be to leave the heavy lifting to the SQL Engine and use the third parameter of the CONVERT function, which specifies the style of the input. As the mthweekyr basically represents a USA style of mm-d-yyyy, the query will look like:

    SELECT *, CONVERT(datetime2(1), mthweekyr, 110) AS EstimatedDate
    FROM theTable
    ORDER BY EstimateDate

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81930

Late answer, but perhaps a thinner alternative.

You can simply use the implicit conversion of m-d-y to date

Example

declare @table table(mthweekyr varchar(10))
insert into @table
values
('9-4-2018'),
('8-4-2018'),
('9-5-2018'),
('7-5-2018'),
('7-4-2018'),
('9-5-2017'),
('9-4-2017'),
('10-4-2017'),  -- added 2 digit month
('10-5-2017')   -- added 2 digit month

Select *
 From  @table
 Order By convert(date,mthweekyr)

Returns

mthweekyr
9-4-2017
9-5-2017
10-4-2017
10-5-2017
7-4-2018
7-5-2018
8-4-2018
9-4-2018
9-5-2018

Upvotes: 3

Jay Wheeler
Jay Wheeler

Reputation: 379

obviously not the most efficient I'm sure but to brute force it...

order by substring(@theStr, len(@theStr) - 4 + 1, 4)  
                + substring(@thestr,  charindex('-',@Thestr)+1,1)
                + right('00' + substring(@thestr, 1, charindex('-',@Thestr)-1),2)

here's how to get there by pieces:

declare @thestr varchar(255) = '9-4-2018'

Declare @SortStr as Varchar(255)

--year
Select @SortStr = substring(@theStr, len(@theStr) - 4 + 1, 4) 
print @Sortstr
--day
Select @SortStr = right('00' + substring(@thestr, 1, charindex('-',@Thestr)-1),2)
print @sortStr

--week
Select @SortStr = substring(@thestr,  charindex('-',@Thestr)+1,1)
print @sortStr

Select @SortStr = substring(@theStr, len(@theStr) - 4 + 1, 4)  
                + substring(@thestr,  charindex('-',@Thestr)+1,1)
                + right('00' + substring(@thestr, 1, charindex('-',@Thestr)-1),2)

will yield this output

2018
09
4
2018409

Upvotes: 1

S3S
S3S

Reputation: 25112

Here's another way...

declare @table table(mthweekyr varchar(10))
insert into @table
values
('9-4-2018'),
('8-4-2018'),
('9-5-2018'),
('7-5-2018'),
('7-4-2018'),
('9-5-2017'),
('9-4-2017')

select *
from @table
order by
    cast(right(mthweekyr,4) as int)                          --order by year first
    ,cast(left(mthweekyr,charindex('-',mthweekyr)-1) as int) --order by month. Works for single or double digit months
    ,left(right(mthweekyr,6),1)                              --order by week number which is always a single digit, since it's the week number of the month not the year.

Upvotes: 3

Sean Lange
Sean Lange

Reputation: 33571

Using JNevill's suggestion you could leverage PARSENAME for this.

declare @Date varchar(20) = '9-4-2018'
select PARSENAME(replace(@Date, '-', '.'), 1) + '-' + right('00' + PARSENAME(replace(@Date, '-', '.'), 3), 2) + '-' + right('00' + PARSENAME(replace(@Date, '-', '.'), 2), 2)

This returns 2018-09-04

You could also do some string manipulation but that starts to get rather tedious.

Upvotes: 2

Related Questions