Convert date format in the select statement

Question

I am trying to convert date in the output to a format of dd-mm-yyyy. I have tried converting just the date alone, but how do I go about converting the date only when the select statement is showing output.

For example this is my query

select * 
from dbo.Car 
where 
    dbo.Car.[Date Registered] > DATEADD(year, -3, GetDate()) 
    and dbo.Car.rentalCost < 350 
ORDER by 
    [Date Registered] desc, brand asc;

My current output for the above statement

In the above query I am selecting everything and one of the columns is DateRegistered which is a date column, so how I do I convert only the date column? Should I specify each column after the select, like this?

select 
   column1, column2, column3,
   Convert(varchar(10), CONVERT(date, Datecolumn4, 105), 103)...

is there an easier solution?

Answer 1

I have created this SP that dynamically creates a column list and converts all columns with Datetime and Date datatypes to dd-mm-yyyy. At the least it will help in writing a lot of converts manually.

CREATE PROCEDURE usp_ColumnToStringDate(@p_TableName SYSNAME, @p_FilterString VARCHAR(4000) = NULL)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @TableName SYSNAME
    DECLARE @ColumnName SYSNAME
    DECLARE @SchemaName SYSNAME
    DECLARE @DataType SYSNAME
    DECLARE @StartLoop INT
    DECLARE @EndLoop INT
    DECLARE @SQLColumnList VARCHAR(4000)
    DECLARE @SQL VARCHAR(4000)
    DECLARE @FilterString VARCHAR(4000)


    SET @TableName = @p_TableName
    SET @FilterString = @p_FilterString

    DECLARE @TargetTable TABLE (
                OrderID INT IDENTITY(1, 1),
                TableSchema SYSNAME,
                TableName SYSNAME,
                ColumnName SYSNAME,
                DataType SYSNAME
            )

    INSERT INTO @TargetTable
    SELECT   QUOTENAME(c.TABLE_SCHEMA),
             QUOTENAME(c.TABLE_NAME),
             QUOTENAME(c.COLUMN_NAME),
             UPPER(c.DATA_TYPE)
    FROM     INFORMATION_SCHEMA.COLUMNS c
    WHERE    c.TABLE_NAME = @TableName
    ORDER BY c.ORDINAL_POSITION

    --SELECT * FROM @TargetTable

    SELECT   @StartLoop = MIN(tt.OrderID),
             @EndLoop        = MAX(tt.OrderID),
             @SchemaName     = MIN(tt.TableSchema),
             @TableName      = MIN(tt.TableName)
    FROM     @TargetTable tt

    WHILE @StartLoop <= @EndLoop
    BEGIN
        SELECT   @DataType = tt.DataType,
                 @ColumnName = tt.ColumnName
        FROM     @TargetTable tt
        WHERE    tt.OrderID = @StartLoop

        --PRINT @ColumnName+' '+@DataType

        SET @SQLColumnList = ISNULL(@SQLColumnList, '')
            + CASE 
                   WHEN @DataType IN ('DATETIME', 'DATE') THEN CHAR(9) + CHAR(9)
                        + 'CONVERT(VARCHAR(10),' + @ColumnName + ', 105)'
                   ELSE CHAR(9) + CHAR(9) + @ColumnName
              END

        IF @StartLoop != @EndLoop
        BEGIN
            SET @SQLColumnList = @SQLColumnList + ',' + CHAR(13) + CHAR(10)
        END

        IF @StartLoop = 1
        BEGIN
            SET @SQLColumnList = 'SELECT' + REPLACE(@SQLColumnList, CHAR(9), ' ')
        END


        SET @StartLoop = @StartLoop + 1
    END
    SET @SQL = @SQLColumnList + CHAR(13) + CHAR(10) + 'FROM ' + @TableName + CHAR(13) + CHAR(10)
    SET @SQL = COALESCE(@SQL+@FilterString,@SQL)
    PRINT @SQL
END 

Usage:

--SELECT Statement
EXEC usp_ColumnToStringDate 'ProductListPriceHistory',NULL

--SELECT Statement with Filter
EXEC usp_ColumnToStringDate 'ProductListPriceHistory','WHERE ProductID = ''1'''

--Use in other SP or assign to parameters
DECLARE @DSQL VARCHAR(MAX)

EXEC @DSQL =  usp_ColumnToStringDate 'ProductListPriceHistory','WHERE ProductID = ''1'''
PRINT REVERSE(SUBSTRING(REVERSE(@DSQL),2,LEN(@DSQL)))

Answer 2

I think it's better to specify each column name in your select statement and for the date column use:

select col1, col3, col3, CONVERT(varchar(10),[Date Registered], 105), col5, col6, col7
from dbo.Car 
where dbo.Car.[Date Registered] > DATEADD(year, -3, GetDate()) and  dbo.Car.rentalCost < 350 
ORDER by [Date Registered] desc, brand asc;