Dates returned as columns in SQL Select

Question

My user will submit a FromDate and a ToDate. What I want to happen is to select the dates that fall in between these dates, which I have accomplished with the script below. The dates will by dynamic.

DECLARE @fromDateParam DATETIME = '2022-01-24 00:00:00.000'
DECLARE @toDateParam DATETIME = '2022-01-29 00:00:00.000'

;WITH fnDateNow(DayOfDate) AS
(
    SELECT @fromDateParam AS TransactionDate

    UNION ALL

    SELECT DayOfDate + 1 
    FROM fnDateNow 
    WHERE DayOfDate < @toDateParam
) 
SELECT fnDateNow.DayOfDate AS TransactionDate 
FROM fnDateNow

This returns that dates as rows. What I am looking for is a way to make these dates return as the columns for a different script.

This table is called DailyTransactionHeader and it has a column [TransactionDate] and another one called [Amount].

There is the probability that their is not a DailyTransactionHeader with the specified Date for this I am looking to return 0.

So I am trying to have the data look like this (I formatted the date) There would be more than one row, but I just wanted to show an example of what I am trying to accomplish.

I appreciate any help,

Thanks

Does this answer your question? [SQL Server- PIVOT Table to one column per date](https://stackoverflow.com/questions/52226242/sql-server-pivot-table-to-one-column-per-date) — SMor, Feb 04 '22 at 18:40
You're using dates - so why is your code written using datetime? Don't create or encourage errors. Since you dynamically define the range of dates, you will need to dynamically pivot your rows into columns. And please stop using that lazy kludge of adding a statement **terminator** (semicolon) immediately before a CTE. Use terminators consistently (which will eventually be required) and you don't need that hack. — SMor, Feb 04 '22 at 18:42
Why not use a calendar table instead of a CTE? Using a CTE like that will probably limit you to a 100 day window due to `MAXRECURSION`. Also SQL Server has limits of 4096 columns in a select statement and 1024 columns in tables/views so at some point you'll need to think about growth and/or limiting how much data report users can request from the front end. — AlwaysLearning, Feb 05 '22 at 00:20

score 1 · Accepted Answer · answered Feb 10 '22 at 02:27

You can do it using dynamic sql. For example:

CREATE PROCEDURE [GET_DATE_TABLE] 
(
    @FROMDATE DATETIME, 
    @TODATE DATETIME
)
AS
DECLARE @PDATE DATETIME 
DECLARE @SQL VARCHAR(MAX) 
DECLARE @SEP VARCHAR(10)

    SET @PDATE = @FROMDATE
    SET @SQL = 'SELECT '
    SET @SEP = ''
    
    WHILE @PDATE < @TODATE
    BEGIN
        SET @SQL = @SQL + @SEP + 'NULL as [' + CONVERT(VARCHAR, CONVERT(DATE, @PDATE)) + ']'
        
        SET @PDATE = @PDATE + 1
        SET @SEP = ', '
    END;

    EXEC(@SQL)

Test Example:

DECLARE @fromDateParam DATETIME = '2022-01-24 00:00:00.000'
DECLARE @toDateParam DATETIME = '2022-01-29 00:00:00.000'
exec dbo.GET_DATE_TABLE @fromDateParam, @toDateParam

Dates returned as columns in SQL Select

1 Answers1