How to Convert the System Date Format to Dd/Mm/Yy in SQL Server 2008 R2

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

   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

Convert Date format into DD/MMM/YYYY format in SQL Server

I'm not sure there is an exact match for the format you want. But you can get close with convert() and style 106. Then, replace the spaces:

SELECT replace(convert(NVARCHAR, getdate(), 106), ' ', '/')

How to display the date as mm/dd/yyyy hh:mm Am/PM using sql server 2008 r2?

I think there is no single format to give them both. Try this using Convert; Sql-Demo

declare @mydate datetime = getdate()
select convert(varchar(10),@mydate, 101) + right(convert(varchar(32),@mydate,100),8)

|           COLUMN_0 |
----------------------
| 02/22/2013  9:36AM |

How to format date on MS SQL Server 2008

I had a similar problem in SQL 2012 they have a great function called format which you could pass in a date and the format you require

SELECT FORMAT(GETDATE(), 'dd/mm/yyy');

put there is nothing similar in SQL Server 2008. I created a simple function to do a similar job, it might need tweaking but is not a bad base.

-- =============================================
-- Author:      Luke Mc Redmond
-- =============================================
CREATE FUNCTION [dbo].[FormatDate]
(
-- Add the parameters for the function here
@Date   DATETIME,
@Format NVARCHAR(50)
)
RETURNS NVARCHAR(50)
AS
 BEGIN

   DECLARE @ResultVar nvarchar(50) = UPPER(@Format);

   DECLARE @d nvarchar(20) = CONVERT(nvarchar(20), DATEPART(day, @Date));
   DECLARE @dd nvarchar(20) = CONVERT(nvarchar(20), DATEPART(day, @Date));
   DECLARE @day nvarchar(20) = DATENAME(weekday, @Date);
   DECLARE @ddd nvarchar(20) = DATENAME(weekday, @Date)+' '+CONVERT(nvarchar(5), DATEPART(day, @Date));

   DECLARE @m nvarchar(20) = CONVERT(nvarchar, DATEPART(month, @Date));
   DECLARE @mm nvarchar(20) = CONVERT(nvarchar, DATEPART(month, @Date));
   DECLARE @mmm nvarchar(20) = CONVERT(VARCHAR(3), DATENAME(month, @Date), 100);
   DECLARE @month nvarchar(20) = DATENAME(month, @Date);

   DECLARE @y nvarchar(20) = CONVERT(nvarchar, DATEPART(year, @Date));
   DECLARE @yy nvarchar(20) = RIGHT(CONVERT(nvarchar, DATEPART(year, GETDATE())),2);
   DECLARE @yyy nvarchar(20) = CONVERT(nvarchar, DATEPART(year, @Date));
   DECLARE @yyyy nvarchar(20) = CONVERT(nvarchar, DATEPART(year, @Date));
   DECLARE @year nvarchar(20) = CONVERT(nvarchar, DATEPART(year, @Date));

   SELECT @ResultVar = CASE WHEN CHARINDEX('DAY',@ResultVar) > 0 THEN  REPLACE(@ResultVar collate Latin1_General_CS_AS ,'DAY' collate Latin1_General_CS_AS ,@day)
                        WHEN CHARINDEX('DDD',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS ,'DDD' collate Latin1_General_CS_AS ,@ddd)
                       WHEN CHARINDEX('DD',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS ,'DD' collate Latin1_General_CS_AS ,@dd)
                       WHEN CHARINDEX('D',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS,'D' collate Latin1_General_CS_AS ,@d) END;

   SELECT @ResultVar = REPLACE(@ResultVar collate Latin1_General_CS_AS,'Monday' collate Latin1_General_CS_AS ,'monday')

   SELECT @ResultVar = CASE WHEN CHARINDEX('MONTH',@ResultVar) > 0 THEN  REPLACE(@ResultVar collate Latin1_General_CS_AS,'MONTH' collate Latin1_General_CS_AS,@month)
                       WHEN CHARINDEX('MMM',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS ,'MMM' collate Latin1_General_CS_AS,@mmm)
                       WHEN CHARINDEX('MM',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS,'MM' collate Latin1_General_CS_AS,@mm)
                       WHEN CHARINDEX('M',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS,'M' collate Latin1_General_CS_AS,@m) END;

   SELECT @ResultVar = CASE WHEN CHARINDEX('YEAR',@ResultVar) > 0 THEN  REPLACE(@ResultVar collate Latin1_General_CS_AS,'YEAR' collate Latin1_General_CS_AS ,@year)
                       WHEN CHARINDEX('YYYY',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS,'YYYY' collate Latin1_General_CS_AS ,@yyyy)
                       WHEN CHARINDEX('YYY',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS ,'YYY' collate Latin1_General_CS_AS ,@yyy)
                       WHEN CHARINDEX('YY',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS ,'YY' collate Latin1_General_CS_AS ,@yy)
                       WHEN CHARINDEX('Y',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS,'Y' collate Latin1_General_CS_AS ,@y) END;

   SELECT @ResultVar = REPLACE(@ResultVar collate Latin1_General_CS_AS,'monday' collate Latin1_General_CS_AS ,'Monday')

   RETURN @ResultVar

 END;
 GO

Hope this helps!

Return date as ddmmyyyy in SQL Server

CONVERT style 103 is dd/mm/yyyy. Then use the REPLACE function to eliminate the slashes.

SELECT REPLACE(CONVERT(CHAR(10), [MyDateTime], 103), '/', '')

Convert DD/MM/YYYY to YYYY-MM-DD at SQL server 2008 R2

In SSRS Report Date format Change use for this concept.

create table az(id integer,Dob varchar(20));

insert into az values(1, '25/03/2014')
insert into az values(2, '26/03/2014')
insert into az values(3, '27/03/2014')
insert into az values(4, '28/03/2014')
select * from az

Use this Line for your Textbox Field Expression:

=IIF(IsNothing(Fields!Dob.Value),"",Mid(Fields!Dob.Value,7,4) + "-" + 
Mid(Fields!Dob.Value,4,2) + "-" + Left(Fields!Dob.Value,2))

How to get a particular date format ('dd-MMM-yyyy') in SELECT query SQL Server 2008 R2

Try this:

Declare @dt NVARCHAR(20)

Select 
    @dt = REPLACE(CONVERT(CHAR(15), SalesDate, 106),' ',' - ') 
FROM SalesTable

---

Related Topics