SQL Server Datetime Format

How to format datetime in SQL SERVER

In SQL Server 2012 and up you can use FORMAT():

SELECT FORMAT(CURRENT_TIMESTAMP, 'yyyy-MM-dd hh:mm:ss tt')

In prior versions, you might need to concatenate two or more different datetime conversions to get what you need, for example:

SELECT 
    CONVERT(CHAR(10), CURRENT_TIMESTAMP, 23) + ' ' + 
    RIGHT('0' + LTRIM(RIGHT(CONVERT(CHAR(20), CURRENT_TIMESTAMP, 22), 11)), 11);

See the Date and Time Styles section of CAST and CONVERT (Transact-SQL) for all of the built-in formatting styles.

---

I would keep in mind that unless you have a good reason for it, I mean a really good reason, formatting is usually a better job for the technology displaying the data.

SQL SERVER DATETIME FORMAT

The default date format depends on the language setting for the database server. You can also change it per session, like:

set language french
select cast(getdate() as varchar(50))
-->
févr 8 2013 9:45AM

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)

Datetime format in MSSQL

Check the defualt language on your MS SQL Server 2008, and if its possible, you can change to Spanish: How to change Date Format after installing SQL Server

Also, you can format your query output with the CONVERT function (@ZLK post the link) using the format 103: CONVERT (DATETIME,[YOURDATE],103)

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.

Which settings provide SQL Server default datetime format?

Don't do that! Don't use strings instead of dates and don't use a culture-specific date or datetime format, it's a recipe for disaster.

SQL Server doesn't have a "date format", just formats it uses to convert dates to and from strings. One of them is the default, controlled by the server's collation. You can neither assume nor should you depend on a specific collation, so you should avoid conversions whenever possible. Moreover, passing a string value can prevent SQL Server from using indexes on date columns because it would have to convert the string to the underlying column type.

It's far easier and safer to pass dates as date-typed variables or parameters. You avoid the entire conversion mess this way, to and from and avoid SQL injection attacks.

There's no reason to pass strings instead of dates to the server. All SQL Server clients (including ADO.NET) allow you to execute parameterized queries. ORMs like NHibernate and Entity Framework also generate parameterized queries for date-typed columns.

Whenever you need to create a string literal, use one of the invariant formats like 20140913 or 2012-11-07T18:26:20

You can read more about it at Writing International T-SQL Statements

SQL Server not accepting YYYY-MM-DD

It seems that your new DB instance picked up a new language after the reinstallation.

The current language setting determines the language used on all system messages, as well as the date/time formats to use.

The date format setting affects the interpretation of character strings as they are converted
to date values for storage in the database. It does not affect the display of date data type values
that are stored in the database or the storage format.

You can run the following statement to return the language currently being used:

SELECT @@LANGUAGE;

This will tell us what the current language is and the date format (as well as a few other things):

DBCC USEROPTIONS;

Date format is modifiable via the following statements:

SET LANGUAGE us_english;
SET DATEFORMAT YMD;

Here is a good article on the subject: How to Change the Current Date Format in SQL Server (T-SQL)

It is also possible to modify SQL Server instance default language globally, once and for all: How to change default language for SQL Server?

DateTime format to SQL format using C#

try this below

DateTime myDateTime = DateTime.Now;
string sqlFormattedDate = myDateTime.ToString("yyyy-MM-dd HH:mm:ss.fff");

How to convert datetime format but keep the dates the same in the table in SQL?

If your data is stored like this, then you should fix the data. I would suggest:

update t 
    set date = convert(date, [date], 20);

alter table t alter column [date] date;

This will change the column to a bona fide date. Then you don't have to worry about arcane formats.

---

Related Topics