Transact-SQL 関数。 SQL - 日付関数 日付を扱うための T SQL 関数
日付を扱うSQL– これは非常に重要であるため、価値のあるプロジェクトでは基本的な SQL 演算子の知識なしでは作業を行うことができません。 何と言っても、すべてのサービスでは時間を考慮して作業する必要があります。 原則として、これはある日付から別の日付までの期間の計算であり、たとえば、年、月、日、時間の登録ユーザーのリストを表示します。私自身が毎日遭遇する、SQL での日付の操作に関連する一般的なタスクに対する解決策をいくつか紹介したいと思います。これが皆さんにとって適切で役立つことを願っています。
SQLで現在の日付を取得する方法
WHERE 日付 = CURDATE()
または別のオプション
WHERE 日付 = STR_TO_DATE(now(), "%Y-%m-%d")
SQL で日付に 1 時間を追加します
DATE_ADD("2013-03-30"、間隔 1 時間)
SQL で日付に 1 日を加算する
DATE_ADD("2013-03-30"、間隔 1 日)
同様に、現在の日付に任意の日数を追加できます。
SQL で日付に 1 か月を加算します
DATE_ADD("2013-03-30"、間隔 1 か月)
同様に、現在の日付に任意の月数を追加できます。
SQLで昨日の日を取得する
DATE_ADD(CURDATE(), INTERVAL -1 DAY)
または
DATE_SUB(CURDATE()、間隔 1 日)
SQLで今週の開始日を取得する
これは一見すると最も難しいタスクの 1 つですが、非常に簡単に解決できます。
CURDATE()-WEEKDAY(CURDATE());
今週の月曜日から現在の曜日までの選択を SQL で取得します
SQLで現在の月の初日から現在の曜日までの選択を取得します
WHERE (日付 (CURDATE()-WEEKDAY(CURDATE())) と CURDATE())
SQLでユーザーの生年月日を取得する方法 SQL で来月が誕生日のユーザーをすべて検索する SQL での日付の操作に関する上記のケースに加えて、次の演算子のドキュメントを読むことをお勧めします。 SQL での日付の操作は、実際にはそれほど難しくありません。PHP を使用して期間を計算する代わりに、SQL クエリを実行する段階でこれを実行し、必要なデータ選択を取得できるようになりました。 最終更新日: 2017 年 7 月 29 日 T-SQL には、日付と時刻を操作するための関数が多数用意されています。 GETDATE: システムクロックに基づいて現在のローカル日付と時刻を日時オブジェクトとして返します。 SELECT GETDATE() -- 2017-07-28 21:34:55.830 GETUTCDATE : 現在のローカル日付と時刻をグリニッジ標準時 (UTC/GMT) で datetime オブジェクトとして返します。 SELECT GETUTCDATE() -- 2017-07-28 18:34:55.830 SYSDATETIME: システム クロックに基づいて現在のローカル日付と時刻を返しますが、日付と時刻が datetime2 オブジェクトとして返される点で GETDATE とは異なります。 SELECT SYSDATETIME() -- 2017-07-28 21:02:22.7446744 SYSUTCDATETIME : 現在のローカル日付と時刻をグリニッジ標準時 (UTC/GMT) で datetime2 オブジェクトとして返します。 SELECT SYSUTCDATETIME() -- 2017-07-28 18:20:27.5202777 SYSDATETIMEOFFSET : GMT を基準とした日付と時刻を含む datetimeoffset(7) オブジェクトを返します。 SELECT SYSDATETIMEOFFSET() -- 2017-07-28 21:02:22.7446744 +03:00 DAY: パラメータとして渡された日付の日を返します。 日の選択(GETDATE()) -- 28 MONTH : 日付の月を返します SELECT MONTH(GETDATE()) -- 7 YEAR : 日付から年を返します 年を選択(GETDATE()) -- 2017 DATENAME: 日付部分を文字列として返します。 日付部分の選択パラメーターが最初のパラメーターとして渡され、日付自体が 2 番目のパラメーターとして渡されます。 SELECT DATENAME(月, GETDATE()) -- 7 月 日付の一部を決定するには、次のパラメータを使用できます (括弧内に省略形が示されています)。 年 (yy, yyyy) : 年 四半期 (qq, q) : 四半期 月 (mm, m) : 月 dayofyear (dy, y) : 年間通算日 day (dd, d) : 月の日 週 (週、ww) : 週 平日 (dw) : 曜日 時 (hh) : 時 分 (mi, n) : 分 秒 (ss, s) : 秒 ミリ秒 (ms) : ミリ秒 マイクロ秒 (mcs) : マイクロ秒 ナノ秒 (ns) : ナノ秒 tzoffset (tz) : GMT を基準とした分単位のオフセット (datetimeoffset オブジェクトの場合) DATEPART: 日付部分を数値として返します。 日付部分の選択パラメーターが最初のパラメーターとして渡され (DATENAME と同じパラメーターが使用されます)、日付自体が 2 番目のパラメーターとして渡されます。 SELECT DATEPART(月, GETDATE()) -- 7 DATEADD: 特定の日付コンポーネントに数値を加算した結果である日付を返します。 最初のパラメータは、DATENAME 関数について上で説明した日付コンポーネントを表します。 2 番目のパラメータは追加する量です。 3 番目のパラメータは日付そのもので、以下を追加する必要があります。 SELECT DATEADD(month, 2, "2017-7-28") -- 2017-09-28 00:00:00.000 SELECT DATEADD(day, 5, "2017-7-28") -- 2017-08-02 00 :00:00.000 SELECT DATEADD(日, -5, "2017-7-28") -- 2017-07-23 00:00:00.000 追加された数量が負の数を表す場合、日付は実際には減分されます。 DATEDIFF: 2 つの日付の差を返します。 最初のパラメータは日付コンポーネントで、差を測定する単位を示します。 2 番目と 3 番目のパラメータは比較される日付です。 SELECT DATEDIFF(年, "2017-7-28", "2018-9-28") -- 1 年の差 SELECT DATEDIFF(月, "2017-7-28", "2018-9-28") -- 差14 か月 SELECT DATEDIFF(day, "2017-7-28", "2018-9-28") -- 差 427 日 TODATETIMEOFFSET : 時間オフセットを別の datetimeoffset オブジェクトに追加した結果である datetimeoffset 値を返します。 SELECT TODATETIMEOFFSET("2017-7-28 01:10:22", "+03:00") SWITCHOFFSET: datetime2 オブジェクトに時刻オフセットを追加した結果である datetimeoffset 値を返します。 SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), "+02:30") EOMONTH: パラメータとして渡された日付で使用される月の最終日の日付を返します。 SELECT EOMONTH("2017-02-05") -- 2017-02-28 SELECT EOMONTH("2017-02-05", 3) -- 2017-05-31 オプションの 2 番目のパラメーターとして、日付に追加する必要がある月数を渡すことができます。 その後、月の末日が新しい日付として計算されます。 DATEFROMPARTS : 年、月、日によって日付を作成します パーツから日付を選択(2017、7、28) -- 2017-07-28 ISDATE: 式が日付であるかどうかをテストします。 存在する場合は 1 を返し、そうでない場合は 0 を返します。 SELECT ISDATE("2017-07-28") -- 1 SELECT ISDATE("2017-28-07") -- 0 SELECT ISDATE("07-28-2017") -- 0 SELECT ISDATE("SQL") - -0 関数の使用例は、注文日を含む注文テーブルを作成することです。 CREATE TABLE 注文 (Id INT IDENTITY PRIMARY KEY、ProductId INT NOT NULL、CustomerId INT NOT NULL、CreatedAt DATE NOT NULL DEFAULT GETDATE()、ProductCount INT DEFAULT 1、Price MONEY NOT NULL); DEFAULT GETDATE() 式は、データの追加時に日付が指定されない場合、GETDATE() 関数を使用して自動的に計算されることを指定します。 別の例 - 16 日前に行われた注文を探してみましょう。 SELECT * FROM 注文 WHERE DATEDIFF(日, CreatedAt, GETDATE()) = 16 著者より:今日は SQL で日付関数がどのように機能するかについて説明します。 次の表は、使用可能なすべての重要な日付と時刻の関数のリストを示しています。 他にもさまざまな DBMS でサポートされているものがあります。 このリストは、MySQL DBMS で使用できる関数を表しています。 ADDDATE()。 日付を追加します ADDTIME()。 時間を追加します CONVERT_TZ()。 あるタイムゾーンから別のタイムゾーンに変換します CURDATE()。 現在の日付を返します CURRENT_DATE()、CURRENT_DATE。 CURDATE() の同義語 CURRENT_TIME()、CURRENT_TIME。 CURTIME() の同義語 CURRENT_TIMESTAMP()、CURRENT_TIMESTAMP。 NOW() の同義語 カータイム()。 現在時刻を返します DATE_ADD()。 2 つの日付を追加します DATE_FORMAT()。 指定された日付形式を設定します DATE_SUB()。 ある日付から別の日付を減算します 日付()。 日付または日時を表す式から日付部分を抽出します。 DATEDIFF()。 ある日付から別の日付を減算します 日()。 DAYOFMONTH() の同義語 曜日名()。 曜日を返します ダヨフモント()。 日付 (1 ~ 31) を返します。 曜日()。 引数の曜日のインデックスを返します。 DAYOFYEAR()。 その年の何日目かを返します (1 ~ 366)。 抽出する。 日付部分を取得します FROM_DAYS()。 日数を日付に変換します FROM_UNIXTIME()。 日付をUNIXタイムスタンプとしてフォーマットします。 時間()。 時間を取得します LAST_DAY。 引数の月の最後の日を返します。 LOCALTIME()、LOCALTIME。 NOW() の同義語 LOCALTIMESTAMP、LOCALTIMESTAMP()。 NOW() の同義語 作成日()。 年と日から日付を作成します 時間を作ります。 時間を作ります()。 マイクロ秒()。 引数からマイクロ秒を返します 分()。 引数から分を返します 月()。 日付から月を返す 月名()。 月の名前を返します 今()。 現在の日付と時刻を返します PERIOD_ADD()。 年月にピリオドを追加します PERIOD_DIFF()。 期間間の月数を返します 四半期()。 引数から四半期を返します SEC_TO_TIME()。 秒を「HH:MM:SS」形式に変換します 2番目()。 秒 (0 ~ 59) を返します。 STR_TO_DATE()。 文字列を日付に変換します SUBDATE()。 3 つの引数を指定して呼び出された場合、DATE_SUB() シノニム サブタイム()。 時間を減算します SYSDATE()。 関数の実行時間を返します。 TIME_FORMAT()。 時刻形式を設定します TIME_TO_SEC()。 引数を秒に変換して返します 時間()。 渡された式の時刻部分を抽出します。 TimeDiff()。 時間を減算します タイムスタンプ()。 引数を 1 つ指定すると、この関数は日付または日時の式を返します。 2 つの引数の場合 - これら 2 つの引数を追加します タイムスタンプパッド()。 日付と時刻の式に間隔を追加します。 タイムスタンプディフ()。 日時式から間隔を減算します。 TO_DAYS()。 日付引数を日数に変換して返します。 UNIX_TIMESTAMP()。 UNIX タイムスタンプを返します UTC_DATE()。 現在の UTC 日付を返します UTC_TIME()。 現在の UTC 時間を返します UTC_TIMESTAMP()。 現在の UTC 日付と時刻を返します。 週()。 週番号を返します 平日()。 曜日のインデックスを返します ウィーコフィイヤー()。 カレンダーの週番号 (1 ~ 53) を返します。 年()。 年を返します 年()。 年と週を返します 2 番目の引数を INTERVAL として指定して呼び出すと、ADDDATE() は DATE_ADD() の同義語になります。 関連関数 SUBDATE() は DATE_SUB() の同義語です。 INTERVAL ブロック引数の詳細については、「DATE_ADD()」を参照してください。 2 番目の引数を日数で指定して呼び出すと、MySQL はこれを式に追加する整数の日数として扱います。 ADDTIME() は expr2 を expr1 に加算し、結果を返します。 Expr1 は時刻式または日付時刻式であり、expr2 は時刻式です。 日付と時刻の値 dt を、from_tz で指定されたタイム ゾーンから to_tz で指定されたタイム ゾーンに変換し、結果の値を返します。 引数が無効な場合、この関数は NULL を返します。 この関数が文字列コンテキストで使用されるか数値コンテキストで使用されるかに応じて、現在の日付を「YYYY-MM-DD」または YYYYMMDD 形式の値として返します。 CURRENT_DATE と CURRENT_DATE() は CURDATE() の同義語です。 関数が文字列コンテキストで使用されるか数値コンテキストで使用されるかに応じて、現在時刻を「HH:MM:SS」または HHMMSS 形式の値として返します。 値は現在のタイムゾーンで表されます。 CURRENT_TIME と CURRENT_TIME() は CURTIME() の同義語です。 CURRENT_TIMESTAMP と CURRENT_TIMESTAMP() は NOW() の同義語です。 日付または日時の式の日付部分を取得します。 DATEDIFF() は、2 つの日付の間の日数として表現された expr1.expr2 を返します。 expr1 と expr2 は両方とも日付式または日時式です。 計算には日付部分のみが使用されます。 これらの関数は、日付の算術演算を実行します。 date は、開始日を示す DATETIME または DATE 値として表されます。 expr は、元の日付に加算または減算する間隔値を指定する式です。 expr は文字列です。 負の間隔の場合は「-」で始まる場合があります。 Unit は、式の測定単位を指定するキーワードです。 INTERVAL キーワードと単位表記では大文字と小文字が区別されません。 次の表は、各単位値に対する expr 引数の予期される形式を示しています。 QUARTER 値と WEEK 値は、バージョン 5.0.0 以降の MySQL で使用できます。 このコマンドは、指定された形式文字列に従って日付値を形式設定します。 次のポインタはフォーマット行で使用できます。 フォーマットインジケーターの前には「%」文字を付ける必要があります。 %a。 曜日の短縮名 (Sun..Sat) %b。 月の短縮名 (Jan..Dec) %と。 月の数値指定 (0 ~ 12) %D。 英語の接尾辞が付いた日付 (0、1、2、3、.) %d。 日付の数値指定 (00..31) %e。 日付の数値指定 (00..31) %f。 マイクロ秒 (000000..999999) %H。 時 (00..23) %h。 時 (01..12) %私。 時 (01..12) %私。 分の数値指定 (00..59) %J. 年間の日 (001..366) %k。 時間 (0..23) %l。 時 (1..12) %M. 月の名前 (1月..12月) %m。 月の数値指定 (00..12) %R。 午前または午後 %r。 時刻、12 時間形式 (hh:mm:ss の後に AM または PM が続く) %S。 秒 (00..59) %s。 秒 (00..59) %T。 時刻、24 時間形式 (hh:mm:ss) %U。 週 (00..53)、日曜日が週の最初の日 %u。 週 (00..53)、月曜日が週の最初の日 %V。 週 (01..53)、日曜日が週の最初の日です。 %X とともに使用 %v. 週 (01..53)。月曜日が週の最初の日です。 %x とともに使用 %W。 曜日の名前 (日曜日..土曜日) %w。 曜日 (0=日曜日..6=土曜日) %バツ。 週の最初の日が日曜日である週の年、4 桁の数字。 %V とともに使用 %バツ。 週の最初の日が月曜日である週の年、4 桁の数字。 %V とともに使用 %Y。 年、日、4桁 %y。 西暦の数値指定(2桁) %%。 文字通り%記号 %バツ。 x、すべて.x. 上記に記載されていないもの DATE_ADD() 関数に似ています。 DAY() は、DAYOFMONTH() 関数の同義語です。 指定された日付の曜日を返します。 指定された日付の日付を 0 ~ 31 の範囲で返します。 曜日のインデックスを返します (1 = 日曜日、2 = 月曜日、...、7 = 土曜日)。 これらのインデックス値は ODBC 標準に従います。 指定された日付の年間通算日を 1 ~ 366 の範囲で返します。 EXTRACT() 関数は、DATE_ADD() または DATE_SUB() と同じタイプの単位インジケーターを使用しますが、日付の算術演算を実行せず、日付の単位インジケーター部分を抽出します。 DATE 値は、日数 N を考慮して返されます。 注記。 古い日付には FROM_DAYS() を慎重に使用してください。 この関数は、グレゴリオ暦 (1582 年) の導入以前の日付値を扱うことを目的としていません。 この関数が文字列コンテキストで使用されるか数値コンテキストで使用されるかに応じて、unix_timestamp 引数の表現を「YYYY-MM-DD HH:MM:SS または YYYYMMDDHHMMSS」形式の値として返します。 値は現在のタイムゾーンで表されます。 unix_timestamp パラメータは、UNIX_TIMESTAMP() 関数によって生成される内部タイムスタンプ値です。 format が指定されている場合、結果はフォーマット文字列に従ってフォーマットされます。フォーマット文字列は、DATE_FORMAT() セクションで説明されているのと同じ方法で使用されます。 指定された時刻からの時間を返します。 返される値の範囲は 0 ~ 23 です。ただし、TIME 値の範囲は実際にははるかに広いため、HOUR は 23 を超える値を返すことができます。 日付または日時の値を受け取り、月の最終日に対応する値を返します。 引数が無効な場合は NULL を返します。 LOCALTIME と LOCALTIME() は NOW() の同義語です。 LOCALTIMESTAMP と LOCALTIMESTAMP() は NOW() の同義語です。 日付、指定された年、および日付の値を返します。 dayofyear の値は 0 より大きい必要があります。0 より大きい場合、結果は NULL になります。 引数時、分、秒から計算された時刻値を返します。 時間式または datetime(expr) 式からマイクロ秒を 0 ~ 999999 の範囲の数値として返します。 指定された時間の分を 0 ~ 59 の範囲で返します。 指定された日付の月を 0 ~ 12 の範囲で返します。 指定された日付の完全な月名を返します。 関数が文字列コンテキストで使用されるか数値コンテキストで使用されるかに応じて、現在の日付と時刻を「YYYY-MM-DD HH:MM:SS」または YYYYMMDDHHMMSS 形式の値として返します。 この値は現在のタイムゾーンで表されます。 期間 P に N か月を追加します (YYMM または YYYYMM 形式)。 YYYYMM 形式で値を返します。 期間引数 P は日付値ではないことに注意してください。 期間 P1 と P2 の間の月数を返します。 期間 P1 および P2 は、YYMM または YYYYMM の形式で指定する必要があります。 期間引数 P1 および P2 は日付値ではないことに注意してください。 指定された日付の四半期を 1 ~ 4 の範囲で返します。 0 ~ 59 の範囲の時間の秒値を返します。 関数が文字列コンテキストで使用されるか数値コンテキストで使用されるかに応じて、秒引数を「HH:MM:SS」または HHMMSS 形式で時、分、秒に変換して返します。 これは DATE_FORMAT() 関数の逆です。 文字列 str とフォーマット文字列 format を受け入れます。 STR_TO_DATE() 関数は、形式文字列に日付と時刻の両方が含まれている場合に DATETIME を返します。 それ以外の場合、文字列に日付または時刻のみが含まれている場合は、DATE または TIME を返します。 2 番目の引数を INTERVAL として指定して SUBDATE() を呼び出した場合、この関数は DATE_SUB() の同義語になります。 INTERVAL 引数の詳細については、「DATE_ADD()」を参照してください。 SUBTIME() 関数は expr1 を返します。 expr2 は expr1 と同じ形式の値として表現されます。 expr1 の値は時刻式または日付時刻式であり、expr2 の値は時刻式です。 関数が文字列コンテキストで使用されるか数値コンテキストで使用されるかに応じて、現在の日付と時刻を「YYYY-MM-DD HH:MM:SS」または YYYYMMDDHHMMSS 形式の値として返します。 expr の時間部分を抽出し、文字列として返します。 TIMEDIFF() は expr1 を返します。 expr2 は時間値として表現されます。 値 expr1 と expr2 は時刻式または日時式のいずれかですが、両方とも同じ型である必要があります。 引数を 1 つ指定すると、この関数は日付または日付時刻式 expr を日付時刻値として返します。 2 つの引数を使用すると、時刻式 expr2 を日付または日時式 expr1 に加算し、結果を日時値として返します。 この関数は、整数間隔式を日付または時刻式 datetime_expr に追加します。 間隔の単位は、unit 引数で指定され、次のいずれかの値をとります。 単位値は、上記のキーワードのいずれかを使用するか、SQL_TSI_ プレフィックスを使用して指定できます。 たとえば、DAY と SQL_TSI_DAY は有効な値です。 日付または日付時刻式 datetime_expr1 と datetime_expr2 の間の整数の差を返します。 結果の測定単位は、unit 引数によって指定されます。 ユニット引数の有効な値は、TIMESTAMPADD() 関数にリストされている値と同じです。 この関数は DATE_FORMAT() 関数と同じ方法で使用されますが、書式文字列には時間、分、秒の書式指定子のみを含めることができます。 時刻値に 23 を超える時間の部分が含まれている場合、時計形式インジケーター %H および %k は、通常の範囲である 0 ~ 23 より大きい値を与えます。他の時計形式インジケーターは、12 時間を法とする値を与えます。 秒に変換された時間引数を返します。 指定された日付の日番号 (0 年からの日数) を返します。 この関数が引数なしで呼び出された場合、Unix タイムスタンプ (「1970-01-01 00:00:00」UTC からの秒数) を正の整数として返します。 UNIX_TIMESTAMP() が日付引数を指定して呼び出された場合、「1970-01-01 00:00:00」UTC からの秒数で表された引数の値が返されます。 date には、DATE 文字列、DATETIME 文字列、TIMESTAMP、または YYMMDD または YYYYMMDD 形式の数値を指定できます。 この関数が文字列コンテキストで使用されるか数値コンテキストで使用されるかに応じて、現在の UTC 日付を「YYYY-MM-DD」または YYYYMMDD 形式の値として返します。 関数が文字列コンテキストで使用されるか数値コンテキストで使用されるかに応じて、現在の UTC 時間を「HH:MM:SS」または HHMMSS 形式の値として返します。 この関数が文字列コンテキストで使用されるか数値コンテキストで使用されるかに応じて、現在の UTC 日付と時刻を値 "YYYY-MM-DD HH:MM:SS" または YYYYMMDDHHMMSS 形式で返します。 この関数は、指定された日付の週番号を返します。 2 つの引数を持つ WEEK() 形式を使用すると、週が日曜日と月曜日のどちらで始まるか、および戻り値の範囲が 0 ~ 53 または 1 ~ 53 のどちらであるかを指定できます。mode 引数が省略された場合、 default_week_format システム変数が使用されます 指定された日付の曜日のインデックスを返します (0 = 月曜日、1 = 火曜日、6 = 日曜日)。 指定された日付 date の暦週を 1 ~ 53 の範囲の数値として返します。 WEEKOFYEAR() は、WEEK(date,3) と同等の互換性関数です。 指定された日付の年を 1000 ~ 9999 の範囲で返します。date.zero の場合は 0 を返します。 指定された日付の年と週を返します。 モード引数は、WEEK() 関数のモード引数とまったく同じように機能します。 結果の年は、年の最初と最後の週の日付引数の年とは異なる場合があります。 注記。 WEEK() は特定の年のコンテキストで週を返すため、週番号は、オプションの 0 または 1 引数に対して WEEK() が返す (0) とは異なります。 Transact-SQL 言語関数は、集計関数またはスカラー関数にすることができます。 この記事では、これらのタイプの関数について説明します。 集計関数は、列値のグループに対して計算を実行し、それらの計算の結果として常に単一の値を返します。 Transact-SQL は、いくつかの一般的な集計関数をサポートしています。 列に含まれるデータの算術平均を計算します。 計算が実行される値は数値である必要があります。 列に含まれるすべてのデータ値の最大値と最小値を決定します。 値は、数値、文字列、または一時的な値 (日付/時刻) にすることができます。 列内の値の合計を計算します。 計算が実行される値は数値である必要があります。 列内の null 以外の値の数をカウントします。 count(*) 関数は、列の計算を実行しない唯一の集計関数です。 この関数は、(個々の列に null 値が含まれているかどうかに関係なく) 行数を返します。 count 関数に似ていますが、BIGINT データ値を返す点が異なります。 SELECT ステートメントでの通常の集計関数の使用については、今後の記事で説明します。 Transact-SQL スカラー関数は、スカラー式の作成に使用されます。 (スカラー関数は単一の値または値のリストに対して計算を実行しますが、集約関数は複数の行にわたる値のグループに対して計算を実行します。) スカラー関数は次のカテゴリに分類できます。 数値関数。 日付関数。 文字列関数。 システム機能。 メタデータ関数。 これらのタイプの関数については、後続のセクションで説明します。 Transact-SQL 数値関数は、数値を変更するための数学関数です。 数値関数のリストとその簡単な説明を以下の表に示します。 数値式 n の絶対値を返します (つまり、負の値は正として返されます)。 SELECT ABS(-5.320) -- 5.320 を返します SELECT ABS(8.90) -- 8.90 を返します 値 n の逆余弦、逆正弦、逆正接を計算する逆三角関数 (ATN2 の場合は逆正接 n/m が計算されます)。 入力値 n、m および結果は FLOAT データ型です。 値 n のコサイン、サイン、タンジェント、コタンジェントを計算する三角関数。 結果のデータ型は FLOAT になります。 DEGREES 関数は、ラジアンを度、RADIANS にそれぞれ変換します (逆も同様)。 SELECT DEGREES(PI() / 4) -- 45 を返します。 SELECT COS(RADIANS(60.0)) -- 0.5 を返します。 数値をより大きな整数値に丸めます。 SELECT CEILING(-5.320) -- -5 を返します SELECT CEILING(8.90) -- 9 を返します n の値を最も近い p に丸めます。 p が正の数の場合は n の小数部分が四捨五入され、負の場合は整数部分が四捨五入されます。 オプションの引数 t を使用する場合、数値 n は四捨五入されず、切り捨てられます (つまり、切り捨てられます)。 SELECT ROUND(5.3208, 3) -- 5.3210 を返します SELECT ROUND(125.384, -1) -- 130.000 を返します SELECT ROUND(125.384, -1, 1) -- 120.000 を返します 最小の整数値に切り捨てられます。 SELECT FLOOR(5.88) -- 5 を返します e n の値を計算します。 LOG(n) - 数値 n の自然対数 (底 e) を計算します。 LOG10(n) - 数値 n の 10 進数 (底 10) 対数を計算します。 π (3.1415) の値を返します。 xy の値を計算します。 0 から 1 までの値の範囲内の FLOAT 型の任意の数を返します。 システムによって実行された最後の Transact-SQL ステートメントによって処理されたテーブル行の数を返します。 戻り値の型は BIGINT です。 n の符号を数値として返します。正の場合は +1、負の場合は -1 です。 SQRT(n) - 数値 n の平方根を計算します。SQUARE(n) - 引数 n の 2 乗を返します。 日付関数は、式の対応する日付部分または時刻部分を評価するか、時間間隔の値を返します。 Transact-SQL でサポートされている日付関数とその簡単な説明を次の表に示します。 現在のシステムの日付と時刻を返します。 SELECT GETDATE() item パラメータで指定された日付部分を整数として返します。 1 (1 月) を返します。 SELECT DATEPART(month, "01/01/2012") -- 4 (水曜日) を返します。 SELECT DATEPART(weekday, "01/02/2012") itemパラメータで指定した日付部分を文字列として返します。 1 月を返します SELECT DATENAME(month, "01/01/2012") -- 水曜日を返します SELECT DATENAME(weekday, "01/02/2012") 2 つの日付部分 dat1 と dat2 の差を計算し、 item 引数で指定された単位で整数の結果を返します。 19 を返します(日付間の 19 年間隔) SELECT DATEDIFF(year, "01/01/1990", "01/01/2010") -- 7305 を返します (日付間の 7305 日間隔) SELECT DATEDIFF(day, "01/01 /1990"、"01/01" .2010") item 引数で指定された n 番目の単位数を、指定された日付に加算します。 (n は負の値にすることもできます。) 現在の日付に 3 日を加算します。 SELECT DATEADD(day, 3, GETDATE()) 文字列関数は、通常は文字データ型である列値を操作します。 Transact-SQL でサポートされている文字列関数とその簡単な説明を次の表に示します。 指定された文字を対応する ASCII コードの整数に変換します。 SELECT ASCII("W") -- 87 SELECT UNICODE("u") -- 1102 ASCII コード (NCHAR の場合は Unicode) を適切な文字に変換します。 SELECT CHAR(87) -- "W" SELECT NCHAR(1102) -- "ゆ" 文字列 str2 内の部分文字列 str1 の出現の開始位置を返します。 文字列 str2 に部分文字列 str1 が含まれていない場合は、値 0 が返されます。 5 SELECT CHARINDEX (「モーフ」、「ポリモーフィズム」) を返します。 2 つの文字列 str1 と str2 の SOUNDEX 値の差である 0 ~ 4 の整数を返します。 SOUNDEX メソッドは、文字列のサウンドを特徴付ける数値を返します。 この方法を使用すると、似た音の文字列を識別できます。 ASCII 文字に対してのみ機能します。 2 SELECT DIFFERENCE (「スペル」、「テリング」) を返します。 LEFT の場合は length パラメーターで指定された文字列 str の最初の文字数を返し、RIGHT 関数の場合は文字列 str の最後の長さの文字を返します。 DECLARE @str nvarchar(30) = "同期"; -- "Sync" を返します SELECT LEFT(@str, 4) -- "Zation" を返します SELECT RIGHT(@str, 5) 引数で指定された文字列 str の文字数 (バイト数ではありません) を返します (末尾のスペースも含みます)。 LOWER 関数は、str1 内のすべての大文字を小文字に変換します。 文字列に含まれる小文字やその他の文字は影響を受けません。 UPPER 関数は、文字列 str 内のすべての小文字を大文字に変換します。 DECLARE @str nvarchar(30) = "同期"; -- "SYNCHRONIZATION" を返します SELECT UPPER(@str) -- "synch" を返します SELECT LOWER(@str) LTRIM 関数は文字列 str の先頭のスペースを削除し、RTRIM 関数は文字列の末尾のスペースをそれぞれ削除します。 入力文字列を有効な区切り文字で区切られた識別子に変換するために区切り文字が追加された Unicode でエンコードされた文字列を返します。 DECLARE @str nvarchar(30) = "同期"; -- "[同期]" を返します SELECT QUOTENAME(@str) 指定された expr 内でパターン p が最初に出現する開始位置を返します。パターンが見つからない場合は 0 を返します。 戻り値 4 SELECT PATINDEX("%chro%", "同期") 文字列 str1 内に出現する部分文字列 str2 をすべて部分文字列 str3 に置き換えます。 "非同期" を返します。 SELECT REPLACE("同期", "同期", "非同期") 文字列 str を i 回繰り返します。 "aBaBaBaBaB" を返します SELECT REPLICATE("aB", 5) 文字列 str を逆の順序で出力します。 「yaitsazinorkhniS」を返します。 SELECT REVERSE("同期") 2 つの文字列が類似しているかどうかを判断するために使用される 4 文字の soundex コードを返します。 ASCII 文字に対してのみ機能します。 length パラメータで指定された長さのスペースの文字列を返します。 REPLICATE(" ", length) と同様です。 指定された浮動小数点式 f を文字列に変換します。ここで、len は小数点、符号、数字、スペースを含む文字列の長さ (デフォルトは 10)、d は返される小数点以下の桁数です。 "3.14" SELECT STR (3.1415, 4, 2) を返します。 位置 a から始まる文字列 str1 から length 文字を削除し、その位置に文字列 str2 を挿入します。 本の中のメモ SELECT STUFF("Notebook", 5, 0," in a ") -- Handbook SELECT STUFF("Notebook", 1, 4, "Hand") 文字列 str から、位置 a から始まる長さ length の部分文字列を抽出します。 Transact-SQL システム関数は、データベース オブジェクトに関する広範な情報を提供します。 ほとんどのシステム関数は、各データベース オブジェクトの作成時に割り当てられる内部数値識別子 (ID) を使用します。 この識別子を使用して、システムは各データベース オブジェクトを一意に識別できます。 次の表に、最も重要なシステム機能のいくつかとその簡単な説明を示します。 式 w を指定されたデータ型 type に変換します (可能な場合)。 引数 w には、任意の有効な式を指定できます。 3 SELECT CAST (3.1258 AS INT) を返します。 式 a1、a2、... のリストから null ではない最初の式値を返します。 データベース オブジェクト (テーブルまたはビュー) obj の列列の長さを返します。 4 を返します SELECT COL_LENGTH("従業員", "ID") CAST 関数と同等ですが、引数の指定方法が異なります。 あらゆるデータ型で使用できます。 現在の日付と時刻を返します。 現在のユーザーの名前を返します。 式 z が占めるバイト数を返します。 このクエリは、各フィールドの長さを返します。 SELECT DATALENGTH(FirstName) FROM Employee データベース dbname での null 値の使用が ANSI SQL 標準に準拠している場合は 1 を返します。 expr が NULL でない場合はその値を返します。 それ以外の場合は値が返されます。 式 expr が有効な数値型であるかどうかを判断します。 UNIQUEIDENTIFIER データ型の値を格納するように設計された 16 バイトのバイナリ文字列で構成される一意の識別番号 ID を作成します。 指定されたコンピューター上で、この関数によって以前に作成された他の GUID よりも大きい GUID を作成します。 (この関数は列のデフォルト値としてのみ使用できます。) expr1 と expr2 の値が同じ場合は null を返します。 クエリはプロジェクトに対して NULL を返します -- その Number = "p1" SELECT NULLIF(Number, "p1") FROM Project データベース サーバーのプロパティに関する情報を返します。 現在のユーザーの ID を返します。 ユーザー ID ユーザー名を返します。 ユーザーが指定されていない場合は、現在のユーザー ID が返されます。 指定された ID を持つユーザー名を返します。 識別子が指定されていない場合は、現在のユーザーの名前が返されます。 基本的に、メタデータ関数は、指定されたデータベースおよびデータベース オブジェクトに関する情報を返します。 以下の表は、最も重要なメタデータ関数のいくつかとその簡単な説明をまとめたものです。 識別子tab_idを持つテーブルの指定された識別子col_idを持つ列の名前を返します。 列「LastName」の名前を返します。 SELECT COL_NAME (OBJECT_ID("Employee"), 3) 指定された列に関する情報を返します。 PRECISION プロパティの値を返します -- Employee テーブルの Id 列の場合 SELECT COLUMNPROPERTY (OBJECT_ID("Employee"), "Id", "precision") データベース プロパティの値を返します。 IsNullConcat プロパティの値を返します -- SampleDb データベースの場合は SELECT DATABASEPROPERTY ("SampleDb", "IsNullConcat") データベース ID db_name を返します。 データベース名が指定されていない場合は、現在のデータベースの ID が返されます。 db_id を持つデータベースの名前を返します。 識別子が指定されていない場合は、現在のデータベースの名前が返されます。 テーブル table のインデックス付き列の名前を返します。 列は、インデクス識別子 i とそのインデクス内の列位置 no で示されます。 指定されたテーブル ID 番号の名前付きインデックスまたは統計のプロパティ、インデックスまたは統計の名前、およびプロパティの名前を返します。 識別子 obj_id を持つデータベース オブジェクトの名前を返します。 SELECT OBJECT_NAME(245575913); データベースのオブジェクト識別子 obj_name を返します。 戻り値 245575913 - 従業員テーブル ID SELECT OBJECT_ID("従業員") 現在のデータベースからオブジェクトに関する情報を返します。 SELECT CURDATE()、CURTIME()、NOW(); INSERT INTO incoming (id_vendor, date_incoming) VALUES ("2", curdate()); SELECT id_vendor、date_incoming FROM incoming; SELECT ADDDATE("2011-04-15 00:02:00", INTERVAL "02:45" HOUR_MINUTE); SELECT SUBDATE("2011-04-15 00:02:00", INTERVAL "23:53" HOUR_MINUTE); SELECT PERIOD_ADD(201102, 2); FRAC_SECOND - マイクロ秒 SELECT TIMESTAMPADD(DAY, 2, "2011-04-02"); SELECT TIMEDIFF("2011-04-17 23:50:00", "2011_04-16 14:50:00"); SELECT PERIOD_DIFF(201108, 201001); FRAC_SECOND - マイクロ秒 SELECT TIMESTAMPDIFF(DAY, "2011-04-02", "2011-04-17") AS 日, TIMESTAMPDIFF(HOUR, "2011-04-16 20:14:00", "2011-04-17 23:58) :20") AS時間。 SELECT SUBTIME("2011-04-18 23:17:00", "02:15:30"); SELECT DATE("2011-04-15 00:03:20"); SELECT TIME("2011-04-15 00:03:20"); SELECT TIMESTAMP("2011-04-17"); SELECT DAY("2011-04-17")、DAYOFMONTH("2011-04-17"); SELECT DAYNAME("2011-04-17")、DAYOFWEEK("2011-04-17")、WEEKDAY("2011-04-17"); SELECT WEEK("2011-04-17"), WEEKOFYEAR("2011-04-17 23:40:00"); SELECT MONTH("2011-04-17"), MONTHNAME("2011-04-17"); SELECT QUARTER("2011-04-17"); SELECT YEAR("2011-04-17"); SELECT DAYOFYEAR("2011-04-17"); SELECT HOUR("2011-04-17 18:20:03"); SELECT TIME_TO_SEC("22:10:30"), SEC_TO_TIME(45368); SELECT MAKEDATE(2011, 120);
SELECT 名前、生年月日、CURRENT_DATE、(YEAR(CURRENT_DATE)-YEAR(誕生)) - (RIGHT(CURRENT_DATE,5)
SELECT 名前、生年月日 FROM ユーザー WHERE MONTH(birth) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));
または別のオプション
ペットから名前、誕生を選択 WHERE MONTH(誕生) = MOD(MONTH(NOW()), 12) + 1;
今()– 現在の日付と時刻を返します。
CURDATE()– 現在の日付を返します。
時間()– 現在時刻を返します。
日付()– 日付と時刻の 2 つの部分で構成されます。
抽出する()– 単一の日付/時刻値を返します。
DATE_ADD()– 指定された日数/分/時間などをサンプルに追加します。
DATE_SUB()– 日付から指定された間隔を減算します。
DATEDIFF()– 2 つの日付の間の時間値を返します。
DATE_FORMAT()– 時刻情報の各種出力機能。ADDDATE(日付,INTERVAL 式単位)、ADDDATE(式,日)
ADDTIME(expr1,expr2)
CONVERT_TZ(dt,from_tz,to_tz)
CURDATE()
CURRENT_DATE と CURRENT_DATE()
時間()
CURRENT_TIME と CURRENT_TIME()
CURRENT_TIMESTAMP および CURRENT_TIMESTAMP()
日付(式)
DATEDIFF(expr1,expr2)
DATE_ADD(日付,INTERVAL式の単位)、DATE_SUB(日付,INTERVAL式の単位)
DATE_FORMAT (日付,形式)
DATE_SUB(日付,INTERVAL 式の単位)
DAY(日付)
DAYNAME(日付)
DAYOFMONTH(日付)
DAYOFWEEK(日付)
DAYOFYEAR(日付)
EXTRACT(日付からの単位)
FROM_DAYS(N)
FROM_UNIXTIME(unix_timestamp)、FROM_UNIXTIME(unix_timestamp,format)
HOUR(時間)
LAST_DAY(日付)
LOCALTIME と LOCALTIME()
LOCALTIMESTAMP と LOCALTIMESTAMP()
MAKEDATE(年、年の日)
MAKETIME(時、分、秒)
マイクロ秒(expr)
MINUTE(時間)
月(日付)
月名(日付)
今()
PERIOD_ADD(P,N)
PERIOD_DIFF(P1,P2)
四半期(日付)
2回目)
SEC_TO_TIME(秒)
STR_TO_DATE(str,format)
SUBDATE(日付,INTERVAL expr 単位) および SUBDATE(expr,days)
SUBTIME(expr1,expr2)
SYSDATE()
時間(expr)
TIMEDIFF(expr1,expr2)
タイムスタンプ (expr)、タイムスタンプ (expr1、expr2)
TIMESTAMPADD(単位、間隔、日時_式)
TIMESTAMPDIFF(単位,日時_式1,日時_式2)
TIME_FORMAT(時間,フォーマット)
TIME_TO_SEC(時間)
今日の日付)
UNIX_TIMESTAMP()、UNIX_TIMESTAMP(日付)
UTC_DATE、UTC_DATE()
UTC_TIME、UTC_TIME()
UTC_TIMESTAMP、UTC_TIMESTAMP()
WEEK(日付[,モード])
平日(日付)
WEEKOFYEAR(日付)
年(日付)
YEARWEEK(日付)、YEARWEEK(日付、モード)
集計関数
スカラー関数
数値関数
Transact-SQL 数値関数
関数
構文
説明
使用例
ABS
ABS(n)
ACOS、ASIN、ATAN、ATN2
ACOS(n)、ASIN(n)、ATAN(n)、ATN2(n, m)
COS、SIN、TAN、COT
COS(n)、SIN(n)、TAN(n)、COT(n)
度、ラジアン
度(n)、ラジアン(n)
シーリング
天井(n)
ラウンド
ROUND(n, p, [t])
床
フロア(n)
経験値
経験値(n)
ログ、ログ10
LOG(n)、LOG10(n)
P.I.
PI()
力
パワー(x, y)
ランド
ランド()
ROWCOUNT_BIG
ROWCOUNT_BIG()
サイン
サイン(n)
スクエア、スクエア
SQRT(n)、SQUARE(n)
日付関数
Transact-SQL の日付関数
関数
構文
説明
使用例
日付の取得
GETDATE()
日付部分
DATEPART (項目、日付)
日付名
DATENAME (項目、日付)
日付の差分
DATEDIFF (項目、dat1、dat2)
日付追加
DATEADD (項目、n、日付)
文字列関数
Transact-SQL 文字列関数
関数
構文
説明
使用例
アスキー、ユニコード
ASCII(文字)、UNICODE(文字)
チャー、NCHAR
CHAR(int)、NCHAR(int)
CHARINDEX
CHARINDEX (str1、str2)
違い
差分 (str1, str2)
左右
LEFT (文字列、長さ)、RIGHT (文字列、長さ)
レン
長さ(長さ)
下部、上部
下限(文字列)、上限(文字列)
リトリム、リトリム
LTRIM(str)、RTRIM(str)
引用名
QUOTENAME(文字列)
パティンインデックス
PATINDEX (%p%, expr)
交換する
REPLACE (str1、str2、str3)
複製する
レプリケート (str, i)
逆行する
リバース (str)
サウンデックス
SOUNDEX (文字列)
空間
スペース(長さ)
STR
STR (f[, len[, d]])
もの
STUFF (str1, a, 長さ, str2)
部分文字列
サブストリング (str1、a、長さ)
システム機能
Transact-SQL システム関数
関数
構文
説明
使用例
キャスト
CAST (w AS タイプ [(長さ)]
合体
合体(a1、a2)
COL_LENGTH
COL_LENGTH (オブジェクト、列)
変換する
CONVERT (タイプ[(長さ)], w)
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP
現在の使用者
現在の使用者
データ長
データ長(z)
ゲタンシヌル
GETANSINULL("データベース名")
無効である
ISNULL (式、値)
イスヌメリック
ISNUMERIC (式)
NEWID
NEWID()
ニューシーケンシャルID
NEWSEQUENTIALID()
NULLIF
NULLIF (式1、式2)
サーバープロパティ
SERVERPROPERTY (プロパティ名)
SYSTEM_USER
SYSTEM_USER
ユーザーID
ユーザーID()
USER_NAME
USER_NAME()
メタデータ関数
Transact-SQL メタデータ関数
関数
構文
説明
使用例
COL_NAME
COL_NAME (tab_id、col_id)
列プロパティ
COLUMNPROPERTY (ID、列、プロパティ)
データベースプロパティ
DATABASEPROPERTY (データベース、プロパティ)
DB_ID
DB_ID()
DB_NAME
DB_NAME()
INDEX_COL
INDEX_COL (テーブル、i、いいえ)
インデックスプロパティ
INDEXPROPERTY (obj_id、index_name、プロパティ)
OBJECT_NAME
OBJECT_NAME (obj_id)
オブジェクトID
OBJECT_ID (オブジェクト名)
オブジェクトプロパティ
OBJECTPROPERTY (obj_id、プロパティ)
SQL - レッスン 13. 日付と時刻の関数
これらの関数は、カレンダー データ型で動作するように設計されています。 最も当てはまるものを見てみましょう。
さて、今日はここまでです。 次回は、日付をある形式から別の形式に変換するのに役立つ関数を見ていきます。
CURDATE() 関数と NOW() 関数は、現在時刻を使用するレコードをデータベースに追加する場合に便利です。 当店では全ての配送・販売は現在の時刻を使用しております。 したがって、配送や販売に関するレコードを追加するには、CURDATE() 関数を使用すると便利です。 たとえば、商品が店舗に到着したとします。その商品に関する情報を Delivery (incoming) テーブルに追加してみましょう。
配達日を datatime 型として保存する場合は、NOW() 関数の方が適しています。
最初のサプライヤーの日付を入力するときに間違いがあったと仮定して、その日付を 1 日減らしてみましょう。
値には日だけでなく、週 (WEEK)、月 (MONTH)、四半期 (QUARTER)、年 (YEAR) も指定できます。 たとえば、2 番目のサプライヤーの納期を 1 週間短縮してみましょう。
Deliveries (incoming) テーブルでは、配達日 (date_incoming) 列に日付タイプを使用しました。 レッスン 2 で思い出したように、このデータ型は日付のみを保存するように設計されています。 ただし、datatime 型を使用すると、日付だけでなく時刻も表示されます。 次に、時間に対しても ADDDATE 関数を使用できます。 この場合の値は、秒 (SECOND)、分 (MINUTE)、時間 (HOUR)、およびそれらの組み合わせです。
分と秒 (MINUTE_SECOND)、
時、分、秒 (HOUR_SECOND)、
時と分 (HOUR_MINUTE)、
日、時、分、秒 (DAY_SECOND)、
日、時、分 (DAY_MINUTE)、
日と時間 (DAY_HOUR)、
年と月 (YEAR_MONTH)。
たとえば、2011 年 4 月 15 日の日付に 2 時間 45 分を追加してみましょう。
SECOND - 秒
分 - 分
HOUR - 時間
日 - 日
週 - 週
月 - 月
クォーター - ブロック
年 - 年
SECOND - 秒
分 - 分
HOUR - 時間
日 - 日
週 - 週
月 - 月
クォーター - ブロック
年 - 年