SQL Server の Date 型に ADO.NET の DbType.Date はうまく動かない

これで例外出るのなんで～？

using (var conn = new SqlConnection(connstr))
using (var cmd = new SqlCommand(@"insert Table_1 values (@id, @date)", conn)) {
  conn.Open();
  cmd.Parameters.Add(new SqlParameter { 
    DbType = DbType.Int32, Value = 1, ParameterName = "id" 
  });
  cmd.Parameters.Add(new SqlParameter { 
    DbType = DbType.Date, Value = new DateTime(1600, 1, 1), ParameterName = "date" 
  });
  cmd.ExecuteNonQuery();
}

こっちは動きます。

using (var conn = new SqlConnection(connstr))
using (var cmd = new SqlCommand(@"insert Table_1 values (@id, @date)", conn)) {
  conn.Open();
  cmd.Parameters.Add(new SqlParameter { 
    DbType = DbType.Int32, Value = 1, ParameterName = "id" 
  });
  cmd.Parameters.Add(new SqlParameter { 
    SqlDbType = SqlDbType.Date, Value = new DateTime(1600, 1, 1), ParameterName = "date" 
  });
  cmd.ExecuteNonQuery();
}

Dapper で SQL Server の date 型の値をパラメータとして渡したい

2016/09/17 コード修正しました

Dapperを使ってDate型のフィールドに1753-1-1以前の値を入れようとすと例外になるのは仕様なのか…マジですか…

2016-09-12 18:37:19 via TweetDeck

を見かけたのでちょっと書いてみる。

何もしていないと、.NET の DateTime は Dapper で DbType.DateTime 扱いなので、以下のような問題が出る。
DapperでDateTime2 - Qiita

上の Qiita でも対応策書いてるけど、個人的には Dapper の DbString を真似たクラスを作って対応してます。

public class DbDate : ICustomQueryParameter {
  public DateTime? Value { get; set; }
  public void AddParameter(IDbCommand command, string name) {
    var param = command.CreateParameter();
    param.ParameterName = name;
    param.Value = Value.HasValue ? (object)Value.Value.Date : DBNull.Value;
    // DbType はダメで、SqlDbType じゃないとダメでした。 
    // https://msdn.microsoft.com/ja-jp/library/cc716729(v=vs.110).aspx ではいけそうですが。。
    var p = param as SqlParameter;
    if (p != null) {
      p.SqlDbType = SqlDbType.Date;
    }
    else {
      param.DbType = DbType.Date;
    }
    command.Parameters.Add(param);
  }
}

使う時はこんな感じ。*1

var query = @"select top(1) [Id] 
from [Hoge] 
where [Hoge日] = @Hoge日";
  
var id = conn.ExecuteScale<int>(query, new {
  Hoge日 = new DbDate { Value = DateTime.UtcNow }
}); 

雰囲気伝わるかな？ datetime2 も同じで DbDateTime2 作ってます。

*1:コンパイル通してないから動かなかったらゴメン

Oracle の add_months を SQL Server に実装する

昨日これ書いたので
Oracle の add_months を SQL Server dateadd(month, ~) の結果と同じにするのを考えてみる‏ - お だ のスペース
どうせなら逆も用意しようかなと。*1

add_months から dateadd(month, ~) は、元が月末の時は、必ず月末になるようにしたら良さそう。*2

SQL Server 2012 以降なら、月末日を取る + 月の加算もしてくれる eomonth があります。
EOMONTH (Transact-SQL)
この blog でも以前取り上げてました。
月末日を取得する - お だ のスペース

移行する方で、今更 2008 とか居ないと思うので、eomonth 使って書いてます。

CREATE FUNCTION [ADD_MONTHS] 
(
  @target date, 
  @plus int
)
RETURNS date
AS
BEGIN
  DECLARE @ret date

  select @ret = case 
    when eomonth(@target) = @target then eomonth(@target, @plus) 
    else dateadd(month, @plus, @target) 
  end

  RETURN @ret

END
GO

ファンクション作らないならこんな感じ。

select 
  case 
    when eomonth(cast(getdate() as date)) = cast(getdate() as date) 
      then eomonth(cast(getdate() as date), 2) 
    else 
      dateadd(month, 2, cast(getdate() as date)) 
  end as ADD_MONTHS

多分ちゃんと動きます。

*1:Oracle から SQL Server への移行される方のために！

*2:時刻は無視します。

Oracle の add_months を SQL Server dateadd(month, ~) の結果と同じにするのを考えてみる‏

twitter で見かけたのでちょっと考えてみる。

SQL Server

select dateadd(month, 1, cast('2016/02/29' as date)) -- 2016/03/29

Oracle

select add_months('2016/02/29', 1) from dual -- 2016/3/31

足す前の日付と足した後の日付の 日 の部分が違った場合で、足した後の方が大きい時だけ問題になるで良いのかな。

1. 日が同じ => 問題無し
2. 日が違って、足した時の方が小さい => 足す前の 日 が足した後の月に存在しない 例：1/30 に 1ヶ月足したら 2/28 or 2/29 になるパターン
3. 日が違って、足した時の方が大きい => 足す前が月末日、足す前の月の日数が足した後の月の日数より少ない。例：4/30 に 1ヶ月足したら 5/31

最後のパターンだけ差し替えるとこんな感じ。*1

create or replace function ADD_MONTHS_SQLSERVER(target date, plus number) return date
is 
  result date;
begin
  select add_months(target, plus) into result from dual;
  select 
    case 
      when to_char(result, 'DD') > to_char(target, 'DD')   
        then to_date(to_char(result, 'YYYYMM') || to_char(target, 'DD'), 'YYYYMMDD')
      else result 
    end into result
  from dual;
  
  return result;
end;

function 作るのいやだったらこんな感じ？

select
  case when to_char(add_months(sysdate, 2), 'DD') > to_char(sysdate, 'DD')
    then to_date(to_char(add_months(sysdate, 2), 'YYYYMM') || to_char(sysdate, 'DD'), 'YYYYMMDD')
    else add_months(sysdate, 2)
  end as add_months_sqlserver
from dual

LAST_DAY とか使うかなーと思ったけど、そうでもなかった。
※雰囲気動くとこまでしか確認してないから利用は自己責任で！

*1:時刻は考慮してない

9/16(金) デブサミ関西 に SQLWorld として参加します

Developers Summit 2016 KANSAI #devsumi

今年で 6回目の Devepolers Summit KANSAI ですが、今回も SQLWorld として参加させていただくことになりました。

今回のデブサミ関西も面白そうな話しがたくさん聞けそうです。
平日開催ですが、ご都合がよければ是非ご参加を～。

今年のコミュニティ LT は懇親会であるそうですので、本編だけでなく懇親会も宜しくお願いします！

SQL Server / SQL Database にクエリを実行する Web アプリのコードを Github で公開しました

SqlWorld :: ホーム のハンズオンで使ってる Webアプリ(TSQL Runner) のコードから一部抜粋・改変して、公開してみました。

TSQLRunnerWeb/TSQLRunnerWeb at master · OdaShinsuke/TSQLRunnerWeb · GitHub

テーブルと列表示する Explorer が未実装だったり、複数の select 書いても最初の select の結果しか表示されなかったりとかしてます。
Explorer は早い目に実装しときます。

8/23(火) SQLWorld★大阪#38 開催します

2週間前の告知！ SqlWorld :: SQLWorld★大阪#38 開催します。20回目の平日夜開催で、前回同様 ハンズオン 形式行う予定です。

【日時】
2016年8月23日（火曜日）　19:00~21:00
 

【イベント概要】
SQLWorld 20回目の平日夜開催～。今回も、みんなで SQL を書いてみようというハンズオン企画です！ブラウザがあれば参加出来るようにしていますので、iPad 等のタブレットでも大丈夫です。
 

【会場】
フェンリル株式会社さま大阪本社 http://www.fenrir-inc.com/
〒530-0011 大阪府大阪市北区大深町 3番1号 グランフロント大阪タワーB（オフィス） 14F
http://info.fenrir-inc.com/jp/profile/overview.html
 

【参加費】
無料
 

【持ち物】
パソコン/タブレット (DB のインストールは不要です。)
 

【参加可能人数】
13 人
 

お題に沿って、SQL を書いてみようという勉強会です。是非ご参加を～。

開催回数は増えていっていますが、続き物というわけでは無いので初めて参加される方でもお気軽にどぞー。