How to convert the system date format to dd/mm/yy in SQL Server 2008 R2?

sql, sql-server, sql-server-2005, sql-server-2008, sql-server-2008-r2

Solution

   SELECT CONVERT(varchar(11),getdate(),101)  -- mm/dd/yyyy

   SELECT CONVERT(varchar(11),getdate(),103)  -- dd/mm/yyyy

Check this . I am assuming D30.SPGD30_TRACKED_ADJUSTMENT_X is of datetime datatype . That is why i am using `CAST()` function to make it as an character expression because `CHARINDEX()` works on character expression. Also I think there is no need of OR condition.

select case when CHARINDEX('-',cast(D30.SPGD30_TRACKED_ADJUSTMENT_X as varchar )) > 0 

then 'Score Calculation - '+CONVERT(VARCHAR(11), D30.SPGD30_TRACKED_ADJUSTMENT_X, 103)
end

EDIT:

select case when CHARINDEX('-',D30.SPGD30_TRACKED_ADJUSTMENT_X) > 0 
then 'Score Calculation - '+
CONVERT( VARCHAR(11), CAST(D30.SPGD30_TRACKED_ADJUSTMENT_X as DATETIME) , 103)
end

See this link for conversion to other date formats: https://www.w3schools.com/sql/func_sqlserver_convert.asp

Problem

I'm using SQL Server 2008 R2. I want to convert the system date to this format: `dd/mm/yy` ``` "2013-01-01 00:00:00.000" to "Score Calculation - 10/01/13". ``` My column contains the data: ``` 1. DMS01A13010101 2. RMS01A13010201 3. 44 4. 2013-01-01 00:00:00.000 ``` What I want: if the record has `2013-01-01 00:00:00.000` in this format then only I change to `Score Caculation - dd/mm/yy` My code is, ``` select case when (CHARINDEX(D30.SPGD30_TRACKED_ADJUSTMENT_X, '-*') > 0 or CHARINDEX(D30.SPGD30_TRACKED_ADJUSTMENT_X, '*-') > 0) then 'Score Calculation - ' + CONVERT(VARCHAR(8), D30.SPGD30_TRACKED_ADJUSTMENT_X, 1) end checkthedate from CSPGD30_TRACKING D30 ```

Original source