Date functions in SSIS

This comes in the sequence of trying to use date functions in SSIS in relation to T-SQL.

There are some differences in using one or another that might cause some difficulties if we are not aware of them.

For example you should remember your systems default date sequence if it is day-month-year, month-day-year, year-month-day or year-day-month. This is important when using dates in calculations with another dates.

GETDATE() - returns today's date

DATEADD - adds a specific value to the referenced date

example: DATEADD(week,2,getdate()) - adds two weeks to current date


A very simple link to refer where is this one from Microsoft:

http://msdn.microsoft.com/en-us/library/aa258863(SQL.80).aspx


Obs: for a list of all SSIS available command please refer to:

http://msdn.microsoft.com/en-us/library/ms141671.aspx

SQL: List of common commands

Here is a list of basic T-SQL commands that you should know in order to work with the queries in Management Studio. You can find them in the web very quickly using a search engine, but I chose to list some here just in case and for my personal reference:

use "database_name"

execute the query on a database


Simple query example structure for basic operations:

select "values" from "table"

select row results from a table, make a query
use * to get all values

order by
order results, by default the order is of finding.
you can force desc - descending
asc - ascending

Example:
select ExtracData from stg.ExtractLog where DataSource = 'HRData'
order by 'Size'


backup database "DataBaseName"
to disk = '"path"'

generates a backup off a specific database to disk location
commonly used with “SQL Server Agent” activated


dbcc showcontig ('"TableName"')

shows information about paging tables, number off reads, accesses, etc
good for optimization analisys

with tableresults
extra paramater (optional) that shows result as a table

Database Diagrams

Aloha,

MS SQL 2005 Management Studio

When using a database that is unfamiliar to you try to create “Database Diagrams” in order to visualize the relationships between tables, as well as the tables and it's variables.

To do this, open the correspondent data base, right click on the data base name and chose the “Database Diagrams”. Right click on it and chose “New Database Diagram”. Select all the tables on your database and create a diagram, the Management Studio will automatically distribute the tables in a way that the relationships between them will appear.

Regards,

T-SQL: friday of the current week

This was a question asked to me in order to return the last day of the current week.

The main idea is to schedule a package update for Friday being the day of less Data Warehouse use.

The response is in this link, on an MSN forum, since I only changed another response:

http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/4613e458-13a1-441e-b1b5-d5bb81af6639/


The presented solution works in either T-SQL and SSIS (data converter tool) there can be other variations and there can be some that only work in one case like:

SSIS:

DATEADD("WEEK",DATEDIFF("WEEK",(DT_DATE)("01/01/1900"),GETDATE()),(DT_DATE)("05/01/1900"))

T-SQL:
select DATEADD("WEEK",DATEDIFF("WEEK",'01-01-1900',GETDATE()),'01-05-1900')


and a more complex way, by counting the week until now and then returning the last day of current week:

T-SQL & SSIS:
SELECT DATEADD(week,DATEDIFF(week,'01-01-1900',GETDATE()),'01-05-1900')

Any questions, explanations, just comment.

Change of pace for this blog...

Working with Business Intelligence I changed this blog in order to share acquired knowledge over BI areas - SQL, T-SQL, SSIS, SSRS, SSAS, MDX - and process related.

For anything comments or bugs that You might detect feel free to respond to the blog articles or directly to me.

Best regards,