Convert SQL Server Date to Mm-Yyyy

Convert SQL Server Date to mm-yyyy

You can use FORMAT function, available from SQL Server 2012 onwards:

DECLARE @myDate DATETIME = '2012-04-20 05:54:59'
SELECT FORMAT(@myDate, 'MM-yyyy') 

Output:

04-2012

Converting string MM/yyyy to date in sql query

Try this:

SELECT salaryMonth 
FROM viewSalary 
WHERE (CONVERT(date, '01/' + salaryMonth, 103) >= CONVERT(date, '02/22/2017', 103)

Convert dd/mm/yyyy in String to Date format using TSQL

If I understand you correct then you have a field that can have a date stored as varchar either like '19/07/2017' or like '20170719'

To convert a varchar field to date (not recommended using right column type is better) you can use the convert function. In the convert function you can add a parameter to tell the Convert function what format to expect for the convert.

More info about this function can be found here https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

Basic it looks like this :

convert(date, datefield, 103)

103 is the code for British/French (format dd/MM/yyyy)

Now look at these examples

declare @date varchar(30) = '20170719'

select case 
  when charindex('/', @date) > 0 then convert(date, @date, 103) 
  else convert(date, @date, 112) 
end as ConvertedDate

second example:

declare @date varchar(30) = '19/07/2017'

select case 
  when charindex('/', @date) > 0 then convert(date, @date, 103) 
  else convert(date, @date, 112) 
end as ConvertedDate

Both will succesfully convert the varchar into a date.

Notice that I enter a different format in the @date field each time, and in the select I first determine what format to use (is there a / in the value or not) and then use the correct value for the format parameter.

This is however not full proof offcourse since you never know what value can be in the varchar field.

EDIT:

The format how the date is shown is not depending on above queries. That depends on settings of your database.

If you want to always show it as dd/MM/yyyy you can use the format function.

Example :

select format(getdate(), 'dd/MM/yyyy')

returns for today:

19/07/2017 

in my example it would than be

declare @date varchar(30) = '20170719'

select format( case 
                 when charindex('/', @date) > 0 then convert(date, @date, 103) 
                 else convert(date, @date, 112) 
               end,
               'dd/MM/yyyy') as ConvertedDate

sql server convert date to string MM/DD/YYYY

That task should be done by the next layer up in your software stack. SQL is a data repository, not a presentation system

You can do it with

CONVERT(VARCHAR(10), fmdate(), 101)

But you shouldn't

How to convert string in 'DD/MM/YYYY' or 'YYYY-MM-DD' into date in SQL Server?

Use try_convert():

insert into @table
    select coalesce(try_convert(date, @string, 111),
                    try_convert(date, @string, 103)
                   ) as date

try_convert() returns NULL if the conversion fails. In that case, the conversion will move on to the next pattern. With coalesce(), you can have as many different formats as you like.

Convert dd/mm/yyyy to date in SQL Server

I suspect you have some bogus data. For example

 Select try_convert(date, '15/07/2014', 103)

Returns

2014-07-15

If 2012+, I would suggest that you

Select *
 From YourTable
 Where try_convert(date, StartDate, 103) is null

This will identify your problem areas

Convert date to YYYYMM format

SELECT LEFT(CONVERT(varchar, GetDate(),112),6)

---

Related Topics