使用日期YYYY的SQL语法

I've been given part of a project financial reporting system to work on. Basically, I'm trying to modify an existing query to restrict returned results by date in a field which contains a variable date format.

我已经获得了项目财务报告系统的一部分。基本上,我正在尝试修改现有查询,以便在包含可变日期格式的字段中按日期限制返回的结果。

My query is being sent the date from someone elses code in the format YYYY twice, so e.g. 2014 and 2017. In the SQL query below they are listed as 2014 and 2017, so you'll just have to imagine them as variables..

我的查询是以YYYY格式从某个elses代码发送的日期两次,例如2014年和2017年。在下面的SQL查询中,它们被列为2014年和2017年,因此您只需将它们想象为变量。

The fields in the database which have the variable date forms come in two forms: YYYYMMDD or YYYYMM.

数据库中具有可变日期形式的字段有两种形式:YYYYMMDD或YYYYMM。

The existing query looks like:

现有查询如下所示:

SELECT   
                    'Expense' AS Type,
                    dbo.Department.Description AS [Country Name],
                    dbo.JCdescription.Description AS Project,
                    dbo.Detail.AccountCode AS [FIN Code], 
                    dbo.JCdescription.ReportCode1 as [Phase Date Start], 
                    dbo.JCdescription.ReportCode2 as [Phase Date End], 
                    dbo.JCdescription.ReportCode3 as [Ratification Date], 
                    dbo.Detail.Year AS [Transaction Year], 
                    dbo.Detail.Period AS [Transaction Year Period], 
                    ...
                FROM
                    dbo.Detail
                INNER JOIN
                    ...
                WHERE
                    (dbo.Detail.LedgerCode = 'jc')
                ...
                AND
                    (dbo.Detail.Year) BETWEEN '2014 AND 2017";

Ideally I'd like to change the last line to:

理想情况下,我想将最后一行更改为:

(dbo.JCdescription.ReportCode2 LIKE '[2014-2017]%')

BUT this searches for all the digits 2,0,1,4,5 instead of everything between 2014 and 2017.

但这会搜索所有数字2,0,1,4,5而不是2014年和2017年之间的所有数字。

I'm sure I must be missing something simple here, but I can't find it! I realise I could rephrase it as LIKE '201[4567]%' but this means searches outside of 2010-2020 will error.. and requires me to start parsing the variables sent which will introduce an additional function which will be called a lot. I'd rather not do it. I just need the two numbers, 2014 and 2017 to be treated as whole numbers instead of 4 digits!

我相信我必须在这里遗漏一些简单的东西,但我找不到它!我意识到我可以将它改为LIKE'201 [4567]%',但这意味着2010-2020之外的搜索会出错...并且要求我开始解析发送的变量,这将引入一个将被称为很多的附加功能。我宁愿不这样做。我只需要两个数字,2014年和2017年被视为整数而不是4位数!

Running on MS SQL server 10.0.5520

在MS SQL Server 10.0.5520上运行

3 个解决方案

#1

I guess you could use the year system function if your date columns are any of the given date or datetime data types available in SQL Server.

如果您的日期列是SQL Server中可用的任何给定日期或日期时间数据类型,我想您可以使用年度系统函数。

This would allow you to write something that looks pretty much like the following.

这将允许您编写看起来非常类似于以下内容的内容。

where year(MyDateColumn) between 2014 and 2017

Besides, if you're using varchar as the date column data type, you will have to cast them to the appropriate and comparable values, and you'll also have to make sure to get the only required substring you need for the comparison.

此外,如果您使用varchar作为日期列数据类型,则必须将它们转换为适当且可比较的值,并且还必须确保获得比较所需的唯一必需子字符串。

So let's suppose you have string value like '201505' in your dateStringColumn.

所以我们假设你的dateStringColumn中有像'201505'这样的字符串值。

where cast(substring(dateStringColumn, 1, 4) as int) between 2014 and 2017

#2

So from my understanding you have 2 parameters for the start and end years that should be used to filter results.

因此,根据我的理解,您有2个参数用于开始和结束年份,应该用于过滤结果。

With dates either being YYYYMM or YYYYMMDD, you can use string manipulation to take the first 4 characters and convert it to an INT, which can then be used to compare to the parameters.

如果日期为YYYYMM或YYYYMMDD,则可以使用字符串操作来获取前4个字符并将其转换为INT,然后可以将其用于与参数进行比较。

Something like:

CREATE TABLE #detail ( id INT , SomeDate NVARCHAR(10) )
INSERT  INTO #detail
        ( id, SomeDate )
VALUES  ( 1, '201311' ),
        ( 2, '201402' ),
        ( 3, '20140313' ),
        ( 4, '201409' ),
        ( 5, '201506' ),
        ( 6, '20150912' ),
        ( 7, '201612' ),
        ( 8, '201701' ),
        ( 9, '20181212' )

DECLARE @startYear INT = 2014, @endYear INT = 2017

SELECT *, CONVERT(INT, (LEFT(SomeDate, 4))) AS [Year]
FROM #detail
WHERE CONVERT(INT, (LEFT(SomeDate, 4))) BETWEEN @startYear AND @endYear 

DROP TABLE #detail

Based on the sample data, this would produce:

根据示例数据,这将产生:

id  SomeDate    Year
====================
2   201402      2014
3   20140313    2014
4   201409      2014
5   201506      2015
6   20150912    2015
7   201612      2016
8   201701      2017

(excludes rows 1 & 9)

(不包括第1行和第9行)

#3

use.DATEPART

datepart(year,dbo.Detail.Year) BETWEEN 2014 AND 2017;

#1