風雲論壇后台开发 → 浏览:帖子主题
分页: 1 2 3, 共 3 页
* 帖子主题:从ACCESS转为MSSQL后,遇到一个闰年2000-2-29日期问题
風雲 (ID: 3)
头衔:论坛版主
等级:天使
积分:1474
发帖:66
来自:保密
注册:2022/3/30 15:28:53
造访:2024/7/15 10:02:25
[ 第 25 楼 ] 回复
把 nongli 替换了下,看看行不行
sql="select count(*) as ttshu from shengri where (user="&session("user")&" and gongnong=0 and DateDiff(day,getdate(),dateadd(year,DateDiff(year,birthday,getdate()),birthday))between 0 and 14) or (user="&session("user")&" and gongnong=1 and DateDiff(day,'"&(jinnongli)&"',dateadd(year,DateDiff(year, concat(year(getdate ()),'-', case when shengrin in ('2-29', '2-30') then '2-28' else shengrin end ),'"&(jinnongli)&"'),concat(year(getdate ()),'-', case when shengrin in ('2-29', '2-30') then '2-28' else shengrin end ))between 0 and 14)"
2023/11/25 23:14:10 IP:已设置保密
風雲 (ID: 3)
头衔:论坛版主
等级:天使
积分:1474
发帖:66
来自:保密
注册:2022/3/30 15:28:53
造访:2024/7/15 10:02:25
[ 第 26 楼 ] 回复
看看如下描述是否正确?
mssql 表 shengri 中:
birthday 为公历生日,例如 2000-2-29
nongli 为农历生日,例如 2000-1-25
gongnong 为生日类型,例如 0 表示过公历生日(birthday),1 表示过农历生日(nongli)。

已知条件:今日公历为 2023-11-26,农历为 2023-10-14,请帮忙查询 未来 14天内过生日的列表。
要求考虑:
1、平年 2月29 改为 2月 28 生日,闰年继续使用 2-29
2、注意 12 月底过后 1月份的年份是明年
可以尝试问问 GPT 或者 文心一言。

以下是 GPT 生成的查询语句:
DECLARE @today DATE = '2023-11-26';
DECLARE @nongli DATE = '2023-10-14';

WITH Birthdays AS (
  SELECT
    *,
    DATEFROMPARTS(YEAR(@today), MONTH(birthday), CASE WHEN MONTH(birthday) = 2 AND DAY(birthday) = 29 AND NOT (YEAR(@today) % 4 = 0 AND (YEAR(@today) % 100 <> 0 OR YEAR(@today) % 400 = 0)) THEN 28 ELSE DAY(birthday) END) AS next_birthday_gongli,
    DATEDIFF(DAY, @today, DATEFROMPARTS(YEAR(@today), MONTH(birthday), CASE WHEN MONTH(birthday) = 2 AND DAY(birthday) = 29 AND NOT (YEAR(@today) % 4 = 0 AND (YEAR(@today) % 100 <> 0 OR YEAR(@today) % 400 = 0)) THEN 28 ELSE DAY(birthday) END)) AS days_to_next_birthday_gongli,
    DATEDIFF(DAY, @nongli, DATEFROMPARTS(YEAR(@today), MONTH(birthday), CASE WHEN MONTH(birthday) = 2 AND DAY(birthday) = 29 AND NOT (YEAR(@today) % 4 = 0 AND (YEAR(@today) % 100 <> 0 OR YEAR(@today) % 400 = 0)) THEN 28 ELSE DAY(birthday) END)) AS days_to_next_birthday_nongli
  FROM shengri
  WHERE gongnong = 0 OR (gongnong = 1 AND MONTH(@nongli) <= 9 AND MONTH(birthday) <= 9) OR (gongnong = 1 AND MONTH(@nongli) > 9 AND MONTH(birthday) > 9)
)
SELECT *
FROM Birthdays
WHERE days_to_next_birthday_gongli >= 0 AND days_to_next_birthday_gongli <= 14
ORDER BY days_to_next_birthday_gongli ASC;
2023/11/26 13:22:11 IP:已设置保密
tian (ID: 45)
等级:精灵
积分:125
发帖:6
来自:保密
注册:2023/11/24 9:14:32
造访:2024/7/13 18:07:31
[ 第 27 楼 ] 回复
Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]'concat' 不是可以识别的 内置函数名称。

把25楼的代码放到ASP文件里,提示上面的错误。
2023/11/26 15:26:45 IP:已设置保密
風雲 (ID: 3)
头衔:论坛版主
等级:天使
积分:1474
发帖:66
来自:保密
注册:2022/3/30 15:28:53
造访:2024/7/15 10:02:25
[ 第 28 楼 ] 回复
我写一个你看对不对:
-- 得到 14 天内生日的人
select * from (
    -- 计算离生日还有多少天
    select gongnong, today, birthday, datediff(day, today, birthday) remain from (
        -- 如果生日已经过了,改为明年对比
        select cast(case when birthday<today then dateadd(year, 1, birthday) else birthday end as date) birthday, today, gongnong from (
            -- 替换农历的 2-29, 2-30 为 2-28,防止出错
            select toyear + '-' + case when birthday in ('2-29', '2-30') then '2-28' else birthday end birthday, gongnong, today from(
                -- 取 mm-dd
                select gongnong, today, toyear, substring(cast(birthday as varchar), 6, 5) birthday from(
                    -- 取今天和生日日期
                    select cast(year(getdate()) as varchar) toyear, gongnong,
                    case gongnong when 0 then cast(getdate() as date) else '2023-10-15' end today,
                    case gongnong when 0 then birthday else nongli end birthday
                    from shengri where [user]=1
                ) a
            ) a
        ) a
    ) a
) a where a.remain<15
去掉换行和注释,改成你的 asp 代码就是:
sql = "select count(0) from (select gongnong, today, birthday, datediff(day, today, birthday) remain from (select cast(case when birthday<today then dateadd(year, 1, birthday) else birthday end as date) birthday, today, gongnong from (select toyear + '-' + case when birthday in ('2-29', '2-30') then '2-28' else birthday end birthday, gongnong, today from(select gongnong, today, toyear, substring(cast(birthday as varchar), 6, 5) birthday from(select cast(year(getdate()) as varchar) toyear, gongnong, case gongnong when 0 then cast(getdate() as date) else '"& jinnongli &"' end today, case gongnong when 0 then birthday else nongli end birthday from shengri where [user]="&session("user")&") a) a) a) a) a where a.remain<15"
需要注意的是,jinnongli 这个传入的变量也有可能出现 2-29 和 2-30,传入前需要处理成 2-28,否则同样解析成日期时会报错。
2023/11/26 17:26:54 IP:已设置保密
tian (ID: 45)
等级:精灵
积分:125
发帖:6
来自:保密
注册:2023/11/24 9:14:32
造访:2024/7/13 18:07:31
[ 第 29 楼 ] 回复
Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]类型 date 不是已定义的系统类型。


环境是asp+sql2005  windows2003 32系统。放到ASP文件里面,提示上面错误。

当把你写的语句里面有2个date改成datetime后,又提示下面的错误



Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]从字符串向 datetime 转换时失败。
2023/11/26 20:34:56 IP:已设置保密
風雲 (ID: 3)
头衔:论坛版主
等级:天使
积分:1474
发帖:66
来自:保密
注册:2022/3/30 15:28:53
造访:2024/7/15 10:02:25
[ 第 30 楼 ] 回复
那就没辙了,我在 2016 下测试的,使用 2-29 和 2-30 都没问题。没有 2005 环境。😢
屏幕截图 2023-11-26 205341.jpg
2023/11/26 20:39:53 IP:已设置保密
分页: 1 2 3, 共 3 页
快速回复主题
账号/密码
用户: 没有注册? 密码:
评论内容