SQL Server 新規にログインアカウントを作成する

SQL Server 新規にログインアカウントを作成する

SQL Server の勉強をする場合、もしもに備えて仕事で使うデータベースやログインアカウントは使いたくないですね。
SQL Serverは簡単にデータベースやログインアカウントを作成できます。

基本的にSQL Serverの学習を行う際は、自前のPCにデータベースを作成し新規に学習ユーザーを作ることが、セキュリティ上からも間違えてデータベースを壊さないためにも良いです。

今回は新規にログインアカウントを作成する方法を紹介します。
新規ログインアカウント名を「Learning-user」と言う名前にします。

新規にログインアカウントを作成する

操作はとっても簡単なのですが、作成する権限が必要なので仕事先で作成する場合はデータベース管理者に相談してください。
自前で自分のPCにSQL Serverをインストールした場合は、saでログインするか、sysadminもしくはsecurityadmin権限があるか確認してください。
もし権限が無い状態で新規にローカルアカウントを作成すると以下のようなエラーになります。

新しいログインを作る

SSMS(SQL Server Management Studio)を立ち上げます。
作成したいSQL Serverを展開し、更に[セキュリティ]を展開します。
[ログイン]が展開表示されるので、右クリックでコンテキストメニューを開きます。
コンテキストメニューの中から[新しいログイン]をクリックします。

ログイン-新規作成

ログイン-新規作成画面が表示されます。

他のデータベースに影響を与えないようにするために、作成するログインアカウントはSQL Server認証が好ましいです。
会社や仕事先の場合は、データベース管理者に相談してください。

ログイン名を任意に入力します。(例ではLearning-user)
SQL Server 認証を選択します。
パスワードを任意の文字列で入力します。
パスワードポリシーを適用するは基本的に必要ないと思います。
※重要なデータベースに元々アクセスしないし学習用なので。
既定のデータベースが決まって居れば指定してください。
最後にOKボタンをクリックして登録します。

作ったログインアカウントにデータベースのアクセス権を与える

作ったばかりのログインユーザーは権限を持っていないため、アクセス拒否をされてしまします。
試しにアクセス拒否されると以下のようになります。

そこで、作成したlearning-userがlearning-dbにアクセスできるようにします。
SSMSを立ち上げます。
SQL Serverを展開し、更に[データベース]を展開します。
既存のデータベースが展開表示されるので、Learning-dbデータベースを確認します。
[Learning-dbデータベース]をクリックして展開します。
[セキュリティ]を展開します。
[ユーザー]を展開します。
Learning-userが居ないことを確認します。
[ユーザー]を右クリックしてコンテキストメニューを開きます。
[新しいユーザー(N)]をクリックします。

「データベース ユーザー – 新規」画面が表示されます。
ログイン名の右端にあるボタンをクリックします。

「ログインの選択」画面が表示されます。
[参照(B)]ボタンをクリックします。

「オブジェクトの参照」画面が表示されます。
今回は先ほど作成した新規ログインアカウントを探して、チェックボックスにチェックを入れます。
[OK]ボタンをクリックします。

「ログインの選択」画面が表示されます。
選択するオブジェクト名欄にlearning-userが表示されているのを確認します。
[OK]ボタンをクリックします。

「データベース ユーザー – 新規」画面が表示されます。
ログイン名が表示されているので確認します。
ユーザー名を適当な名前で入れます。今回はそのままlearning-userとしました。
右側のページ選択メニューのメンバーシップをクリックします。

「データベース ロールのメンバーシップ」設定画面が表示されます。
db_datareader、db_datawriter、db_ddladminにチェックを入れます。
簡単なロールの役割を下に記述しておきます。
[OK]ボタンをクリックします。

Learning-userが追加されていることを確認します。

SSMSでログインできることを確認します。
正常にログインできたらデータアクセスが出来るようになっています。
データベース操作で足らない権限が出てきたらロールを追加して対応します。

固定データベース ロール

固定データベース ロールとその機能を示します。
これらのロールは、すべてのデータベースに存在します。

固定データベース ロールの名前 [説明]
db_owner db_owner 固定データベース ロールのメンバーは、データベースでのすべての構成作業とメンテナンス作業を実行でき、 SQL Serverでデータベースを削除することもできます。 ( SQL データベース と SQL データ ウェアハウスでは、一部のメンテナンス作業にサーバー レベルの権限が必要であり、 db_ownersでは実行できません。)
db_securityadmin db_securityadmin 固定データベース ロールのメンバーは、ロールのメンバーシップを変更し、権限を管理できます。このロールにプリンシパルを追加すると、特権が意図せず昇格されることがあります。
db_accessadmin db_accessadmin 固定データベース ロールのメンバーは、Windows ログイン、Windows グループ、および SQL Serverログインのデータベースに対するアクセスを追加または削除できます。
db_backupoperator db_backupoperator 固定データベース ロールのメンバーは、データベースをバックアップできます。
db_ddladmin db_ddladmin 固定データベース ロールのメンバーは、すべての DDL (データ定義言語) コマンドをデータベースで実行できます。
db_datawriter db_datawriter 固定データベース ロールのメンバーは、すべてのユーザー テーブルのデータを追加、削除、または変更できます。
db_datareader db_datareader 固定データベース ロールのメンバーは、すべてのユーザー テーブルからすべてのデータを読み取ることができます。
db_denydatawriter db_denydatawriter 固定データベース ロールのメンバーは、データベース内のユーザー テーブルのデータを追加、変更、または削除することはできません。
db_denydatareader db_denydatareader 固定データベース ロールのメンバーは、データベース内のユーザー テーブルのデータを読み取ることはできません。

以上、簡単な説明でしたが「SQL Server 新規にログインアカウントを作成する」でした。

SQL Server 新規にデータベースを作成する

SQL Server 新規にデータベースを作成する

SQL Server の勉強をする場合、もしもに備えて仕事で使うデータベースやログインアカウントは使いたくないですね。
SQL Serverは簡単にデータベースやログインアカウントを作成できます。

基本的にSQL Serverの学習を行う際は、自前のPCにデータベースを作成し新規に学習ユーザーを作ることが、セキュリティ上からも間違えてデータベースを壊さないためにも良いです。

今回は新規にデータベース・Databaseを作成する方法を紹介します。
今回は新規データベース名を「Learning-db」と言う名前にします。

新規にデータベースを作成する

操作はとっても簡単なのですが、作成する権限が必要なので仕事先で作成する場合はデータベース管理者に相談してください。
自前で自分のPCにSQL Serverをインストールした場合は、saでログインするか、sysadminもしくはdbcreator権限があるか確認してください。
もし権限が無い状態で新規にローカルアカウントを作成すると以下のようなエラーになります。



新規データベース「Learning-db」を作成する

SSMS(SQL Server Management Studio)を立ち上げます。
作成したいSQL Serverを展開し、更に[データベース]を展開します。
既存のデータベースが展開表示されるので、新規データベースと同じ名前のデータベースが無いことを確認します。
[データベース]を右クリックしてコンテキストメニューを開きます。
コンテキストメニューの中から[新しいデータベース(N)]をクリックします。

新しいデータベース画面が立ち上がります。
データベース名を入力します。
※後から変更も可能です。
基本的に何も変更せずに追加を行えば良いのです。
しかし、もし使用領域の制限を設けたいなら「自動拡張/最大サイズ」で下のように設定してください。
また、右スクロールすると「パス」でデータベースファイルを作るドライブやディレクトリを指定できます。
テスト用のドライブやディレクトリに入れたい場合は変更します。
「追加(A)」ボタンはデータベースファイルを増やしたい時に使用します。
この辺りの設定はテストでは必要ないですが、実運用の際はパフォーマンスに影響を及ぼすため、後日説明したいと思います。
最後に「OK」ボタンをクリックしてLearning-dbを作成します。

使用領域の制限を設ける

「ファイルの最大サイズ」を次のサイズに制限を選択してMB単位で指定します。
下の場合は、1Gまで拡張出来るように設定しました。
「OK」をクリックして設定します。

新規に作成したLearning-dbが作成されているか確認します。
正常に表示されていればOKです。

データベースを削除する

必要が無くなったデータベースを簡単に削除できます。
※簡単に削除出来てしまうため、注意が必要です。

SSMSを立ち上げます。
SQL Serverを展開し、更に[データベース]を展開します。
既存のデータベースが展開表示されるので、削除を行いたいデータベースを確認します。
[削除したいデータベース]を右クリックしてコンテキストメニューを開きます。
コンテキストメニューの中から[削除(D)]をクリックします。

確認画面が表示されますので間違いがなければ「OK」ボタンをクリックします。
以上で(削除権限が有れば)データベースが削除されます。

データベース名を変更する

データベース名の変更も簡単にできます。
※簡単に変更が出来てしまうため、注意が必要です。

SSMSを立ち上げます。
SQL Serverを展開し、更に[データベース]を展開します。
既存のデータベースが展開表示されるので、名前の変更を行いたいデータベースを確認します。
[名前を変更したいデータベース]を右クリックしてコンテキストメニューを開きます。
コンテキストメニューの中から[名前の変更(M)]をクリックします。

下のようにテキスト入力可能になりますので任意の名前を入力します。

データベースのプロパティを確認する

データベースのプロパティを変更することで多くのカスタマイズが可能になります。

例えば、データベースの処理速度が重たくなって場合、別ドライブを追加し物理アクセスの負担を軽くする場合にファイルを増やして対応するなど。
ユーザー権限の設定もできます。
また、自動圧縮するしないとか照合順序を変更してカタカナ検索をカスタマイズするなど。
かなりのことができますので、勉強がてら壊しても問題のないテスト用データベースをカスタマイズするのも良いです。

以上、簡単な説明でしたが「SQL Server 新規にデータベースを作成する」でした。

SSMS とは

SQL Server Management Studio (SSMS) とは

SSMS(SQL Server Management Studio) は、SQL Server や Azure SQL Database の SQL インフラストラクチャを管理するための統合環境です。
SSMS には、SQL のインスタンスを構成したり、状況を監視したり、データベースを管理するためのツールが備わっています。
SSMS を使用して、アプリケーションで使われるクエリとスクリプトを作成したりすることもできます。

SSMSを利用すると、データベースがローカル コンピューター上にあっても、ローカルネットワーク上にあっても、クラウドにあっても、どこにあっても作業ができます。

SSMS は無料です。

SSMS の入手

SSMSはマイクロソフトからダウンロード可能です。
SSMS ダウンロード で検索すると一番上に出てくると思いますので、ダウンロードしてインストールしてください。

インストールは他のWebサイトに詳しく書いてあると思うので、各自でググってください。
このWebサイトにも「SQL Server 2017 Express エディション のインストール」に少し載せているので、良かったら参考にしてください。

無料ですので是非ともSQLの学習に役立ててください。

 

SSMSの使い方

SSMS は、SQL のインスタンスを構成したり、状況を監視したり、データベースを管理するためのツールで、アプリケーションで使われるクエリとスクリプトを作成したりすることもできます。
なので非常に多岐にわたる使用方法が有ります。

 

ここでは、SQLの学習のみに焦点をあてて、クエリを書くところまで説明します。

SSMS 起動

スタートから「Microsoft SQL Server xxxx」もしくは「Microsoft SQL Server Tools xx」を探し展開します。
その中から「Microsoft SQL Server Management…」を探しクリックします。

もしくは、検索にキーワード「ssms」を入力して「Microsoft SQL Server Management Studio」をクリックします。

 

新しいクエリ画面の表示

データベースのデータを操作するには「クエリ」を書きます。
SQL言語でデータ操作を行う文をクエリ画面に書きます。

SSMSを立ち上げると下のような画面が表示されます。
サーバーを確認して、データベースを展開します。
データベースをクリックして選択します。
メニューバーの「新しいクエリ(N)」をクリックします

 

クエリの入力と実行

大きな赤枠の部分にSQL文を記述します。
記述し終わったら、左上の「!実行」ボタンをクリックして、SQL文を実行します。
正常に処理されると、下に結果が表示されます。
エラーが発生するとその原因が表示されます。

 

以上、SSMSとは、でした。

SQL Server 2017 Express インストールエラー

SQL Server 2017 Express インストールエラー

SQL Server 2017 Express をインストール中に下のような画面が表示され、インストールが出来なくなりました。
今まで数えきれないほどインストールしてきたのですが、このような画面は初めてでした。

ちなみに、今回のPCは新規にwindows 10 ProをインストールしたてのPCでインストール直後に Windows Update されたかも不明でした。

 

とりあえず再起動が必要とのことで、再起動を行い実施したのですが同じ状況でした。

 

そこでWindows Update を実施しました。

まずは、「Windows の設定」画面を開きます。
Windows 10 の開き方は『Windows 10 「Windows の設定」画面を開くには』まで。
次に「更新とセキュリティ」をクリックします。

 

Windows Update の「更新プログラムのチェック」を実行します。

 

Windows Update の更新プログラムの確認が始まります。
更新プログラムが有れば自動で更新されます。
再起動などの必要が有る場合もあります。

 

今回のエラーはWindows Update を実行することで解消され、その後正常にインストールされました。
もし、SQL Server 2017 Express インストールエラーが出た場合、Windows Update で解消されるかもしれません。

以上、「SQL Server 2017 Express インストールエラー」の紹介でした。

Sql Server 偏差値の求め方

Sql Server 偏差値の求め方

Sql Server を使って偏差値を求める SQL の書き方です。
偏差値とは、ある数値がサンプルの中でどれくらいの位置にいるかを表した無次元数。
平均値が50、標準偏差が10となるように標本変数を規格化したものです。
偏差値の利用価値が高いのは、サンプルの数値の分布が正規分布に近い状態の時で、試験などの結果に良く利用されていますね。

偏差値の求め方

偏差値の求め方ですが、
1)平均点を求める
2)平均点との差を求める
3)平均点との差の平方数を求める
4)分散を求める
5)標準偏差を求める
6)平均点との差に10をかけ標準偏差で割り+50する
以上で偏差値が求まります。

SQL で偏差値を求める

偏差値を求める SQL の例を示します。

declare @tbl1 table(c_point int)
insert into @tbl1 (c_point) values (30)
insert into @tbl1 (c_point) values (40)
insert into @tbl1 (c_point) values (20)
insert into @tbl1 (c_point) values (60)
insert into @tbl1 (c_point) values (60)
insert into @tbl1 (c_point) values (70)
insert into @tbl1 (c_point) values (80)

--平均値を算出する
declare @avg float
select @avg=AVG(cast(c_point as float)) from @tbl1

--標準偏差を求める
declare @varp float
select @varp=STDEVP(c_point) from @tbl1
select @varp

--平均点との差に10をかけ標準偏差で割り+50する
declare @tbl2 table(c_point int, c_standardscore float)
insert into @tbl2 select c_point, ((c_point-@avg)*10)/@varp+50 from @tbl1
select * from @tbl2
SQLで偏差値を求めた結果

SQLで偏差値を求めた結果

以上、「Sql Server 偏差値の求め方」でした。

SQL Server 標準偏差: STDEVP, STDEV

標準偏差: STDEVP, STDEV

標準偏差とは、データの平均値から見た「ばらつき具合」を数値で表したもので、標準偏差の値が小さいほど、散らばり度合いが小さいことが示されます。

指定したデータが、全てであるデータの値がとる標準偏差(standard deviation) が欲しい場合、STDEVP 関数を使います。
指定したデータが、全体の内の「標本」(抜き出した一部分)として考え、そのデータの値がとる標本標準偏差(sample standard deviation) が欲しい場合、STDEV 関数を使います。

STDEVPの数値の求め方は、
・平均値を算出する
・各データと平均値の差を求める
・各差を2乗して
・2乗した全ての求めた数値を足す
・最後にデータの数で割る(分散)
・分散の平方根を求める

STDEVの数値の求め方は、
・平均値を算出する
・各データと平均値の差を求める
・各差を2乗して
・2乗した全ての求めた数値を足す
・最後にデータ-1の数で割る(*日本工業規格)(標本分散)
・標本分散の平方根を求める
以上となりますが・・・

「標準偏差」や「分散」という言葉自体をあまり聞いたことのない方にとっては、何を言っているのか、非常に難しい説明と感じることでしょう。
わかりづらいと感じた方は、次のように覚えてください。

「計算された数値が、小さいほどデータのばらつきが少ない」
という意味で、つまり、出てきた数値が「0」に近いほどばらつきの無いデータということが分かる手法になります。

標準偏差のよいところは、標準偏差は単位の次元がデータと同じなのでデータの散らばり具合が把握しやすいと言えます。

STDEVP

構文:STDEVP( [ ALL | DISTINCT ] expression )
引数:ALL すべての値にこの集計関数を適用します。 ALL は既定値です。
   DISTINCT 重複する値は 1 つだけ有効データとします。
   expression 整数式です。 集計関数とサブクエリは使用できません。

戻り値の型は、float です。

下のSQL例文を載せておきます。

declare @tbl1 table(c_point int)
insert into @tbl1 (c_point) values (30)
insert into @tbl1 (c_point) values (40)
insert into @tbl1 (c_point) values (20)
insert into @tbl1 (c_point) values (60)
insert into @tbl1 (c_point) values (60)
insert into @tbl1 (c_point) values (70)
insert into @tbl1 (c_point) values (80)

--ALL
--NULLはカウントされません
select STDEVP(c_point) as [STDEVP] from @tbl1

--DISTINCT
--60が重複しているので結果が異なります
select STDEVP(DISTINCT c_point) as [STDEVP_DISTINCT] from @tbl1

-- STDEVP(c_point)を検証
--平均値を算出する
declare @avg float
select @avg=AVG(cast(c_point as float)) from @tbl1

--各データと平均値の差を求める
declare @tbl2 table(c_point float)
insert into @tbl2 select c_point-@avg from @tbl1

--各差を2乗して
update @tbl2 set c_point=c_point*c_point

--2乗した全ての求めた数値を足す
declare @sum float
select @sum=sum(c_point) from @tbl2

--データの数で割る
declare @varp float
select @varp=@sum/count(c_point) from @tbl2

--平方根を求める
select sqrt(@varp) as [STDEVP(c_point)検証値]
STDEVP 結果

STDEVP 結果

STDEV

構文:STDEV( [ ALL | DISTINCT ] expression )
引数:ALL すべての値にこの集計関数を適用します。 ALL は既定値です。
   DISTINCT 重複する値は 1 つだけ有効データとします。
   expression 整数式です。 集計関数とサブクエリは使用できません。

戻り値の型は、float です。

下のSQL例文を載せておきます。

declare @tbl1 table(c_point int)
insert into @tbl1 (c_point) values (30)
insert into @tbl1 (c_point) values (40)
insert into @tbl1 (c_point) values (4)
insert into @tbl1 (c_point) values (60)
insert into @tbl1 (c_point) values (60)
insert into @tbl1 (c_point) values (80)

--ALL
--NULLはカウントされません
select STDEV(c_point) as [STDEV] from @tbl1

--DISTINCT
--60が重複しているので結果が異なります
select STDEV(DISTINCT c_point) as [STDEV_DISTINCT] from @tbl1

-- VAR(c_point)を検証
--平均値を算出する
declare @avg float
select @avg=AVG(cast(c_point as float)) from @tbl1

--各データと平均値の差を求める
declare @tbl2 table(c_point float)
insert into @tbl2 select c_point-@avg from @tbl1

--各差を2乗して
update @tbl2 set c_point=c_point*c_point

--2乗した全ての求めた数値を足す
declare @sum float
select @sum=sum(c_point) from @tbl2

--データの数で割る
--各観測値の平均値からの偏差の二乗の和を観測個数から1を引いた数で割ったばらつきの尺度
declare @var float
select @var=@sum/(count(c_point)-1) from @tbl2

--平方根を求める
select sqrt(@var) as [STDEV(c_point)検証値]
STDEV 結果

STDEV 結果

以上、SQL Server STDEV, STDEVP 標準偏差でした。

SQL Server 分散: VARP, VAR

分散: VARP, VAR

分散とは、データの平均値から見た「ばらつき具合」を数値で表したもので、数学などでは統計学や確率論にて用いられる手法の1つです。

指定したデータが、全てであるデータの値がとる分散(variance) が欲しい場合、VARP 関数を使います。
指定したデータが、全体の内の「標本」(抜き出した一部分)として考え、そのデータの値がとる標本分散 (sample variance) が欲しい場合、VAR 関数を使います。

VARPの数値の求め方は、
・平均値を算出する
・各データと平均値の差を求める
・各差を2乗して
・2乗した全ての求めた数値を足す
・最後にデータの数で割る

VARの数値の求め方は、
・平均値を算出する
・各データと平均値の差を求める
・各差を2乗して
・2乗した全ての求めた数値を足す
・最後にデータ-1の数で割る(*日本工業規格)
以上となりますが・・・

「分散」という言葉自体をあまり聞いたことのない方にとっては、何を言っているのか、非常に難しい説明と感じることでしょう。
わかりづらいと感じた方は、次のように覚えてください。

「計算された数値が、小さいほどデータのばらつきが少ない」

という意味で、つまり、出てきた数値が「0」に近いほどばらつきの無いデータということが分かる手法になります。

VARP

構文:VARP( [ ALL | DISTINCT ] expression )
引数:ALL すべての値にこの集計関数を適用します。 ALL は既定値です。
   DISTINCT 重複する値は 1 つだけ有効データとします。
   expression 整数式です。 集計関数とサブクエリは使用できません。

戻り値の型は、float です。

下のSQL例文を載せておきます。

declare @tbl1 table(c_point int)
insert into @tbl1 (c_point) values (30)
insert into @tbl1 (c_point) values (40)
insert into @tbl1 (c_point) values (4)
insert into @tbl1 (c_point) values (60)
insert into @tbl1 (c_point) values (60)
--insert into @tbl1 (c_point) values (NULL)
insert into @tbl1 (c_point) values (80)

--ALL
--NULLはカウントされません
select VARP(c_point) as VARP from @tbl1

--DISTINCT
--60が重複しているので結果が異なります
select VARP(DISTINCT c_point) as VARP_DISTINCT from @tbl1

-- VARP(c_point)を検証
--平均値を算出する
declare @avg float
select @avg=AVG(cast(c_point as float)) from @tbl1

--各データと平均値の差を求める
declare @tbl2 table(c_point float)
insert into @tbl2 select c_point-@avg from @tbl1

--各差を2乗して
update @tbl2 set c_point=c_point*c_point

--2乗した全ての求めた数値を足す
declare @sum float
select @sum=sum(c_point) from @tbl2

--最後にデータの数で割る
select @sum/count(c_point) as [VARP(c_point)検証値] from @tbl2
VARP 結果

VARP 結果

VAR

構文:VAR( [ ALL | DISTINCT ] expression )
引数:ALL すべての値にこの集計関数を適用します。 ALL は既定値です。
   DISTINCT 重複する値は 1 つだけ有効データとします。
   expression 整数式です。 集計関数とサブクエリは使用できません。

戻り値の型は、float です。

下のSQL例文を載せておきます。

declare @tbl1 table(c_point int)
insert into @tbl1 (c_point) values (30)
insert into @tbl1 (c_point) values (40)
insert into @tbl1 (c_point) values (4)
insert into @tbl1 (c_point) values (60)
insert into @tbl1 (c_point) values (60)
insert into @tbl1 (c_point) values (80)

--ALL
--NULLはカウントされません
select VAR(c_point) as VARP from @tbl1

--DISTINCT
--60が重複しているので結果が異なります
select VAR(DISTINCT c_point) as VARP_DISTINCT from @tbl1

-- VAR(c_point)を検証
--平均値を算出する
declare @avg float
select @avg=AVG(cast(c_point as float)) from @tbl1

--各データと平均値の差を求める
declare @tbl2 table(c_point float)
insert into @tbl2 select c_point-@avg from @tbl1

--各差を2乗して
update @tbl2 set c_point=c_point*c_point

--2乗した全ての求めた数値を足す
declare @sum float
select @sum=sum(c_point) from @tbl2

--最後にデータの数で割る
--各観測値の平均値からの偏差の二乗の和を観測個数から1を引いた数で割ったばらつきの尺度
select @sum/(count(c_point)-1) as [VAR(c_point)検証値] from @tbl2
VAR 結果

VAR 結果

以上、SQL Server VAR, VARP 分散関数でした。

SQL Server 便利な日付関数:DATEADD DATEDIFF

SQL Server 便利な日付関数:DATEADD DATEDIFF

Sql Server は、日付の演算を行える関数を2つ実装しています。

DATEADD

DATEADDは引数に指定した日時要素*間隔分を日時に加算(減算)する関数です。
DATEADD(日時要素, 間隔, 日時)
日時要素は、以下のように指定します。
 年:year, yy, yyy
 四半期:quarter, qq, q
 月:month, mm, m
 日:dayofyear, dy, y, day, dd, d
 週:week, wk, ww
 時:hour, hh
 分:minute, mi, n
 秒:second, ss, s
 ミリ秒:millisecond, ms
 マイクロ秒:microsecond, mcs
 ナノ:nanosecond, ns

間隔は、整数値で指定します。加算ならそのまま、減算なら負の整数値で指定します。
日時は、演算を行いたい日付型データです。

以下の例は、現在日時に対し、いくつかの演算を行っています。

declare @dd datetime
set @dd = getdate()

--DATEADD
select '--基準日--' as 加算データ, @dd as 結果
union all
select '1年プラス', DATEADD(year, 1, @dd)			--1年プラス
union all
select '1年マイナス', DATEADD(year, 1, @dd)		--1年マイナス
union all
select '1月プラス', DATEADD(month, 1, @dd)			--1月プラス
union all
select '1月マイナス', DATEADD(month, -1, @dd)		--1月マイナス
union all
select '1日プラス', DATEADD(day, 10, @dd)			--1日プラス
union all
select '1日マイナス', DATEADD(day, -10, @dd)		--1日マイナス
union all
select '1時間プラス', DATEADD(hour, 1, @dd)		--1時間プラス
union all  
select '1時間マイナス', DATEADD(hour, -1, @dd)		--1時間マイナス
union all  
select '1分プラス', DATEADD(minute, 10, @dd)		--10分プラス  
union all  
select '1分マイナス', DATEADD(minute, -10, @dd)	--10分マイナス  
union all  
select '30秒プラス', DATEADD(second, 30, @dd)	--30秒プラス
union all  
SELECT '30秒マイナス', DATEADD(second, -30, @dd)	--30秒マイナス
DATEADD 結果

DATEADD 結果

DATEDIFF

DATEDIFFは、指定した日時要素単位で 2 つの日付の時間間隔を調べることができます。たとえば、2 つの日付の間の日数や、現在から年末までの週の数などを求めることができます。
DATEDIFF(日時要素, 開始日時, 終了日時, [,firstdayofweek or firstweekofyear])

下の例で @dend に設定する数値を替えいろいろ試して挙動を確認してください。

declare @dstart datetime
declare @dend datetime

set @dstart = getdate()
set @dend = dateadd(day, 100, @dstart)
select @dstart as 開始日時, @dend as 終了日時 

select 'DATEDIFF(year, @dstart, @dend)' as 式, DATEDIFF(year, @dstart, @dend) as 差
union all
select 'DATEDIFF(month, @dstart, @dend)', DATEDIFF(month, @dstart, @dend)
union all
select 'DATEDIFF(week, @dstart, @dend)', DATEDIFF(week, @dstart, @dend)
union all
select 'DATEDIFF(day, @dstart, @dend)', DATEDIFF(day, @dstart, @dend)
union all
select 'DATEDIFF(hour, @dstart, @dend)', DATEDIFF(hour, @dstart, @dend)
union all
select 'DATEDIFF(minute, @dstart, @dend)', DATEDIFF(minute, @dstart, @dend)
DATEDIFF 結果

DATEDIFF 結果

以上、「SQL Server 便利な日付関数:DATEADD DATEDIFF」でした。

SQL Server 文字列を日付型に変換する: CONVERT, CAST

SQL Server 文字列を日付型に変換する: CONVERT, CAST

SQL Serverで文字列を日付型に変換するには CONVERT, CAST を使います。
逆に日付型から文字列にする場合にも CONVERT を使います。
SQL Server 日付の年月日だけを取り出す: CONVERT

CONVERT や CAST は、あるデータ型の式を別のデータ型の式に変換できる非常に便利な関数です。

CAST

CASTを使った変換です。

select 'CAST(''02/27/2018'' AS datetime)' as 式, CAST('02/27/2018' AS datetime) as 結果
union all
select 'CAST(''180227'' AS datetime)', CAST('180227' AS datetime)
union all
select 'CAST(''18/02/27'' AS datetime)', CAST('18/02/27' AS datetime)
union all
select 'CAST(''18/02/27 07:08:09'' AS datetime)', CAST('18/02/27 07:08:09' AS datetime)
union all
select 'CAST(''20180227'' AS datetime)', CAST('20180227' AS datetime)
union all
select 'CAST(''2018/02/27'' AS datetime)', CAST('2018/02/27' AS datetime)
union all
select 'CAST(''2018/02/27 07:08:09'' AS datetime)', CAST('2018/02/27 07:08:09' AS datetime)

結果は、以下の通りです。

SQL Server CASTを使った文字列⇒日付型変換

SQL Server CASTを使った文字列⇒日付型変換

CONVERT

CONVERTを使った変換です。(CASTと変わりませんが。。。)

select 'CONVERT(datetime, ''02/27/2018'')' as 式, CONVERT(datetime, '02/27/2018') as 結果
union all
select 'CONVERT(datetime, ''180227'')', CONVERT(datetime, '180227')
union all
select 'CONVERT(datetime, ''18/02/27'')', CONVERT(datetime, '18/02/27')
union all
select 'CONVERT(datetime, ''18/02/27 07:08:09'')', CONVERT(datetime, '18/02/27 07:08:09')
union all
select 'CONVERT(datetime, ''20180227'')', CONVERT(datetime, '20180227')
union all
select 'CONVERT(datetime, ''2018/02/27'')', CONVERT(datetime, '2018/02/27')
union all
select 'CONVERT(datetime, ''2018/02/27 07:08:09'')', CONVERT(datetime, '2018/02/27 07:08:09')

結果は、以下の通りです。

SQL Server CONVERTを使った文字列⇒日付型変換

SQL Server CONVERTを使った文字列⇒日付型変換

引数の文字列表記

CONVERT も CAST も、引数にいろいろな形式で文字列を与えることができます。
これは使用する言語によってエラーになってしまう場合もあります。

使っているSql Server の設定言語に合わせて引数の文字列を渡すか、SET DATEFORMAT を使ってそのセッションの日付フォーマットを変えるかすると安心です。
基本的に日本のサーバーであれば日本語が使用言語になっていると思いますが、私は格安のアメリカのレンタルサーバーを使うこともあるため、SET DATEFORMAT を使っています。

日本は「年月日」で表記しますが

日本は「年月日」で表記しますが、アメリカは「月日年」、ヨーロッパは「日月年」となります。
セッションの言語によって、datetime の形式とシステム メッセージが決められているので、注意しましょう。

以上、「SQL Server 文字列を日付型に変換する: CONVERT, CAST」の備忘録でした。

SQL Server 言語確認

SQL Server 言語確認

SQL Server は、使用している言語に対応してメッセージなどを表示します。
言語環境が異なるといろいろ戸惑うこと、とくに日付の表記の違いとか、があるので注意が必要です。

SQL Server 使用言語の調べ方

以下のSQLで調べることができます。

select name from syslanguages where langid=(select value from sys.configurations where name='default language')

ただし、デフォルトの言語を知ることができるのみで、セッションで設定された言語は表示されません。
セッションで設定した言語の調べ方は、私も知りません。知ってたらコメントで教えてください。

エラーメッセージ

エラーメッセージの言語も設定した言語に変わります。
言語を変えて以下のようにエラーを発生させてみます。


SET LANGUAGE japanese;
GO
test
GO

SET LANGUAGE us_english;
GO
test
GO

SET LANGUAGE Italian;
GO
test

結果は、以下のようになります。

SQL Server 使用言語の違い エラー表示

SQL Server 使用言語の違い エラー表示

「メッセージ 2812、レベル 16、状態 62、行 3」のエラー情報が日本語のままですが、「ストアド プロシージャ ‘test’ が見つかりませんでした。」はちゃんと変更されてますね。

その他の関連表記

この他にも使用言語は、以下の表記にも関係してきます。
dateformat : 日付要素の順序を指定します。 有効なパラメーターはmdy、 dmy、 ymd、 ydm、 myd、およびdym。
datefirst : 週の最初の曜日を指定します。 設定が 7 なら日曜日です。
months : 言語に合わせた月名を表示。
shortmonths : 言語に合わせた短い月名を表示。
days : 言語に合わせた曜日を表示。

この言語設定に関する情報は syslanguages を参照すると見ることができます。

select * from syslanguages
SQL Server 使用言語設定表

SQL Server 使用言語設定表

この他にもまだ言語に影響される表記もありますが、とりあえずこの辺で。
以上、「SQL Server 使用言語を調べる」でした。