如何在mysql查询中使用datebackbone，就像在mssql查询中一样...... [英] How to use datebackbone in mysql query like in mssql query on the following......

问题描述

declare @Date1 Datetime, @Date2 DateTime 
set @Date1 = '2016/1/1 00:00:00' 
set @Date2 = @Date1 +  DATEDIFF(d,'2016/1/1 00:00:00', '2016/1/31 23:59:59' )
SELECT Day(Date) AS DDate,Month(Date) AS MDate,Year(Date) as YDate
FROM dbo.DateBackbone( @Date1,  @Date2)

我试过的：



我还没有在MySql查询中找到使用DateBackBone函数它在MS_Sql Server查询中工作！

What I have tried:

I have not found using DateBackBone function in MySql query it is work in MS_Sql Server query!

推荐答案

在SQL Server或MySQL中没有DateBackBone这样的函数。



我只能假设你在引用找到的函数在这篇文章 - 即使没有记录，如何选择月份的所有日期？ - qtoue [ ^ ]。您或有权访问您系统的人已经创建了该函数（UDF）。



该函数在MSSQL上运行正常 - 它可能是您日期的格式造成了这个问题。您可以尝试使用' - '而不是'/'。



如果您实际上只是想生成当月的日期列表，请尝试以下方式（你可以把它放在一个函数中）

There no such function as DateBackBone in either SQL Server or MySQL.

I can only presume you are referring the function found at this post - How to select all the dates of month even though there are no records? - qtoue[^]. Either you or someone with access to your system has already created the function (UDF).

The function works fine on MSSQL - it may be the format of your dates that is causing the problem. You could try using '-' instead of '/' .

If you are actually just trying to generate the list of dates in the month then try the following instead (which you could put in a function)

declare @Date1 Datetime = '2016-1-1 00:00:00' 
declare @Date2 DateTime = @Date1 +  DATEDIFF(d,'2016-1-1 00:00:00', '2016-1-31 23:59:59' )
;WITH q AS
    (
    SELECT   @Date1 AS datum
    UNION ALL
    SELECT  datum + 1
    FROM    q
    WHERE datum + 1 <= @Date2
    )
SELECT Day(datum) AS DDate,Month(datum) AS MDate,Year(datum) as YDate
FROM    q

我从在SQL中生成序列 [ ^ ]（免责声明：我很有礼貌d与该文章的共同作者，但这项工作来自@ManasBhardwaj）

which I took from Generating a Sequence in SQL[^] (Disclaimer: I am credited with co-authorship of that article, but this work is from @ManasBhardwaj)

这篇关于如何在mysql查询中使用datebackbone，就像在mssql查询中一样......的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！