Update dbase blank columns before published in SQL Server

dbase_SSIS When I was designing a SSIS package to move dbase (DBF) files to SQL Server tables. I noticed I was going have to clean up the VARCHARs and DATETIMEs in the database before I could publish the data for BI Team. In my SSIS Package these are the last two tasks. The two queries below dynamically get the columns data types and names and does update statements on the columns to correct them.

Update DateTimes to NULL

declare @tab table (id int identity(1,1) primary key,SQLtable varchar(50),SQLcolumn VARCHAR(50))
insert into @tab(SQLtable,SQLcolumn)

select
so.name table_name
,sc.name column_name
--,st.name data_type
from sysobjects so
inner join syscolumns sc on (so.id = sc.id)
inner join systypes st on (st.type = sc.type)
where so.type = 'U'
and st.name IN ('DATETIME', 'DATE')
declare @sql varchar(8000), @i int,@count int, @sqltable varchar(50), @sqlcolumn varchar(50)

set @i=1
select @count= count(*) from @tab
while @i <= @count

begin
select @SQLtable=SQLtable from @tab where id=@i
select @sqlcolumn =sqlcolumn from @tab where id=@i

set @sql = '
UPDATE '+@SQLtable+'
SET '+@SQLcolumn+' = CASE WHEN '+@SQLcolumn+' = ''1899-12-30''
THEN NULL
ELSE '+@SQLcolumn+'
END
'
--To print in the Result/Message box
print @sql
--To EXECUTE
--EXEC (@sql)

set @i=@i+1
end

Update Varchars to NULL

declare @tab table (id int identity(1,1) primary key,SQLtable varchar(50),SQLcolumn VARCHAR(50))
insert into @tab(SQLtable,SQLcolumn)

 select
 so.name table_name
 ,sc.name column_name
 --,st.name data_type
from sysobjects so
inner join syscolumns sc on (so.id = sc.id)
inner join systypes st on (st.type = sc.type)
where so.type = 'U'
and st.name IN ('nvarchar', 'varchar', 'char', 'nchar')
--Added because of Error
AND sc.name <> 'group'

declare @sql varchar(8000), @i int,@count int, @sqltable varchar(50), @sqlcolumn varchar(50) 

set @i=1
select @count= count(*) from @tab
while @i <= @count

begin
select @SQLtable=SQLtable from @tab where id=@i
select @sqlcolumn =sqlcolumn from @tab where id=@i 

set @sql = '
 UPDATE '+@SQLtable+'
 SET '+@SQLcolumn+' = CASE WHEN LEN('+@SQLcolumn+') = 0
 THEN NULL
 ELSE '+@SQLcolumn+'
 END
'
--To print in the Result/Message box
print @sql
--To EXECUTE
--EXEC (@sql)
set @i=@i+1
end

Turn Unusable Date into usable SQL DateTime

We had situation where we had an application that feed a varchar date into a column that was not usable to our BI report writers. The column had 3 months letters and a space and the year which was numeric.

Analysis_Date

 

To create the sample Table:

SELECT * INTO [Table]
FROM (
SELECT N'Apr 2014' AS [Analysis_Date] UNION ALL
SELECT N'Feb 2012' AS [Analysis_Date] UNION ALL
SELECT N'Jan 2014' AS [Analysis_Date] UNION ALL
SELECT N'Jul 2014' AS [Analysis_Date] UNION ALL
SELECT N'Jun 2014' AS [Analysis_Date] UNION ALL
SELECT N'Mar 2014' AS [Analysis_Date] UNION ALL
SELECT N'May 2014' AS [Analysis_Date] UNION ALL
SELECT N'Apr 2013' AS [Analysis_Date]
) t;
SELECT [Analysis_Date]
FROM [Table]
--DROP TABLE [Table]
GO

 

First I broke the data into two columns as you can see below with a SUBSTRING.

SELECT DISTINCT
DDA_Hist.Analysis_Date ,
SUBSTRING(DDA_Hist.Analysis_Date, 5, 9) AS [Analysis Year] ,
LEFT(DDA_Hist.Analysis_Date, 4) AS [Analysis Month]
FROM Table

This produces:

Analysis_Date1

 

Then I took the two substrings and mashed them to recreate the beginning of the month datetime. The magic happens with the SUBSTRING and CHARINDEX.

‘XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC’

this string lets me count the months and get a number instead of the months first three letters.

SELECT DISTINCT
DDA_Hist.Analysis_Date ,
SUBSTRING(DDA_Hist.Analysis_Date, 5, 9) AS [Analysis Year] ,
LEFT(DDA_Hist.Analysis_Date, 4) AS [Analysis Month],
 CAST(CAST(SUBSTRING(DDA_Hist.Analysis_Date, 5, 9) AS VARCHAR(4))
 + RIGHT('0'
 + CAST(CHARINDEX(LEFT(DDA_Hist.Analysis_Date, 3),
 'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')
 / 3 AS VARCHAR(2)), 2) + RIGHT('0'
 + CAST('01' AS VARCHAR(2)),
 2) AS DATETIME) AS Analysis_Datetime_BeginMonth
FROM Table

This produces:

Analysis_Date2

Click to enlarge

Taking that same logic we can also get the end of that month datetime as well.

SELECT DISTINCT
DDA_Hist.Analysis_Date ,
SUBSTRING(DDA_Hist.Analysis_Date, 5, 9) AS [Analysis Year] ,
LEFT(DDA_Hist.Analysis_Date, 4) AS [Analysis Month],
CAST(CAST(SUBSTRING(DDA_Hist.Analysis_Date, 5, 9) AS VARCHAR(4))
+ RIGHT('0'
+ CAST(CHARINDEX(LEFT(DDA_Hist.Analysis_Date, 3),
'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')
/ 3 AS VARCHAR(2)), 2) + RIGHT('0'
+ CAST('01' AS VARCHAR(2)),
2) AS DATETIME) AS Analysis_Datetime_BeginMonth</pre>
<pre> DATEADD(DAY,
-DAY(DATEADD(MONTH, 1,
CAST(CAST(SUBSTRING(DDA_Hist.Analysis_Date,
5, 9) AS VARCHAR(4))
+ RIGHT('0'
+ CAST(CHARINDEX(LEFT(DDA_Hist.Analysis_Date,
3),
'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')
/ 3 AS VARCHAR(2)), 2) + RIGHT('0'
+ CAST('01' AS VARCHAR(2)),
2) AS DATETIME))),
DATEADD(MONTH, 1,
CAST(CAST(SUBSTRING(DDA_Hist.Analysis_Date, 5,
9) AS VARCHAR(4)) + RIGHT('0'
+ CAST(CHARINDEX(LEFT(DDA_Hist.Analysis_Date,
3),
'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')
/ 3 AS VARCHAR(2)),
2) + RIGHT('0'
+ CAST('01' AS VARCHAR(2)),
2) AS DATETIME))) AS Analysis_Datetime_MonthEnd
FROM Table

This produces:

Analysis_Date3

Click to enlarge

Have Fun!