SQL で西暦を和暦(元号)に変換する/Salesforce Marketing Cloud
Salesforce Marketing Cloud のデータベースは、SQL Server です。SQL クエリアクティビティで記述する SQL もそれに準じた構文である必要があります。今回取り上げる西暦・和暦変換ですが、SQL Server では関数などが用意されていません。また、SQL クエリアクティビティは、SELECT 文しか使用できないので、複雑なデータ処理ができません。
色々と制約がありますが、CASE 式を使って西暦・和暦変換する SQL を書いてみました。関数などで自動的に変換しているわけではなく、条件分岐で出し分けているだけなので、改元の際には改修が必要になります。
SQL の説明
SELECT
Seireki_Date,
/* 1. 元号の判定 */
CASE
WHEN Seireki_Date > CONVERT(DATETIME, '2019/04/30') THEN
'令和'
WHEN Seireki_Date > CONVERT(DATETIME, '1989/01/07') THEN
'平成'
WHEN Seireki_Date > CONVERT(DATETIME, '1926/12/24') THEN
'昭和'
WHEN Seireki_Date > CONVERT(DATETIME, '1912/07/29') THEN
'大正'
WHEN Seireki_Date > CONVERT(DATETIME, '1868/01/24') THEN
'明治'
END +
/* 2. 年数の変換 */
CONVERT(
NVARCHAR,
CASE
WHEN Seireki_Date > CONVERT(DATETIME, '2019/04/30') THEN YEAR(Seireki_Date) - 2018
WHEN Seireki_Date > CONVERT(DATETIME, '1989/01/07') THEN YEAR(Seireki_Date) - 1988
WHEN Seireki_Date > CONVERT(DATETIME, '1926/12/24') THEN YEAR(Seireki_Date) - 1925
WHEN Seireki_Date > CONVERT(DATETIME, '1912/07/29') THEN YEAR(Seireki_Date) - 1911
WHEN Seireki_Date > CONVERT(DATETIME, '1868/01/24') THEN YEAR(Seireki_Date) - 1867
END
)
+ '年'
/* 3. 月日の整形 */
+ MONTH(Seireki_Date) + '月' + DAY(Seireki_Date) + '日' AS Wareki_Date
FROM
test_ConvertToWareki_de
1. 元号の判定
「Seireki_Date」には、日付型の日付データが格納されています。以下のように条件分岐させて、元号を判定します。
2. 年数の変換
1 と同じ条件式で分岐させた後、年数を変換します。日付の年部分だけを取得するために、YEAR 関数を使用しています。変換した年数はテキスト型に変換して、1 で抽出する元号の後ろに結合します。
3. 月日の整形
月日は変わらないので、MONTH 関数と DAY 関数で月日を抜き出して「○月○日」の形式に整形します。