SQL Date Format

How to get a date in YYYY-MM-DD format from a TSQL datetime field?

SELECT CONVERT(char(10), GetDate(),126)

Limiting the size of the varchar chops of the hour portion that you don't want.

SQL: how to specify a date format on creating a table and fill it

You don't need to specify the format in the table definition as dates are stored in a binary format.

CREATE TABLE APP(
    ID   INT NOT NULL,
    DT DATE,
    ADDRESS  NVARCHAR (100) ,
    PRIMARY KEY (ID)
);

When you try to insert into that table however, the server will try to convert the string to a date before inserting it. This can be problematic as it is unable to tell if 12.11.2017 is the 12th of November or 11th of December. To figure this out it uses the localization settings of the user account that is performing the operation.

Often you will find that the account that is running the operation is set to USA format, month day then year (MDY), when what you want is day month year (DMY) format. One way to tell it what the sequence of the date's parts is to use the DATEFORMAT setting like this:

SET DATEFORMAT dmy;

INSERT INTO APP (ID, DT)
VALUES (1,'22.12.2016')

Another alternative is to cast the string to a date using the CONVERT function and tell it what the date format is. The formats have numeric codes like 104 for German format Like this:

INSERT INTO APP (ID, DT)
VALUES (2,CONVERT(date,'22.12.2016',104))

How to change sql date format of existing records in table?

I assume the column is really a string, not a datetime.

SQL Server has a pretty flexible conversion to date. First check to be sure that all the dates convert:

select col
from t
where try_convert(date, col) is null and col is not null;

If you are satisfied with the conversion, you can do:

update t
    set col = try_convert(date, col);

alter table t alter col date;

Multiple date format in on column

Each string representation format potentially can require own conversion date style, therefore:

with cte as (
select '2017-06-14' DateString
union all
select '04/09/15' -- expected dateformat: dd/mm/yy
)

select 
case when DateString like '%-%-%' then CONVERT(DATE, DateString, 20) -- ODBC canonical
     when DateString like '%/%/%' then CONVERT(DATE, DateString, 3) -- French 
     else null
end
from cte

Results to:

2017-06-14
2015-09-04

How to change datetime format in query?

This is what finally worked:
I Casted the datetime and used FORMAT() with a custom format. The other solutions I tried where a little bit slower.

WHERE s.zeitpunkt
  BETWEEN 
    CAST( 
        FORMAT(CAST($__timeFrom() AS DATETIME),'yyyyMMddHHmmss')
      AS VARCHAR)
    AND CAST(
        FORMAT(CAST($__timeTo() AS DATETIME),'yyyyMMddHHmmss')
      AS VARCHAR)

---

Related Topics