How to use datebackbone in mysql query like in mssql query on the following......

Question

0.00/5 (No votes)

See more:

SQL

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)

What I have tried:

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

Posted 8-Feb-16 22:13pm

Wathon Aung

Add a Solution

Comments

_Asif_ 9-Feb-16 4:26am

DateBackbone is not part of MSSQL Server, its a user defined function. Share this UDF code of this function and what have you tried by the way?

Wathon Aung 9-Feb-16 4:38am

I have not this UDF I only use DateBackbone(Date1,Date2). DateBackBone is not implement in my MSSql Server.

_Asif_ 9-Feb-16 4:45am

Try responding following queries :|
* This SQL Code is running fine on MS SQL Server?
* If yes then can you share DateBackBone(Date1, Date2) code here? you can view this code by executing below query in SQL Window
sp_helptext 'dbo.DateBackbone'

Wathon Aung 9-Feb-16 23:08pm

Yeah I got this function dbo.DateBackbone from my Ms Sql Server, but how can I change this function for usinng in my Mysql database with phpMyAdmin?

CREATE FUNCTION [dbo].[DateBackbone](@StartDate Datetime,@EndDate DateTime)

RETURNS @Dates TABLE (Date DateTime) AS

BEGIN
While @StartDate <= @EndDate
BEGIN
insert @Dates (Date) Values (@StartDate)
Set @StartDate = @StartDate + 1
END
RETURN

END

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2016-02-08T22:45:00

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)

SQL

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

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)