Ddmmyyyy to SQL Datetime in SQL

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), '/', '')

ddmmyyyy to sql datetime in SQL

Rebuild your format to yyyymmdd.

declare @D varchar(8)
set @D = '23072009'

select cast(right(@D, 4)+substring(@D, 3, 2)+left(@D, 2) as datetime)

Convert datetime to ddmmyyyy in SQL server

Select Replace(CONVERT(VARCHAR(20), GETDATE(), 103),'/','')

OR

select substring(CONVERT(VARCHAR(10),GETDATE(),103),1,2) + substring(CONVERT(VARCHAR(10),GETDATE(),103),4,2) +
substring(CONVERT(VARCHAR(10),GETDATE(),103),7,4)

will does the same

Input: 02-07-2014

This will give output:

02072014

Unable to convert dd-mm-yyyy dateformat string to datetime in SQL Server

See the below query. It may help you.

SELECT FORMAT (CONVERT(DATETIME, '30-11-2017 11:53:53', 103),'dd-MM-yyyy HH:mm:ss');

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

How to convert a dd/mm/yyyy string to datetime in SQL Server?

The last argument of CONVERT seems to determine the format used for parsing. Consult MSDN docs for CONVERT.

111 - the one you are using is Japan yy/mm/dd.

I guess the one you are looking for is 103, that is dd/mm/yyyy.

So you should try:

 SELECT convert(datetime, '23/07/2009', 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 - Select between 2 dates of type DD/MM/YYYY

To query a range of dates, use the DATE-datatype instead of VARCHAR.

If datatype of column ORDERDATE is DATETIME:

WHERE CONVERT(DATE, ORDERDATE) BETWEEN
CONVERT(DATE, '17/10/2017', 103) AND CONVERT(DATE, '19/10/2017', 103)

The conversion of ORDERDATE is only necessary if the start and end date are the same. (in this case, when no conversion is done, only dates with a time value of '00:00:00.000' will be returned)

EDIT:
To omit the conversion of ORDERDATE you can add the time to the dates and convert them to DATETIME instead of DATE, like this:

WHERE ORDERDATE BETWEEN
CONVERT(DATETIME, '19/10/2017 00:00:00') AND CONVERT(DATETIME, '19/10/2017 23:59:59.999');

Or even simpler, like suggested in @Used_By_Already's answer:

WHERE ORDERDATE >= '20171017' AND ORDERDATE < '20171020' --Note the end date is here +1 day

---

Related Topics