在表格中搜索日期，并且仅在字段不为null时返回 [英] Searching dates in table and only return when fields are not null

问题描述

嗨

我想知道是否有人可以快速帮助我将一些SQL查询组合在一起.我有一些我想返回到报表中的数据，从数据库表中选择了一些字段.

字段如下:

VERSION_ID_FK
DATE_TYPE
FROM_DAY
FROM_MONTH
FROM_YEAR
TO_DAY
TO_MONTH
TO_YEAR


因此，基本上我想摆脱的是，客户可以通过某种方式在系统中输入日期，有时他们只输入年份而没有日和月，或者他们只输入年份和年而没有日.

因此，如果有人可以帮助首先检查是否有一天，那么我想使用sql，如果这些字段不为null，则返回FROM_TO，FROM_MONTH，FROM_YEAR，TO_DAY，TO_MONTH，To_YEAR中的一个或两个.这些字段通过Version_ID_FK链接.

然后，下一个检查将是FROM_DAY还是TO_DAY为空，则返回FROM_MONTH，FROM_YEAR TO_MONTH，TO_YEAR.如果日和月都为空，则只返回其中一个或两个的年份，具体取决于哪一个中有数据.

我希望这很清楚，希望有人能够迅速帮助我.

提前致谢.如果您有任何疑问，请告诉我

所以这是某人已经放置的当前SQL，但是我现在已将上面提到的字段添加到表中

Hi

I wonder is someone could quickly help me out put some SQL query together. I have some data that i would like to return back into a report, selecting some fields from the database table.

Fields as follows:

VERSION_ID_FK
DATE_TYPE
FROM_DAY
FROM_MONTH
FROM_YEAR
TO_DAY
TO_MONTH
TO_YEAR


So basically what i want to get out of this is that the client has got a way in which they enter dates in system and sometimes they only just enter the year without the day and month or they only enter month and year without the day.

So i want the sql please if someone can help to check first is there a day then return either or both FROM_TO, FROM_MONTH, FROM_YEAR, TO_DAY, TO_MONTH, To_YEAR if these fields are not null. These fields are linked bu the Version_ID_FK.

Then next check would be if FROM_DAY or TO_DAY is blank return FROM_MONTH, FROM_YEAR TO_MONTH, TO_YEAR. and if both day and month are null just return the year for either both or just one of them depending which one has got data in them.

I hope that is clear and hope someone will be able to help me out quickly.

Thanks in advance. Let me know if you have any questions

So here is the current SQL that someone had already put in place, but i have now added the fields mentioned above to the table

select p.prod_date_id, p.version_id_fk, p.DATE_TYPE_ID, p.date_type, p.date_type, p.date_from, p.date_to, p.circa, p.notes, p.created_by, p.created_date, p.modified_by, p.modified_date from
mdd_version_production_dates p, mdd_versions v
where v.version_id = p.version_id_fk
and v.version_id1 = (case when :VERSIONID1 is null then v.version_id1 else :VERSIONID1 end)

推荐答案

尝试一下:

Try this:

SELECT <List_Field>
FROM <TableName>
WHERE NOT DateField IS NULL

或使用 ISNULL [ COALESCE [ ^ ]函数将NULL替换为默认新值.

or use ISNULL[^] or COALESCE[^] function to replace NULL with default new value.

SELECT COALESCE(NULL, '1899-01-01') AS DefaultNewDate

这篇关于在表格中搜索日期，并且仅在字段不为null时返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！