Question

I have a database/table in SQLITE using the following structure

CREATE TABLE milestones ( 
    ID       INT( 10 )        NOT NULL,
    Title    VARCHAR( 50 )    DEFAULT NULL,
    mYear    INT( 11 )        NOT NULL,
    mMonth   INT( 11 )        DEFAULT NULL,
    mDay     INT( 11 )    DEFAULT NULL,
    mText    VARCHAR( 2000 )  NOT NULL,
    Theme1   VARCHAR( 50 )    DEFAULT NULL,
    Theme2   VARCHAR( 50 )    DEFAULT NULL,
    ImageURL VARCHAR( 50 )    DEFAULT NULL,
    PRIMARY KEY ( ID ) 
);

我正在利用以下询问,使5个最接近的日期与今天的日期挂钩。

SELECT dtable.ID, dtable.Date 
FROM(SELECT ID, date(mYear|| - ||mMonth|| - ||mDay) as Date 
FROM milestones
WHERE mMonth IS NOT NULL AND mDay IS NOT NULL AND mMonth ="+varMonth+")AS dtable
ORDER BY ABS("+varDay+"-date(dtable.Date,  %d ))
LIMIT 5;

The problems using this query is that I am only getting records with the date format yyyy-mm-dd. If there is any record with 1 digit month or day ( the format yyyy-m-d or yyyy-mm-d or yyyy-m-dd) it doesn t show in the result.

How can I solve this problem without changing the datatype of mMonth or mDay?

Answer 1

如果你想用零铺面,确保宽2英寸,那么你就可以利用这种简单脏的滴滴滴滴,利用现有的几根基:

SUBSTR( 00 ||month,-2)

Answer 2

I think a potential reason you are not getting matches for single digit months is because your +varMonth+ itself is formatted to be "04" for example. You are likely treating them as strings and there is an implicit (string)int cast happening so "4"="04" does not match. Not sure why that would also effect +varDay+ though because I would assume for the subtraction its being cast back to an int, so something like (int)((string)int) is happening.

你们能否对此做出解释?

我在这里也看到了一个缺陷,即最近的5个日期限于本月,因此,如果今天是你本月的第一个/最后一天,你本月将只会取得成果。

询问:

SELECT dtable.ID, dtable.Date  
FROM (SELECT ID, date(mYear|| - ||mMonth|| - ||mDay) as Date  
FROM milestones WHERE mMonth IS NOT NULL AND mDay IS NOT NULL) AS dtable 
ORDER BY ABS(date("+varYear+"|| - ||"+varMonth+"|| - ||"+varDay+") - dtable.Date) ASC  
LIMIT 5;

友情链接