Convert Month Names To Sortable Values?

[Aaron Bertrand - MVP]

You should keep another column which stores the date as a date (as it should
be). Or have *just* the date column.

In case (1), you can have

SELECT
formattedDate
FROM table
ORDER BY realDate

In case (2), you can have something like this (not sure of the exact syntax
in Access, which I assume you're using).

SELECT DATENAME('m', realDate) + RIGHT(YEAR(realDate, 2))
FROM table
ORDER BY realDate

Please, ALWAYS indicate what database you're using.

As a final try, you could reverse engineer the abbreviation and make a
computed column in the query only, and order by that.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"MDW" <anonymous@discussions.microsoft.com> wrote in message
news:B3BB87DA-0ED7-446F-B739-4DD488991770@microsoft.com...

> Hey all,
>
> I've got a database I'm going to be linking to via ASP. It's got a "date"

field that is very important for my users. Its data come from an outside
source and I don't have a lot of control over it. Essentially, this field
contains the appreviated month name, concatenated with the last two digits
of the year, making it effectively a text field. For instance, "DEC03" or
"MAR04".

>
> What I want to do is sort on this column, but sort it by the actual date.

If I sorted it as text, then "FEB04" would come before "JAN04", etc.

>
> I can create a function that would receive this field as input, and return

some value, such as #01/01/2004# for "JAN04"....but I'm unsure how to
incorporate that into a SQL statement.

[Bob Barrows [MVP]]

MDW wrote:

> Hey all,
>
> I've got a database

What database? And version?
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

You DO realize that your database will have a Y2100 bug ...
Or are you planning on this to guarantee some work for your children ... ?
;-)

Yes, something like what you propose will work, however, coming up with a
single solution that will work in both Access and SQL Server will be
difficult (convert is the name of a builtin T-SQL function, so you would not
want to use that as the name of your custom function ... you'd want to use a
less "generic" name anyways, wouldn't you?).

MDW wrote:

> Sorry, I forgot to mention the databases I'm using.
>
> Yes, plural. I've got an Access version of it that I'm using to
> develop the thing, but when it goes live it will be on SQL Server.
> Lovely, isn't it?
>
> Using your third option, would something like this work?
>
> "SELECT ACCT,[FORMATTED DATE] FROM Table ORDER BY convert([FORMATTED
> DATE])"
>
> Where [FORMATTED DATE] is the field that contains "MAR04" and convert
> is a function that can change that to 03/01/2004? (I would simply
> change all the dates to the first of the month, as that will
> accomplish the sort order I need. There isn't a specific date
> attached to these records.)
>
> ----- Aaron Bertrand - MVP wrote: -----
>
> You should keep another column which stores the date as a date
> (as it should be). Or have *just* the date column.
>
> In case (1), you can have
>
> SELECT
> formattedDate
> FROM table
> ORDER BY realDate
>
> In case (2), you can have something like this (not sure of the
> exact syntax in Access, which I assume you're using).
>
> SELECT DATENAME('m', realDate) + RIGHT(YEAR(realDate, 2))
> FROM table
> ORDER BY realDate
>
> Please, ALWAYS indicate what database you're using.
>
> As a final try, you could reverse engineer the abbreviation and
> make a computed column in the query only, and order by that.
>
> --
> Aaron Bertrand
> SQL Server MVP
> [url]http://www.aspfaq.com/[/url]
>
>
>
>
> "MDW" <anonymous@discussions.microsoft.com> wrote in message
> news:B3BB87DA-0ED7-446F-B739-4DD488991770@microsoft.com...

> > Hey all,

> >> I've got a database I'm going to be linking to via ASP. It's

> got a "date" field that is very important for my users. Its data
> come from an outside source and I don't have a lot of control
> over it. Essentially, this field contains the appreviated month
> name, concatenated with the last two digits of the year, making
> it effectively a text field. For instance, "DEC03" or "MAR04".

> >> What I want to do is sort on this column, but sort it by the

> actual date. If I sorted it as text, then "FEB04" would come
> before "JAN04", etc. >> I can create a function that would
> receive this field as input, and return some value, such as
> #01/01/2004# for "JAN04"....but I'm unsure how to incorporate
> that into a SQL statement.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Aaron Bertrand - MVP]

> Yes, plural. I've got an Access version of it that I'm using to develop
the thing, but when it goes live it will be on SQL Server. Lovely, isn't it?

Well, have you considered developing in MSDE (free) or SQL Server Developer
Edition ($49)?

Then you can develop in a single codebase and not have to worry about any
side effects of code conversion, syntax changes, upsizing, data type
changes, differing keyword violations, etc.

> "SELECT ACCT,[FORMATTED DATE] FROM Table ORDER BY convert([FORMATTED

DATE])"

Yes, but I still think you should consider adding a column that, upon
initial population of the table, has the date in the proper format. In SQL
Server you can use a computed column for this, or handle all inserts with a
stored procedure that does the work. In fact, I think this is the only
column you will need, because you can always generate the presentation
format after the fact. If it looks and smells like date, and you want it to
act like a date, you should store it as a date, not as a char(5).

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[MDW]

I'll let them worry about it in 96 years!

Besides, the data we're looking at is going to be current month, going back maybe 6 months at best.

I'f it'll make you feel better, I'll use Year(Date) somehow and ensure that if we're in year 2100+, it records the dates properly

And no, the function will probably be called convertAbbDate or something

----- Bob Barrows [MVP] wrote: ----

You DO realize that your database will have a Y2100 bug ..
Or are you planning on this to guarantee some work for your children ...
;-

Yes, something like what you propose will work, however, coming up with
single solution that will work in both Access and SQL Server will b
difficult (convert is the name of a builtin T-SQL function, so you would no
want to use that as the name of your custom function ... you'd want to use
less "generic" name anyways, wouldn't you?)

MDW wrote

> Sorry, I forgot to mention the databases I'm using

>> Yes, plural. I've got an Access version of it that I'm using t

> develop the thing, but when it goes live it will be on SQL Server
> Lovely, isn't it

>> Using your third option, would something like this work
>> "SELECT ACCT,[FORMATTED DATE] FROM Table ORDER BY convert([FORMATTE

> DATE])

>> Where [FORMATTED DATE] is the field that contains "MAR04" and conver

> is a function that can change that to 03/01/2004? (I would simpl
> change all the dates to the first of the month, as that wil
> accomplish the sort order I need. There isn't a specific dat
> attached to these records.

>> ----- Aaron Bertrand - MVP wrote: ----
>> You should keep another column which stores the date as a dat

> (as it should be). Or have *just* the date column

>> In case (1), you can hav
>> SELEC

> formattedDat
> FROM tabl
> ORDER BY realDat

>> In case (2), you can have something like this (not sure of th

> exact syntax in Access, which I assume you're using)

>> SELECT DATENAME('m', realDate) + RIGHT(YEAR(realDate, 2)

> FROM tabl
> ORDER BY realDat

>> Please, ALWAYS indicate what database you're using
>> As a final try, you could reverse engineer the abbreviation an

> make a computed column in the query only, and order by that

>> -

> Aaron Bertran
> SQL Server MV
> [url]http://www.aspfaq.com[/url]

>>>>> "MDW" <anonymous@discussions.microsoft.com> wrote in messag

> news:B3BB87DA-0ED7-446F-B739-4DD488991770@microsoft.com..

>> Hey all

>>> I've got a database I'm going to be linking to via ASP. It'

> got a "date" field that is very important for my users. Its dat
> come from an outside source and I don't have a lot of contro
> over it. Essentially, this field contains the appreviated mont
> name, concatenated with the last two digits of the year, makin
> it effectively a text field. For instance, "DEC03" or "MAR04"

>>> What I want to do is sort on this column, but sort it by th

> actual date. If I sorted it as text, then "FEB04" would com
> before "JAN04", etc. >> I can create a function that woul
> receive this field as input, and return some value, such a
> #01/01/2004# for "JAN04"....but I'm unsure how to incorporat
> that into a SQL statement

--
Microsoft MVP -- ASP/ASP.NE
Please reply to the newsgroup. The email account listed in my Fro
header is my spam trap, so I don't check it very often. You will get
quicker response by posting to the newsgroup

[Aaron Bertrand - MVP]

> And no, the function will probably be called convertAbbDate or something.

And then plan to rewrite your function from scratch when you move to SQL
Server. :-(

I honestly think you will be more efficient with time if you start with MSDE
or Personal Edition / Developer Edition.