Как задать формат даты в sql
Перейти к содержимому

Как задать формат даты в sql

  • автор:

SET DATEFORMAT (Transact-SQL)

Задает порядок элементов даты (месяц, день, год) при интерпретации символьных строк дат. Эти строки имеют тип date, smalldatetime, datetime, datetime2 или datetimeoffset.

Обзор всех типов данных и функций даты и времени в языке Transact-SQL см. в статье Типы данных и функции даты и времени (Transact-SQL).

Синтаксис

SET DATEFORMAT

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

format | @format_var
Порядок следования составляющих частей даты. Допустимые параметры: mdy, dmy, ymd, ydm, myd и dym. Может быть задано в формате Юникод или в виде двухбайтовой кодировки (DBCS), преобразованной в Юникод. По умолчанию для английского языка США используется mdy. Значения параметра DATEFORMAT по умолчанию для всех поддерживаемых языков см. в разделе sp_helplanguage (Transact-SQL).

Замечания

Значение ydm параметра DATEFORMAT не поддерживается для типов данных date, datetime2 и datetimeoffset.

Параметр DATEFORMAT может интерпретировать символьные строки по-разному для разных типов данных даты в зависимости от их формата строк. Например, интерпретации datetime и smalldatetime могут не соответствовать date, datetime2 или datetimeoffset. Этот параметр влияет на интерпретацию символьных строк при их преобразовании в значения даты для базы данных. Он не влияет на отображение значений типов данных даты, хранящихся в базе данных, а также на формат их хранения.

Некоторые форматы символьных строк, например ISO 8601, интерпретируются независимо от параметра DATEFORMAT.

Установка SET DATEFORMAT происходит во время выполнения, а не во время синтаксического анализа.

SET DATEFORMAT имеет преимущество над неявной установкой формата даты SET LANGUAGE.

Разрешения

Необходимо быть членом роли public.

Примеры

В следующем примере в качестве входных параметров сеансов используются различные строковые значения дат с одинаковым значением параметра DATEFORMAT .

-- Set date format to day/month/year. SET DATEFORMAT dmy; GO DECLARE @datevar DATETIME2 = '31/12/2008 09:01:01.1234567'; SELECT @datevar; GO -- Result: 2008-12-31 09:01:01.123 SET DATEFORMAT dmy; GO DECLARE @datevar DATETIME2 = '12/31/2008 09:01:01.1234567'; SELECT @datevar; GO -- Result: Msg 241: Conversion failed when converting date and/or time -- from character string. GO 

Как задать формат даты в sql

In this article, you will learn how to format date and time values using SQL Statements. There are more than 50 SQL Date Formats that can be used in different versions of SQL Server, MySQL, and other database management systems. Apart from SQL Date Formats, Wikipedia provides a lengthy list of SQL date formats used in different parts of the world. For example in the United States, a unique date format ‘mm-dd-yyyy’ is being used. Some nations, such as Korea, Iran, and China, write the year first and the day last (yyyy-mm-dd). (MIT)

This different Date interpretation varies between different countries. Suppose you have stored the ’01-05-1979′ date in your SQL database. SQL Server and MySQL have their specific Date formats. On retrieval of this date can communicate different meanings to different peoples across the globe.

Hence for a database programmer and a user, SQL Date Format is very much necessary to learn. In this article, we will try to enlist and outline the details of all SQL Date Formats used in SQL Server and MySQL with east and practical examples.

As you have already learned, various SQL Date Functions and SQL Time Functions articles about the different functions like FORMAT() and CONVERT() can be used to format Date in a specific way.

Types of MS SQL Server Date Formats

MS SQL Server provides Six different data types for manipulating Date and Time values. Similarly, you can SQL Server Convert Date Formats.

Data Types SQL Date Format Example
Time hh:mm:ss[.nnnnnnn] 12:36:30.1231231
Date YYYY-MM-DD 1979-05-01
SmallDateTime YYYY-MM-DD hh:mm:ss 1979-05-01 12:36:30
DateTime YYYY-MM-DD hh:mm:ss[.nnn] 1979-05-01 12:36:30.123
DateTime2 YYYY-MM-DD hh:mm:ss[.nnnnnnn] 1979-05-01 12:36:30.1231231
DateTimeOffset YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm 1979-05-01 12:36:30.1231231 + 05:30

Let run this SELECT statement on MS SQL SEVER to verify the above SQL Date formats in SQL SERVER. In this query, we have used GETDATE() and SYSDATETIME() SQL Functions to get the current DateTime value. The SQL Date and Time Functions GETUTCDATE() and SYSUTCDATETIME() functions are used

SAMPLE OUTPUT

SQL Date Formats in MS SQL SERVER - Convert and Format

SQL Date Formats in SQL SERVER — Sample output 1

Use of FORMAT() Function in SQL Date Formats

Let’s start explaining the use of FORMAT() Microsoft SQL Server Function for formatting the dates and times. Before MS SQL SERVER 2008 version, the CONVERT() Function was to perform all SQL Date Formats and to convert from one Date Format to other. We will also explain the use of CAST() and CONVERT() for SQL Date convert and format options in this article. After learning the syntax, you will be demonstrated a list of the available SQL examples on how to use the CONVERT() SQL Server function to handle different SQL SERVER Date formats in a database.

Syntax of FORMAT() Function

In, MS SQL SERVER 2012, the FORMAT() function is introduced for handling the conversion of SQL Date Formats. You do not need to remember the default SQL DateTime Format Numbers to convert a date from one specific format to another in the FORMAT() function.

The syntax of the SQL Server FORMAT function is the following:

  • value: must be an expression or value that belongs to any supported DateTime data type.
  • format:can be a nvarchar format pattern containing a valid format string. It can be a customer pattern supported characters for dates and numeric values.
  • culture: An optional nvarchar argument specifying a culture. If the culture argument is not provided, the language of the current session is used. This language is set either implicitly, or explicitly by using the SET LANGUAGE statement. culture accepts any culture supported by the .NET Framework as an argument; it is not limited to the languages explicitly supported by SQL Server. If the culture argument is not valid, FORMAT raises an error. For getting the list of culture, you can visit Wikipedia for ISO 639-1, ISO 3166-1 alpha-2.
Different types of supported date formats

Here is the table which shows the list of supported dates characters with their range and expected output values:

Format Range/Output
dd 01-31
MM 01-12
yy 00-99
HH 00-23
mm 00-59
ss 00-59
tt AM or PM
dddd Day Name e.g. Monday
MMM Abbreviated Month Name e.g. JAN, FEB
MMMM Full Month Name
yyyy four digit year
d 1-31
ud US Culture
Example-1 | SQL Date Format with the FORMAT() Function

Now is the perfect time to work with SQL Date formats examples with the FORMAT() functions. In all the below examples, We will use Use the FORMAT() SQL Function to mentioning Different SQL Date Formats. We will use the GETDATE() function to get the DateTime value and will use CUSTOM Format String. In this SELECT Statement, we will use only two arguments (value, and format_string).

In this SELECT Query to convert and format the DECLARE @datetimevalue ‘ 01/05/1979 ‘, we have used a Custom Format string. You can see that no SQL Date Format number is being used as it was being used in the CONVERT() SQL DATE Format Function. Six different format_strings have been mentioned in every FORMAT() function with same the @datetimevalue = ’01/05/1979′ .

SAMPLE OUTPUT

SQL SERVER DATE FORMATS with FORMAT() Function

SQL SERVER DATE FORMATS with FORMAT() Function — Sample Output 2

SQL Server Date FORMAT with Culture

The Culture is an optional argument that can be used for SQL Date convert and format. With the culture argument, we can get SQL Date Formats for different regions. As Format() function is very rich in syntax and arguments and can be used with all data types supported by .NET and SQL Server. Hence we have a lengthy list of culture codes to use with FORMAT SQL Date Format and Convert function.

Example-2 | Use of Culture with SQL Date Format()

Here is another example of the SELECT Statement that will use the culture argument in the FORMAT() function. You will see how simple it will be to get the required SQL Date Format for a specific region. For example, you want to convert the DateTime value into US English, Norwegian, Zulu, and Indian-Hindi format. We will use only their culture code from the above-mentioned list as under

SAMPLE OUTPUT

SQL SERVER DATE FORMATS with FORMAT() and Culture argument

Use of FORMAT() Function with culture — Sample output 3

CAST and CONVERT() Date Format Function

Microsoft SQL Server 2008 and earlier versions used this CONVERT() SQL function to format dates in SQL queries and other SQL procedures. These functions convert an expression of one data type to another. Hence these SQL functions can also be used to Format SQL Dates. The CAST() and The CONVERT() function can convert a value (of any type) into a specified datatype.

As far as SQL Date Formatting is concerned, the FORMAT() function is a much better choice as compared to the CONVERT() SQL function. It is not very flexible and provides limited date formats and styles in contrast to the SQL FORMAT() function for date formatting.

SQL Server Date and Time styles

SQL Date Formats/styles are mentioned in the official documentation with the help of a table consisting of more than 40 different date formats. For a date or time data type expression, style can have one of the values shown in the following table. It is not be mentioned here that CAST() SQL Function can convert/format a SQL date format to any other datatype but cannot use these styles.

Syntax of CAST() / CONVERT()

In both these syntax, the expression means the data type which you need to format or convert a SQL DateTime value. You can observe in both The CAST() and CONVERT() expression and data_type(length) parameters are being indifferent sequences. The CONVERT SQL Function contains an extra parameter style which can be chosen from the above-mentioned table from the official documentation.

Here is a smaller snapshot of this table.

SQL Date format Styles in SQL Server

SQL Date format Styles in SQL Server | Figure 1

Example-3 | Use of CONVERT() for SQL Server DATE Formats

In this code you can see that we are using two styles USA and ANSI in the CONVERT() SQL Server Function with the help of Style parameter values 101 and 102 respectively. We have used the VARCHAR data type to convert the values returned by the GETDATE() SQL Function. As shown in the above Figure 1 showing the style table, USA date style code 101 for displaying year value with century and only 1 for year value without century.

SAMPLE OUTPUT

SQL Date Foramt with CONVERT() Functions - Output

SQL Date Format with CONVERT() — Sample Output 4

In the output, you can see the style values from figure 1 are correctly producing the output. In these, all examples USA style value is used in different ways with Date and Datetime target format.

Example-4 | Use of CAST() for SQL Server DATE Formats

The CAST() SQL Function is used to convert a data type into another data type. Here in this example, we will use the CAST() SQL Function to convert a DateTime value returned by GETDATE() function into varchar, DateTime, date, and time data types.

SAMPLE OUTPUT

SQL Date Format with CAST() Function - Sample output 5

SQL Date Format with CAST() Function — Sample output 5

In the sample output of this example, you can see that

  • The first column GETDATE() value is converted to varchar
  • Second column GETDATE() value is converted into DateTime
  • Third column GETDATE() value is converted in only the Date
  • Fourth column GETDATE() value is converted in only the Time
Example-5 | Use of Long SQL Date Formats with CONVERT()

In most parts of the world, a long-date format is being used. For example in Europe, the Following «DD MMM YYYY HH:MM:SS: MMM» long date format is used. In the United States, this «USA with Time AM/PM» long date format is also used. ISO uses this long-date format «YYYY-MM-DDTHH:MM: SS.mmm».

In this example, we will use see how the CONVERT() function can be used with Style code from the official SQL Date FORMAT Styles table to format SQL dates in the above-mentioned date formats.

SAMPLE OUTPUT

sql date - Date_Format() example 5

SQL Date Format | Example 5 output

Example-6 | Use of DATEADD() with CONVERT() and CAST()

In this example, we will see that DATADD() SQL Function can be used with CAST() and CONVERT() date functions to add and subtract date with new formats. As you know that, DATEADD() SQL Server function can add/subtract year, month, or day values from the given DateTime value. In this code, we will get the current date using the GETDATE() Function and then will add and subject 1 year. We will use short date format 107 from the style table.

SAMPLE OUTPUT

SQL DATE Convert Foramt with DATEADD Example 6

SQL DATE Convert Format with DATEADD | Example Output

Summary

In this article, we learned about SQL Date Formats and how to use FORMAT() , CAST() and CONVERT() SQL Functions. Different date formats have been reported by Wikipedia in different parts of the world. Hence, SQL Server and other DBMS have made it possible to covert and format the SQL Date using different built-in SQL Date Format and CONVERT Functions.

In earlier versions of Microsoft SQL SEVER, CONVERT() SQL Function was used with a pre-defined list of SQL Date Format and styles. In, MS SQL SERVER 2012 and higher, the FORMAT() function is introduced for handling the conversion and formatting of SQL Date Formats. You do not need to remember the default SQL DateTime Format Numbers to convert a date from one specific format to another in the FORMAT() function.

References

Didn’t find what you were looking for? Perform a quick search across GoLinuxCloud

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can either use the comments section or contact me form.

# Dates

# Date Arithmetic — Difference between Dates in Days, Hours, Minutes and/or Seconds

In oracle, the difference (in days and/or fractions thereof) between two DATE s can be found using subtraction:

Outputs the number of days between the two dates:

Outputs the fraction of days between two dates:

The difference in hours, minutes or seconds can be found by multiplying this number by 24 , 24*60 or 24*60*60 respectively.

The previous example can be changed to get the days, hours, minutes and seconds between two dates using:

(opens new window) to correctly handle negative differences.)

The previous example can also be solved by converting the numeric difference to an interval

# Setting the Default Date Format Model

When Oracle implicitly converts from a DATE to a string or vice-versa (or when TO_CHAR()

(opens new window) are explicitly called without a format model) the NLS_DATE_FORMAT session parameter will be used as the format model in the conversion. If the literal does not match the format model then an exception will be raised.

You can review this parameter using:

You can set this value within your current session using:

(Note: this does not change the value for any other users.)

If you rely on the NLS_DATE_FORMAT to provide the format mask in TO_DATE() or TO_CHAR() then you should not be surprised when your queries break if this value is ever changed.

# Date Arithmetic — Difference between Dates in Months or Years

The difference in months between two dates can be found using the MONTHS_BETWEEN( date1, date2 )

If the difference includes part months then it will return the fraction of the month based on there being 31 days in each month:

Due to MONTHS_BETWEEN assuming 31 days per month when there can be fewer days per month then this can result in different values for differences spanning the boundaries between months.

The difference in years can be found by dividing the month difference by 12.

# Generating Dates with No Time Component

All DATE s have a time component; however, it is customary to store dates which do not need to include time information with the hours/minutes/seconds set to zero (i.e. midnight).

Convert it from a string literal using TO_DATE()

(More information on the date format models

(opens new window) can be found in the Oracle documentation.)

(If you are converting language specific terms such as month names then it is good practice to include the 3rd nlsparam parameter to the TO_DATE() function and specify the language to be expected.)

# Generating Dates with a Time Component

Convert it from a string literal using TO_DATE()

Oracle will implicitly cast a TIMESTAMP to a DATE when storing it in a DATE column of a table; however you can explicitly CAST()

# The Format of a Date

In Oracle a DATE data type does not have a format; when Oracle sends a DATE to the client program (SQL/Plus, SQL/Developer, Toad, Java, Python, etc) it will send 7- or 8- bytes which represent the date.

A DATE which is not stored in a table (i.e. generated by SYSDATE and having «type 13» when using the DUMP() command) has 8-bytes and has the structure (the numbers on the right are the internal representation of 2012-11-26 16:41:09 ):

A DATE which is stored in a table («type 12» when using the DUMP() command) has 7-bytes and has the structure (the numbers on the right are the internal representation of 2012-11-26 16:41:09 ):

If you want the date to have a specific format then you will need to convert it to something that has a format (i.e. a string). The SQL client may implicitly do this or you can explicitly convert the value to a string

# Converting Dates to a String

(opens new window) is not provided then the NLS_DATE_FORMAT session parameter will be used as the default format model

(opens new window) ; this can be different for every session so should not be relied on. It is good practice to always specify the format model.)

Некоторые примеры нестандартных возможностей синтаксиса sql. Часть вторая: форматы дат

Данная статья посвящена форматам дат в Oracle и некоторым особенностям их обработки. В статье приведен обзор нескольких стандартных масок форматирования дат, явная и неявная конвертация строк в даты и дополнительные параметры, влияющие на этот процесс. Как и в первой части статьи, обсуждение материала происходит на основе примеров, демонстрирующих нестандартные возможности форматирования. Детально рассмотрены механизмы Oracle, участвующие в процессе неявного преобразования. Описание большинства возможностей сопровождается ссылками на соответствующие разделы документации.

Изначально я не планировал написание статьи о датах, а собирался остановиться на рассмотрении всего одного вопроса данной тематики. Однако в ходе работы возникла необходимость в освещении различных дополнительных возможностей Oracle, стали появляться новые примеры. Так рассмотрение одного вопроса разрослось в небольшую статью. Надеюсь, получилось нескучно, несмотря на не самую интересную тематику.

Первая часть статьи, посвященная особенностям оператора order by, функционированию not in и примеру неявного преобразования типов, находится здесь.

Функция to_date и форматы даты

Мало кто из программистов любит тематику форматирования. Например, на некоторых курсах темы форматирования дат и региональных стандартов специально ставят на последние часы последнего дня тренинга, т.к. слушателям нудно. Причина в большом количестве существующих форматов при относительно редком их использовании в стандартных задачах. Чаще всего маски используются в трех функциях: to_number, to_char и to_date. Во всех трех случаях маска идет вторым необязательным параметром. И если масок для форматирования чисел еще более-менее вменяемое количество, то масок для форматирования дат до неприличия много, плюс еще суффиксы и модификаторы.

Безусловно, доступность большого количества масок является позитивным моментом, поскольку расширяет возможности, например, проверить является ли 13 сентября 2011 днем программиста, можно с помощью маски ‘DDD’, которая возвращает номер дня в году:

Несмотря на явную пользу форматирования, я не планировал включать во вторую часть статьи обзор форматов дат и примеры использования экзотических масок. Во-первых, вряд ли это будет кому-то интересно, во-вторых, автор также не является большим почитателем сложного форматирования, поскольку редко его применяет в жизни. Единственная причина появления данного раздела – некоторые вопросы, возникшие у читателей по поводу использования формата RR.

Перед тем как перейти непосредственно к основной теме раздела, давайте рассмотрим несколько нестандартных примеров работы с датами.

Пример №1. Использование урезанных шаблонов
Начнем со стандартного форматирования. Пускай сегодняшняя дата 16.09.2011, выполнятся ли следующие запросы, и что они вернут?

Запрос №2 является типичным примером конвертации даты в строку с приведением ее к нужному формату. Единственное отличие – вместо более привычных масок вида ‘DD.MM.YY’ или ‘DD-MON-YYYY’ мы использовали маску, задающую только год. Запрос №2 выполнится успешно и вернет текущий год в четырехзначном формате, т.е. ‘2011’.

Запрос №3 немного интереснее, он представляет собой типичный пример явного преобразования строки в дату с урезанной маской формата, поэтому, с точки зрения синтаксиса, запрос верный и выполнится успешно. Более важным вопросом является результат его выполнения, т.е. какую дату он вернет, если задан только день? Перед ответом на данный вопрос давайте вспомним, как Oracle устанавливает время, если оно явно не задано:

В запросе №4 время не указано, в запросе №5 указано только количество минут, часы и секунды опущены. В Oracle существует правило, согласно которому, если в дате отсутствует временной компонент, то время автоматически устанавливается в значение 00:00:00 (т.е. полночь), если задана только часть элементов времени (как в запросе №5), то пропущенные элементы устанавливаются в 00. Поэтому, запрос №4 вернет строку ‘03.02.2011 00:00:00’, а запрос №5 — ‘03.02.2011 00:30:00’.

Вернемся к запросу №3, верно ли данное правило для дат, т.е. заменяются ли пропущенные при конвертации элементы даты на 00 или 01? Да заменяются, но не все, точнее, для пропущенных элементов даты используются значения из sysdate (первый день текущего месяца текущего года). Поэтому запрос №3 будет использовать 09 в качестве месяца и 2011 в качестве года, таким образом, результатом выполнения запроса будет дата 03.09.2011.

Пример №2. Порядок параметров форматирования
Выполнится ли следующий запрос, и если да, то какую дату он вернет?

На первый взгляд, отсутствие разделителей в строке с датой может показаться критическим фактором несовместимым с выполнением запроса, однако маска даты также задана без разделителей и строка для преобразования соответствует указанному шаблону. Поэтому запрос №6 выполнится успешно и вернет 20.11.2009 (формат результата может несколько отличаться в зависимости от настроек сессии). Детальнее вопросы, связанные с разделителями, мы рассмотрим в следующем примере.

Пример №3. Неявная конвертация
Пусть формат даты по умолчанию DD.MON.RR, а язык даты – русский, отработает ли следующий запрос:

В данном запросе указано два строковых параметра, которые должны быть преобразованы в даты с помощью неявной конвертации. Согласно документации, при использовании форматов по умолчанию, строка для неявного преобразования в дату должна удовлетворять шаблону: [D|DD] separator1 [MM|MON|MONTH] separator2 [R|RR|YY|YYYY]. В качестве separator1 и separator2 можно использовать большинство разделительных знаков и специальных символов, в том числе пробелы, табуляцию, а также » и удвоенную одинарную кавычку ». Более того, если в строке указано не менее двух цифр для задания дней, месяцев и лет, то separator вообще может быть опущен. Например:

Поскольку обе строки указанные в запросе №7 соответствуют приведенному шаблону, то запрос выполнится успешно и вернет число 11.

Пример №4. Параметры функции to_date
Пусть формат даты по умолчанию DD.MON.RR, а язык даты – русский, отработает ли следующий запрос:

Схожий запрос фигурировал в одном из обсуждений на странице ask Tom. Ловушка запроса в том, что мы пытаемся преобразовать дату (sysdate) в дату. Если бы запрос выглядел так:

То выполнение прошло бы успешно, и он вернул строку ’09/15/2011 23:00:11′. Однако функция to_date в качестве первого параметра ожидает строку поэтому, вначале происходит неявная конвертация даты в строку (что эквивалентно вызову to_char(sysdate) с маской по умолчанию). Результатом данной конвертации является строка ‘15.09.11’, далее происходит вызов to_date. Таким образом, запрос №11 эквивалентен следующему запросу:

Как не сложно убедиться, запрос №13 не может быть выполнен, поскольку строка ‘15.09.11’ не соответствует установленной маске, соответственно, запрос №11 так же не может быть выполнен.

Установка формата даты по умолчанию
Формат дат по умолчанию задается двумя параметрами: NLS_DATE_FORMAT (отвечает за сам формат как таковой) и NLS_DATE_LANGUAGE (отвечает за язык, который будет использован при написании названий дней, месяцев и т.д.). Если эти параметры не заданы явно, то их значения устанавливаются на основе параметра NLS_LANG.

  1. Уровень БД: Параметры этого уровня устанавливаются при создании БД и прописываются в файле init.ora.
  2. Уровень экземпляра: Параметры этого уровня устанавливаются при старте экземпляра и могут быть изменены с помощью команды ALTER SYSTEM.
  3. Уровень сессии: Параметры этого уровня могут быть изменены командой ALTER SESSION. Также значение данных параметров можно проверить с помощью запроса:

Логично предположить, что преобразование строки ‘11.09.11’ в дату пройдет успешно, а строки ’11.SEP.11′ – нет. Однако это не так, успешно выполнятся оба преобразования. Вначале я предполагал, что в случае невозможности преобразовать строку по маске сессии Oracle пытается задействовать маски других уровней (маска уровня БД у меня установлена в ‘DD-MON-RR’). Чтение документации показало, что это не так, и Oracle руководствуется принципами, описанными в предыдущем пункте.

Попробуем другой пример:

Если вы думаете, что результат будет идентичен предыдущему запросу, то вы ошибаетесь. Одно из преобразований не выполнится. В данном случае строка ‘11.09.11’ не удовлетворяет шаблону. Возможно, это мистика?

Увы, нет. Чтение документации показало, что существуют правила автозамены элементов форматирования даты. Ниже привожу таблицу замен.

Original Format Element Additional Format Elements to Try in Place of the Original
‘MM’ ‘MON’ and ‘MONTH’
‘MON ‘MONTH’
‘MONTH’ ‘MON’
‘YY’ ‘YYYY’
‘RR’ ‘RRRR’

Глядя на содержимое этой таблицы, становится понятно, что в формате ‘DD.MM.RR’ неявным образом присутствует формат ‘DD.MON.RR’ (а также ‘DD.MONTH.RR’ и другие), а вот в формате ‘DD.MON.RR’ формат ‘DD.MM.RR’ не присутствует, что и объясняет поведение запросов №14 и №15.

Пример №6. Формат RR vs YY
Большинству пользователей отличия масок RR и YY хорошо известны, однако есть и те, кому данная информация окажется полезной. Перейдем сразу к рассмотрению примера. Какие данные вернут следующие запросы:

Оба приведенных выше запроса выполнятся успешно и вернут даты в соответствии с правилами, описанными в примере №1 для запроса №3. Таким образом, значение дня во всех полученных датах будет равно 01, а значение месяца 09 (если вы выполняете запрос в сентябре). Главный вопрос, каким будет значение года?

Как несложно предположить, в запросе №16 под ’11’ я подразумевал 2011 год и обе маски мне его вернули, т.е. результат выполнения запроса №16 это 01.09.2011 и 01.09.2011.

В запросе №17 под ’99’ я подразумевал 1999 год и тут мнения масок разделились: маска RR вернула ожидаемый 1999 год, а маска YY – 2099, т.е. результат выполнения запроса №17 это 01.09.1999 и 01.09.2099.

Рассмотрим, как работают эти элементы форматирования более детально:

  • If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.
  • If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.
  • If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.
  • If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.

Поэтому запрос №19 вернет 1950 год в обоих случаях.

Пример №7. Некоторые другие примеры
В завершение обзора рассмотрим немного экзотики. Будет ли ошибка в результате выполнения следующего запроса:

Если вы решили, что это бессмысленная запись, то вы ошибаетесь – это вполне корректное задание даты в соответствии со стандартом ANSI, запрос №20 выполнится успешно и вернет 25.12.1928.

Какой из запросов не выполнится?

Данный пример призван продемонстрировать наличие третьего параметра в функции to_date. Данный параметр позволяет установить значение одного из NLS (National Language Support) параметров только для этого вызова функции to_date. Установку NLS параметров для сессии мы рассматривали в примере №5. Ключевая разница запросов №20 и №21 состоит не в названии месяца (маска MON автоматически замещается маской MONTH, как это описано в примере №5), а в указании разных языков даты. Запрос №21 ожидает название месяца на английском и, соответственно, не выполнится, запрос №22 ожидает название месяца на русском и выполнится успешно.

В каком случае следующее объявление таблицы может приводить к возникновению ошибок при вставке данных?

Каждый раз, когда сессия с форматом даты по умолчанию ‘DD.MON.RR’ будет производить вставку с указанием только значения первого столбца будет возникать ошибка.

Особенности отображения дат в различных приложениях

Что влияет на отображение даты
Этот раздел добавлен после публикации статьи благодаря рекомендациям, указанным в комментариях. Описанное далее верно как для отображения дат, так и для отображения чисел. Возможно, при выполнении некоторых приведенных выше в статье примеров, вы получили даты в отличном от указанного в результатах формате. Если настройки вашей сессии соответствовали указанным в примерах, то это представляется, по меньшей мере, странным.

Правда заключается в том, что при выполнении запроса
вы получаете дату, но для отображения результата на экран конкретная утилита, с помощью которой вы обращаетесь к БД, должна провести конвертацию даты в строку. Таким образом, для отображения дат (и чисел) неявным образом вызывается to_char, т.е. имеем классический случай неявной конвертации (это конвертация только для вывода на экран, ее результаты не участвуют ни в каких вычислениях и ни на что кроме отображения данных не влияют). Если есть неявная конвертация, значит, существует и маска, по которой она выполняется. В классическом случае это должна быть маска, установленная для сессии, т.е. маска, указанная в параметре NLS_DATE_FORMAT таблицы nls_session_parameters, с которой мы активно работали на протяжении всей статьи.

Давайте проверим работу некоторых приложений. Проверять будем с помощью следующего скрипта:

image

Проверим, какие параметры для отображения дат использует sqlplus.

Рис. 1. Результат выполнения запроса №25 в sqlplus.

Как видно из рис.1, формат отображения даты меняется в зависимости от настроек сессии, т.е. sqlplus использует настройки сессии. Это упрощает понимание процесса преобразования дат в строки и обратно, поскольку и для преобразования и для отображения используются одинаковые маски.

image

Некоторые продвинутые средства разработки используют свои собственные NLS настройки, не имеющие отношения к настройкам Оракл. В качестве примера проверим, какие параметры для отображения дат использует PL/SQL Developer. Для этого выполним в нем запрос №25.

Рис. 2. Результат выполнения запроса №25 в PL/SQL Developer.

image

Как видно из рис.2, формат отображения даты не меняется при изменении настроек сессии. Более того, если посмотреть внимательно, то видно, что и первый и второй результаты вывода даты на экран не соответствовали параметрам сессии (в первом случае выведенная дата имела год в четырехзначном формате, а маска указывала год в двухзначном формате). Это означает, что утилита использует собственные NLS настройки, в случае PL/SQL Developer’а их расположение указано на рис. 3.

Рис. 3. Установка NLS параметров отображения дат в PL/SQL Developer.

Чем могут быть вредны NLS настройки утилит
Отображение даты в формате отличном от формата сессии вредно по одной причине – оно вводит пользователя в заблуждение и может привести к возникновению ошибок. Выполним в sqlplus и PL/SQL Developer следующий запрос:
В последнюю строку запроса вместо ХХХХХХХХ мы будем вставлять полученные из предыдущей строки данные.

image

Результаты выполнения запроса представлены на рисунках ниже.

Рис. 4. Результат выполнения запроса №26 в sqlplus.

Рис. 5. Результат выполнения запроса №26 в PL/SQL Developer.

Почему в sqlplus выведенные на экран данные были успешно конвертированы в дату, а данные выведенные на экран PL/SQL Developer’ом не смогли сконвертироваться? Потому что для конвертации Оракл использует формат данных указанный в сессии, а данные выведенные PL/SQL Developer’ом были приведены для отображения в свой формат, отличный от формата сессии.

Заключение

В качестве заключения хочу напомнить, что почти в каждом своем посте посвященном работе с датами, Том Кайт говорит о необходимости использования явных преобразований и обязательном указании маски. «При конвертации строки в дату никогда не полагайтесь на формат даты по умолчанию, всегда явно задавайте маску» — примерно так звучат его слова. Дополнительные примеры и возможные ошибки при работе с преобразованием дат вы можете найти, воспользовавшись страничкой ask Tom.

Поскольку работа с датами заняла всю статью, то «за бортом» осталось множество интересных вопросов, которые я хотел бы рассмотреть. Скорее всего, появится и третья часть статьи, как только у меня найдется свободное время.

Time and Date Format in SQL

An SQL database stores a variety of data types, such as numbers, text strings, boolean values, dates, etc. However, storing and handling such data have their own specifications. The most difficult part when working with dates is to be sure that the format of the date you are trying to insert, matches the date format of the date column in the database.

SQL Date and Time Data Types

MySQL comes with the following data types for storing a date or a date/time value in the database:

DATE — YYYY-MM-DD
DATETIME — YYYY-MM-DD HH:MI
TIMESTAMP — YYYY-MM-DD HH:MI
YEAR — YYYY or YY

Convert DateTime to Date Format YYYY-MM-DD

While working with database tables, we need only the date part from the DateTime column. Let us first create an Employee Table in MySQL.

Output

Now, as we can see, the DOB column has DateTime format, we can convert this DateTime to YYYY-MM-DD which is a date datatype in SQL using DATE_FORMAT .

DATE_FORMAT(date, format) — Formats the date value according to the format string. The % character is required before format specifier characters.

Now, let’s query both the original DOB column values and DOB_new values to demonstrate the difference between them:

Output

Convert String to Date Format YYYY-MM-DD

STR_TO_DATE(str,format) — This is the inverse of the DATE_FORMAT() function. This STR_TO_DATE() function takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts. If the date, time, or datetime value extracted from str is illegal, STR_TO_DATE() returns NULL and produces a warning.

Output

The server scans the str parameter in an attempt to match the format. The format string can contain literal characters and format specifiers beginning with % . The Literal character string is a sequence of zero or more characters enclosed by single quotes, such that the format must match literally in str . Format specifiers in the format must match a date or time part in str .

Convert Date Format YYYY-MM-DD into Other Formats

Similar to how we can convert other DateTime formats to YYYY-MM-DD , we can do the reverse. To convert the YYYY-MM-DD date format to different date formats, you can use the CONVERT function in MySQL.

The CONVERT() function converts a value into the specified datatype or character set.

Syntax:

Parameter Values:

  • value: the required value to convert
  • type: the required datatype to convert to which can be DATE, TIME, DATETIME, etc.

Output

In the above snippet, the first query converts the date format in SQL from YYYY-MM-DD to DATETIME format which gives YYYY-MM-DD HH:MI:SS as the output. While the second SQL query converts the YYYY-MM-DD HH:MI:SS to TIME format and gives HH:MI:SS as the output.

Date and Time Functions

In the following section, we will learn about some of the most important built-in date functions in SQL.

1. ADDDATE()

The ADDDATE() function adds a time/date interval to a date and then returns the date datatype in SQL.

Syntax

Parameter Values —

  • date: the date to be modified
  • days: the number of days to add to date
  • value: the value of the time/date interval to add. Both positive and negative values are allowed.
  • addunit: the type of interval to add.

Output

The last query in the above snippet is a bit different than the other two as ADDDATE(expression, days) is a shorthand way of modifying the date by just specifying the number of days as a parameter in the ADDDATE() function, this special form differentiates ADDDATE() from DATE_ADD() . In fact, when using this syntax, the ADDDATE() function is a synonym for DATE_ADD() .

2. ADDTIME()

The ADDTIME() function in SQL adds a time interval to a time/datetime and then returns the time/datetime.

Syntax

Parameter Values

  • datetime: the time/datetime to be modified
  • addtime: the time interval to add to datetime. Both positive and negative values are allowed
3. DATE_ADD()

The DATE_ADD() function adds a time/date interval to a date and then returns the date datatype in SQL.

Syntax

Parameter Values

  • date: the date to be modified
  • value: the value of the time/date interval to add. Both positive and negative values are allowed.
  • addunit: the type of interval to add.

Output

The first two queries are exactly the same as the ADDDATE() syntax. In fact, when using this syntax, the ADDDATE() function in SQL is a synonym for DATE_ADD() . The last query in the above snippet shows an error because DATE_ADD() function doesn’t support the shorthand syntax.

4. FROM_DAYS()

The FROM_DAYS() function returns a date datatype in SQL from a numeric datevalue. The FROM_DAYS() function is to be used only with dates within the Gregorian calendar.

The Gregorian calendar was introduced in October 1582 as a modification of, and replacement for, the Julian calendar. The Julian calendar day Thursday, 4 October 1582 was followed by the first day of the Gregorian calendar, Friday, 15 October 1582 (the cycle of weekdays was not affected).

The main difference between Julian and Gregorian calendars is that an average year in Julian calendar is 365.25 days while an average year in Gregorian calendar is 365.2425 days.

Syntax

Parameter Values

  • number the numeric day to convert to a date

Output

5. SEC_TO_TIME()

The SEC_TO_TIME() function returns a time value (in format HH:MM:SS ) based on the specified seconds.

Syntax

Parameter Values

  • seconds the number of seconds. Both positive and negative values are allowed

Output

6. CURRENT_TIMEZONE()

This function returns the name of the time zone observed by a server. For SQL, the return value is based on the time zone of the instance itself assigned during instance creation, not the time zone of the underlying operating system.

Syntax

Argument This function takes no arguments.

Return Value CURRENT_TIMEZONE is a non-deterministic function. Views and expressions that reference this column cannot be indexed.

Похожие публикации:

  1. 7 какие существуют средства группирования в sql как они используются
  2. Ddl команды sql что это
  3. Invalid identifier sql что это
  4. Microsoft sql server native client что это

TO_DATE ФУНКЦИЯ

Oracle/PLSQL функция TO_DATE преобразует строку в дату.

Синтаксис

Синтаксис функции Oracle/PLSQL TO_DATE:

TO_DATE( string1, [ format_mask ], [ nls_language ] )

Параметры или аргументы

string1 это строка, которая будет преобразована в дату.

format_mask не является обязательным. Это формат, который будет использоваться для преобразования string1 к дате.
Это может быть одно или сочетание следующих значений:

Параметр Пояснение
YYYY 4-х значный год
YYY
YY
Y
Последние 3, 2 или 1 цифры(а) года.
IYY
IY
I
Последние 3, 2 или 1 цифры(а) года ISO.
IYYY 4-значный год в соответствии со стандартом ISO
RRRR Принимает год с 2 цифрами и возвращает год с 4 цифрами.
Значение между 0-49 вернет 20xx год.
Значение между 50-99 вернет 19xx год.
Q Квартал года (1, 2, 3, 4; JAN-MAR = 1).
MM Месяц (01-12; JAN = 01).
MON Сокращенное название месяца.
MONTH Название месяца, дополненное пробелами длиной до 9 символов.
RM Римская цифра RM (I-XII; JAN = I).
WW Неделя года (1-53), где неделя 1 начинается в первый день года и продолжается до седьмого дня года.
W Неделя месяца (1-5), где 1-я неделя начинается в первый день месяца и заканчивается седьмым.
IW Неделя года (1-52 или 1-53) на основе стандарта ISO.
D День недели (1-7).
DAY Название дня.
DD День месяца (1-31).
DDD День года (1-366).
DY Сокращенное название дня.
J Юлианский день; количество дней с 1 января 4712 г. до н.э.
HH Час дня (1-12).
HH12 Час дня (1-12).
HH24 Час дня (0-23).
MI Минута (0-59).
SS Секунда (0-59).
SSSSS Секунды после полуночи (0-86399).
FF Дробные секунды. Используйте значение от 1 до 9 после FF, чтобы указать количество цифр в долях секунды. Например, ‘FF4’.
AM, A.M., PM, или P.M. Индикатор меридиана.
AD или A.D AD индикатор.
BC или B.C. BC индикатор.
TZD Летнее время информация. Например, ‘PST’
TZH Часовой пояс час.
TZM Часовой пояс минуты.
TZR Часовой пояс региона.

nls_language не является обязательным. NLS language используется для преобразования string1 к дате.

Применение

Функцию TO_DATE можно использовать в следующих версиях Oracle/PLSQL:

  • Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Пример

Рассмотрим несколько примеров функции TO_DATE, чтобы понять, как использовать функцию TO_DATE в Oracle/PLSQL.

Работа с форматом даты в SQL

SQL формат даты является важным аспектом при работе с данными даты и времени в базах данных. Формат даты определяет, как даты будут храниться и отображаться в таблицах, а также как они будут использоваться при выполнении различных операций и запросов. В стандарте SQL определены несколько типов данных для работы с датами и временем, такие как DATE , TIME , DATETIME , TIMESTAMP и другие. При создании таблицы, необходимо выбрать подходящий тип данных для хранения даты и определить его формат.

Пример создания таблицы с полем даты типа DATE:

 CREATE TABLE myTable ( id INT PRIMARY KEY, date_field DATE ); 
  1. Формат «ГГГГ-ММ-ДД»:
 INSERT INTO myTable (id, date_field) VALUES (1, '2022-01-01'); 
 INSERT INTO myTable (id, date_field) VALUES (2, '01/01/2022'); 
 INSERT INTO myTable (id, date_field) VALUES (3, '01.01.2022'); 
 INSERT INTO myTable (id, date_field) VALUES (4, '22-01-01'); 
 INSERT INTO myTable (id, date_field) VALUES (5, '2022/01/01'); 

При выборке данных, можно использовать различные функции форматирования даты для получения нужного вида представления. Например:

 SELECT id, DATE_FORMAT(date_field, '%d.%m.%Y') AS formatted_date FROM myTable; 

Результат выполнения данного запроса будет содержать отформатированную дату в формате «ДД.ММ.ГГГГ».

Также, можно использовать функции преобразования даты, чтобы выполнить операции с датами. Например:

 SELECT id, date_field, ADDDATE(date_field, INTERVAL 7 DAY) AS new_date FROM myTable; 

В этом примере мы добавляем 7 дней к полю date_field для каждой строки и выводим новую дату.

В зависимости от конкретных требований и поддерживаемых функций СУБД, можно использовать и другие функции форматирования даты и времени. Важно также учитывать особенности используемой СУБД и ее поддержку стандарта SQL.

Описанные примеры являются лишь некоторыми из множества возможностей работы с форматом даты в SQL. При разработке приложений и работы с базами данных, рекомендуется изучить документацию конкретной СУБД и ее функции работы с датами и временем.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *