Removing Time from datetime

Following on my trend of date manipulations, another simple but useful bit of code is the following:

select convert(datetime, convert(char, <your date field here>, 106))
from <your table here>

This will strip the time out of your date, leaving with something you can aggregate to form daily reports or whatever you may need it for

T

Simple but overlooked - Date Text

I always forget this one!

Say you have a date 01/02/2011 but you want the month returned as text for a report

Use the datename function!

Example:

SELECT DATENAME(month, ew_event_date) Mon , COUNT(*) AS Events
FROM            EW.tbl_core_inbound
GROUP BY DATENAME(month, ew_event_date)

Returns:

Mon          Events

January     1258212
February   1120195
March       876901
April         51111

Simple, but effective
T

So after a long break...

I return!

Work has been an exciting explosion of development opportunities and creativity recently which has left me little time for anything else!

Things I have been looking at:

• Microsoft - Sharepoint 2010/PerformancePoint/PowerPivot
• Engage Sciences - Social Media marketing opportunities (@EngageSciences)
• IMGroup - Surfacing BI data (@IMGROUPonline)

As well as doing a review of our systems and working with the team to develop two new ones.

The data mart is near completion, with SSRS reports running on top of them (I wish I could show you some of the stuff our team has come up with)

Most excitingly though is a project that is kicking off to bring together all of our email platforms into one central warehouse, to analyse activities, trends and behaviours of all of our customers.
This is a mamoth project that we are all excited about...

I will prepare an example tutorial for using PowerPivot in the next few days as i think it has applications in all businesses that people dont even realise is there, all using a nice friendly Excel interface.

T

SSIS - .Tar and .tgz files and you

Ive just received a load of data from a supplier that is:

• PGP encrypted
• TAR compressed
• TGZ compressed

I received over 200 of them in the format file_name.tgz.tar.pgp

As luck would have it i came across this blog post that really helped me out:

http://sqlserversolutions.blogspot.com/2008/10/zip-and-unzip-files-in-folder.html

T