Skip to content

Instantly share code, notes, and snippets.

@UweKeim
Last active August 7, 2019 07:04
Show Gist options
  • Save UweKeim/57014172181d108a51638bf4d856d012 to your computer and use it in GitHub Desktop.
Save UweKeim/57014172181d108a51638bf4d856d012 to your computer and use it in GitHub Desktop.
Convert Oracle "RR" date formats to SQL Server "YYYY" formats
namespace OracleRRConverter
{
// Takes an Oracle-generated SQL script file and converts something like
// to_date('02.10.18','DD.MM.RR') to something like CONVERT(DATETIME,'02.10.2018',126).
// https://dba.stackexchange.com/q/244722/42
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
internal static class Program
{
private static int Main(string[] args)
{
var sourcePath = args[0];
if (string.IsNullOrEmpty(sourcePath))
{
Console.Error.WriteLine(@"No file or directory path as first argument specified.");
return -1;
}
var sourceFilePaths = new List<string>();
if (Directory.Exists(sourcePath))
{
sourceFilePaths.AddRange(Directory.GetFiles(sourcePath, @"*.sql"));
}
else if (File.Exists(sourcePath))
{
sourceFilePaths.Add(sourcePath);
}
else
{
Console.Error.WriteLine($@"File or directory path '{sourcePath}' does not exist.");
return -1;
}
if (sourceFilePaths.Count <= 0)
{
Console.Error.WriteLine(@"No .sql files found/specified.");
return -1;
}
var sourceFolderPath = Path.GetDirectoryName(sourceFilePaths.First());
var backupFolderPath = Path.Combine(sourceFolderPath, $@"backups\{DateTime.Now.Ticks}");
Directory.CreateDirectory(backupFolderPath);
foreach (var sourceFilePath in sourceFilePaths)
{
Console.WriteLine($@"Processing file '{sourceFilePath}'.");
var sourceFileName = Path.GetFileName(sourceFilePath);
var sourceFileNameWithoutExtensions = Path.GetFileNameWithoutExtension(sourceFilePath);
var sourceFileExtension = Path.GetExtension(sourceFilePath);
var backupFilePath = Path.Combine(backupFolderPath,
$@"{sourceFileName}");
var tempFilePath = Path.Combine(sourceFolderPath,
$@"{sourceFileNameWithoutExtensions}.temp.{Guid.NewGuid():N}{sourceFileExtension}");
var ec = Encoding.Default;
using (var reader = new StreamReader(sourceFilePath, ec))
using (var writer = new StreamWriter(tempFilePath, false, ec))
{
var regEx = new Regex(@"to_date\('(\d\d)\.(\d\d)\.(\d\d)','DD\.MM\.RR'\)", RegexOptions.Compiled);
while (reader.Peek() > 0)
{
var line = reader.ReadLine();
if (string.IsNullOrEmpty(line) || !regEx.IsMatch(line))
{
writer.WriteLine(line);
}
else
{
var newLine = line;
// Do some action with the string.
var match = regEx.Match(newLine);
while (match.Success)
{
var day = Convert.ToInt32(match.Groups[1].Value);
var month = Convert.ToInt32(match.Groups[2].Value);
var rr = Convert.ToInt32(match.Groups[3].Value);
var year = rr <= 49 ? 2000 + rr : 1900 + rr;
var replacement = $@"CONVERT(DATETIME,'{year:D4}-{month:D2}-{day:D2}',126)";
newLine =
newLine.Substring(0, match.Index) +
replacement +
newLine.Substring(match.Index + match.Length);
match = regEx.Match(newLine);
}
writer.WriteLine(newLine);
}
}
}
File.Move(sourceFilePath, backupFilePath);
File.Move(tempFilePath, sourceFilePath);
}
return 0;
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment