如何在SQL中将varchar转换为日期和顺序？ [英] How do I convert a varchar to a date and order by it in SQL?

问题描述

我在下面的代码中有一个select语句，它将始终显示为5个字符的月份和年份（例如：JAN14）作为字符串。我遇到的问题是，当它在参数中显示为一个选项时，我似乎无法按顺序排序。



我知道我必须将它转换为日期时间，但我似乎无法让它工作。我需要将这一天连接到代码中，因为所选的5个字符不包含该字符。有谁知道如何实现这一目标？提前致谢。代码在下面 - 第一行选择有问题的字符串。

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.

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)

推荐答案

P！{LoggedInUserAttribute_ClientID}
和 aname = ' Micro DPC'
和 SUBSTRING（条形码， 7 ， 3 ） IN （' JAN'，< span class =code-string>' FEB'，' APR'，' MAY'，' JUN'，' JUL'，' AUG'，
' SEP'，' OCT'，' NOV'，' DEC'）
order by right （ convert （ datetime ，' 15' + ' 条形码'， 6 ）， 5 ）

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)

更改

Change

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

到

to

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

这适用于SQL Server 2008

This is working in SQL Server 2008

这篇关于如何在SQL中将varchar转换为日期和顺序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！