How do I convert a varchar to a date and order by it in SQL?

Question

1.00/5 (1 vote)

See more:

Hi,

I have a select statement in the code below that will always display as a 5 character month and year (example: JAN14) as a string. The problem I am having is I can't seem to get it to order the correct way when it displays as an option in a parameter.

I know I have to convert it to a date time, but I can't seem to get this to work. I need to concatenate the day into the code as the 5 characters selected do not include that. Does anyone know how this may be accomplished? Thanks in advance. The code is below- the first line selects the string in question.

SQL

SELECT right(barcode,5) AS barcode,
assettype."aname" AS aname

FROM
     "public"."assettype" assettype INNER JOIN "public"."asset" asset ON assettype."id" = asset."assettype_id"

WHERE
asset."client_id" = $P!{LoggedInUserAttribute_ClientID}
and aname = 'Micro DPC'
and SUBSTRING(barcode,7,3) IN ('JAN','FEB','APR','MAY','JUN','JUL','AUG',
'SEP','OCT','NOV','DEC')
order by right(convert(datetime,'15' +  'barcode', 6),5)

Posted 12-Oct-14 12:48pm

Member 11147914

Updated 12-Oct-14 16:16pm

ChauhanAjay

v2

Add a Solution

Comments

Sergey Alexandrovich Kryukov 12-Oct-14 21:01pm

First of all, don't store time data as varchar, use appropriate data types, such as DATE.
—SA

Member 11147914 12-Oct-14 21:09pm

Sergey,

Thanks for the response-

I actually don't have control of that date- it is actually a bar code field that always ends in a month and year (JAN14, FEB14, MAR13 etc.) so I am trying to take those characters and convert them to a date to order them, ideally they would be converted like so- JAN14 would become 15JAN14.

It may not be possible- but I assume there is a way?

Sergey Alexandrovich Kryukov 12-Oct-14 21:16pm

Convert does it — see you SQL reference....
—SA

Member 11147914 12-Oct-14 22:43pm

Convert doesn't seem to do it- I think it is because the string only represents the Month and year (JAN14)- is there a way to concatenate the day with that? I tried style 6 which would return dd,mon,yy but that didn't work. Or possibly I have the wrong syntax?

Sergey Alexandrovich Kryukov 12-Oct-14 23:05pm

Well, use SQL CONCAT...
—SA

Maciej Los 13-Oct-14 2:41am

What version of MS SQL Server?

Member 11147914 13-Oct-14 12:00pm

I am using SQL in an single select SQL query in an Input Control for Jasper Ireports- the server is postgresSQL 9.3

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abdul Samad KP · Answer 1 · 2014-10-12T19:41:00

Solution 1

Change

VB

right(convert(datetime,'15' +  'barcode', 6),5)

to

convert(datetime,'15' + right( 'barcode', 5),6)

This is working in SQL Server 2008

Posted 12-Oct-14 19:41pm

Abdul Samad KP

Comments

Member 11147914 13-Oct-14 12:04pm

Abdul,

Thanks- I'm having no luck with this either. I apologize, but I probably should have been more specific in where I'm using this- it is a Single Select SQL query in Jasper Ireports 5.0.

I would think that the latest SQL version would apply here. The Server is in Postgres sql 9.3

The query works fine until I add the order by and try to convert to a datetime, so I'm pretty sure SQL would still apply here, but maybe I'm looking in the wrong place.

How do I convert a varchar to a date and order by it in SQL?

1 solution

Solution 1

Add your solution here

Preview 0

How do I convert a varchar to a date and order by it in SQL?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us