Common date manipulation functions for SQL Server

SQL server date manipulation functions

The common data functions used in Business Objects objects as date conditions for SQL server data base:

Current Date: 

  convert(char(10), getdate(), 111)

Yesterday:

  DATEADD(D,-1,GETDATE())

Last week:

  convert(char(10), dateadd(day, -7, getdate()), 111)

Last 2 week:

  convert(char(10), dateadd(day, -14, getdate()), 111)

Last Month Start:

  convert(char(10), dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)

Last Month End:

  convert(char(10), dateadd(dd, -(day(getdate() )), getdate() ), 111)

Quarter To Date Start Date:

  case month(getdate() )
when 1 then
convert(char(10), dateadd(mm, -3, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 2 then
convert(char(10), dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 3 then
convert(char(10), dateadd(mm, -2, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 4 then
convert(char(10), dateadd(mm, -3, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 5 then
convert(char(10), dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 6 then
convert(char(10), dateadd(mm, -2, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 7 then
convert(char(10), dateadd(mm, -3, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 8 then
convert(char(10), dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 9 then
convert(char(10), dateadd(mm, -2, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 10 then
convert(char(10), dateadd(mm, -3, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 11 then
convert(char(10), dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 12 then
convert(char(10), dateadd(mm, -2, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
end

First of Current  Month:

  convert(char(10), dateadd(dd, -(day(getdate() ))+1, getdate() ), 111)

Current Year:

  case month(getdate())
when 1 then
year(dateadd(yy, -1, getdate()) )
else
year( getdate())
end

Quarter To Date END DATE:

  convert(char(10), dateadd(dd, -(day(getdate() )), getdate() ), 111)

Previous Quarter To Date Start Date:

case month(getdate() )
when 1 then
convert(char(10), dateadd(yy,-1,dateadd(mm, -3, dateadd(dd, -(day(getdate() )-1), getdate() ) )), 111)
when 2 then
convert(char(10), dateadd(yy,-1,dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) )), 111)
when 3 then
convert(char(10), dateadd(yy,-1,dateadd(mm, -2, dateadd(dd, -(day(getdate() )-1), getdate() ) )), 111)
when 4 then
convert(char(10), dateadd(yy,-1,dateadd(mm, -3, dateadd(dd, -(day(getdate() )-1), getdate() ) )), 111)
when 5 then
convert(char(10), dateadd(yy,-1,dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) )), 111)
when 6 then
convert(char(10), dateadd(yy,-1,dateadd(mm, -2, dateadd(dd, -(day(getdate() )-1), getdate() ) )), 111)
when 7 then
convert(char(10), dateadd(yy,-1,dateadd(mm, -3, dateadd(dd, -(day(getdate() )-1), getdate() ) )), 111)
when 8 then
convert(char(10), dateadd(yy,-1,dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) )), 111)
when 9 then
convert(char(10), dateadd(yy,-1,dateadd(mm, -2, dateadd(dd, -(day(getdate() )-1), getdate() ) )), 111)
when 10 then
convert(char(10), dateadd(yy,-1,dateadd(mm, -3, dateadd(dd, -(day(getdate() )-1), getdate() ) )), 111)
when 11 then
convert(char(10), dateadd(yy,-1,dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) )), 111)
when 12 then
convert(char(10), dateadd(yy,-1,dateadd(mm, -2, dateadd(dd, -(day(getdate() )-1), getdate() ) )), 111)
end

Previous Quarter To Date End date:

  convert(char(10), dateadd(yy,-1,dateadd(dd, -(day(getdate() )), getdate() )), 111)

Year To Date START DATE:

  case month(getdate())
when 1 then
convert(char(4),dateadd(yy, -1, getdate()), 111) + '/01/01'
else
convert(char(4),datepart(yyyy, getdate()), 111) + '/01/01'
end

Last 12 Months Start date:

  convert(char(10), dateadd(mm, -12,dateadd(dd, -(day(getdate() )), getdate() )), 111)

Last 12 Months End date:

  convert(char(10), dateadd(yy, -1, dateadd(mm, 0, dateadd(dd, -(day(getdate() )-1), getdate() ) ) ), 111)

Year To Date Start Date:

  convert(char(4),datepart(yyyy, getdate()), 111) + '/01/01'

Current Quarter Start and End Dates:

 
select convert(char(10), dateadd(dd, -(day(getdate() ))+1, getdate() ), 111) , convert(char(10),dateadd(dd,-1, dateadd(mm, +3, dateadd(dd, -(day(getdate() )-1), getdate() ) )),111)

Quarter To Date Start Date Adhoc:

  case month(getdate() )
when 1 then
convert(char(10), dateadd(dd, -(day(getdate() ))+1, getdate() ), 111)
when 2 then
convert(char(10), dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 3 then
convert(char(10), dateadd(mm, -2, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 4 then
convert(char(10), dateadd(dd, -(day(getdate() ))+1, getdate() ), 111)
when 5 then
convert(char(10), dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 6 then
convert(char(10), dateadd(mm, -2, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 7 then
convert(char(10), dateadd(dd, -(day(getdate() ))+1, getdate() ), 111)
when 8 then
convert(char(10), dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 9 then
convert(char(10), dateadd(mm, -2, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 10 then
convert(char(10), dateadd(dd, -(day(getdate() ))+1, getdate() ), 111)
when 11 then
convert(char(10), dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 12 then
convert(char(10), dateadd(mm, -2, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
end



See also this link for PL/SQL procedure for truncating tables:
http://dwbitechguru.blogspot.ca/2014/11/sql-server-function-pl-sql-procedure-to.html

Troubleshooting common hadoop Scoop issues on Cloudera implementation.

 Troubleshooting common hadoop Scoop issues on Cloudera implementation.

Issue 1: Sqoop does not start. Below is the error message:

[hdfs@localhost ~]$ sqoop help
Warning: /opt/cloudera/parcels/CDH-5.0.2-1.cdh5.0.2.p0.13/bin/../lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
/opt/cloudera/parcels/CDH-5.0.2-1.cdh5.0.2.p0.13/bin/../lib/sqoop/bin/sqoop: line 101: /usr/lib/hadoop/bin/hadoop: No such file or directory
/opt/cloudera/parcels/CDH-5.0.2-1.cdh5.0.2.p0.13/bin/../lib/sqoop/bin/sqoop: line 101: exec: /usr/lib/hadoop/bin/hadoop: cannot execute: No such file or directory


Resolution:
a) Set the HADOOP_HOME variable to /opt/cloudera/parcels/CDH/lib/hadoop. you can add a line HADOO_HOME=/opt/cloudera/parcels/CDH/lib/hadoop to your .profile file and that should take care of the path.

export HADOOP_HOME=/opt/cloudera/parcels/CDH/lib/hadoop

b) Other option that I haven't tried is in https://groups.google.com/a/cloudera.org/forum/#!msg/cdh-user/UcGsb_8ALJM/vxWWcwufJ-UJ


Issue 2: Scoop Import command did not work- error could not find the netezza or mysql driver:

Resolution: This is probably sqoop bug that’s fixed in CDH 5.1, The error should be fixed after the upgrade.


In the  sqoop-conf/sqoop-env.sh check the hadoop classpath is configured:
export HADOOP_CLASSPATH=/usr/lib/sqoop/lib/nzjdbc.jar:$HADOOP_CLASSPATH:
and the  jar files are present in  /usr/lib/sqoop/lib/

Example PL-SQL Procedure to truncate tables in SQL server using cursors

Example PL-SQL Procedure to truncate tables in SQL server using Cursors

/* The below procedure truncates all the tables that end with _CDW using cursor function */

DECLARE @name VARCHAR(256) -- table name
DECLARE @fileName VARCHAR(256)


DECLARE db_cursor CURSOR FOR
SELECT 'TRUNCATE TABLE '+TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%_CDW'


OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name 
WHILE @@FETCH_STATUS = 0 
BEGIN 
SET @fileName =  @name
exec (@fileName)
FETCH NEXT FROM db_cursor INTO @name 
END 

CLOSE db_cursor 
DEALLOCATE db_cursor

Check out SQL server date manipulation functions:
http://dwbitechguru.blogspot.ca/2014/11/sql-server-important-date-manipulation.html

Troubleshooting amazon redshift connections issues:

Troubleshooting amazon redshift connections issues:

a) Could not connect or resolve host name

Resolution/Cause: This means the cluster is not up or your cluster address is wrong. Check the obbc/jdbc setting. Bring the cluster up.

b) ][ODBC PostgreSQL Wire Protocol driver]Seeding the PRNG failed, most likely because the system does not have /dev/random. Connection String.

][Database Classes][Error] Failed to connect to data source. Error message: Connect failed.

Cause: cluster down for maintenance or is not available.

c) [ODBC PostgresSQL Wire protocol driver] FATAL: no pg_hba.conf entry for host "10.10.140.1". user "something", database "something", SSL off

Resolution: Have the SSL mode On i.e. in Allow mode. This is a client side setting. If you are using AQT, Workbench, or any client to connect, you need to have SSL mode on if you are getting the above error.

d) [ODBC PostgreSQL Wire Protocol driver]SSL I/O Error.

Resolution: This could be caused by the cluster not being up, or the odbc not being configured properly or the connection being dropped from huge volume of data. Most likely cause being the connection being dropped because the odbc driver is trying to read large chuncks of data.

Amazon Redshift - Top concerns and issues

 Amazon Redshift - Top concerns and issues

 

Amazon redshift cloud data warehouse is going to change the data warehousing landscape sooner or later. With more and more companies wanting to expand their data warehousing capabilities and wanting high powered devices that can churn huge loads, Amazon redshift is a very attractive proposition. It offers a peta byte scale data warehouse appliance which is very cheap and payable on demand on hourly basis with zero or no administration or maintenance required and over very high performance. All we have to do find a secure reliable way to upload data to the amazon redshift cloud which is not hard by the way, and then go crazy doing any kind of data processing. It is a great solution for sure, but before you jump into the bandwagon, a few concerns to be noted. The amazon redshift top concerns are listed below:

1) How much data do you have to upload and download and at what frequency ? - If you are uploading gigabytes of data to amazon s3 and then loading to amazon redshift, there is some amount of time required to upload the data and which also consumes huge bandwidth. Again not a problem if you are doing once a week or month. If doing daily or very constantly then yes this will add latency and network cost.  

2) Integration with ETL/BI tools: If you are reading/writing huge volume data  for ETL/BI processing directly over odbc/jdbc then most likely this will be challenging since the network is going to slow it down and there are not many odbc drivers (atleast I havent come across so far) that can do bulk loads directly to redshift. Odbc drivers  do not compress data and if you are reading millions of records then it is going to run out of buffer or the connection will fail at some point. The recommended solution for huge volumes is to upload/download data as flat files through s3 to amazon redshift.



3) Security clearance - Some companies like insurance/banks have policies over not allowing data to be uploaded to cloud. If you can get all the security clearance then it is possible to upload data to cloud in a secure and encrypted way and keep it encrypted on the cluster in such a way that even amazon cannot see it. In any case, there is some hurdles to be crossed before companies get all the internal clearance to upload data to the amazon redshift cloud. 

3) Special characters- Redshift stores data in UTF-8 characters sets. It can copy data from files in UTF-8, UTF16 formar but it converts them all into UTF-8 character set. That means some characters like those french characters with accents etc will not get loaded properly. The solution redshift is offering is to replace all of them with a single utf-8 character while copying. If any one has figured out a way to load french characters to amazon redshift, please message me. At this moment this is a big concern for me.

Also, check out:
http://dwbitechguru.blogspot.ca/2014/11/performance-tuning-in-amazon-redshift.html

Spector T230- Bluetooth Wireless Speaker - Review

 Spector T230- Bluetooth Wireless Speaker - Review

If you wondering what is the best gift you can give for christmas or as a corporate gift and you love music, then this product might be a great choice. I got this as a gift from my company and I totally love it. What is that is so special about this speaker? It does great stuff for a small palm size speaker. I'm sure few years ago to get the volume and clarity that this bluetooth speaker provides, we would have to go with an expensive speaker and which is few times bigger in size.





The few things that I like about this bluetooth speaker:
a) Very small in size and can be carried even in a pocket.
b) Looks very cute and stylish.
c) It surprises every one with the volume and clarity of sound. The clarity and volumes is great even few meters away from the speakers. You can hide the speaker in your pocket and make people wonder where is the sound coming from :). Which makes it ideal for taking it to picnic spots or wherever you want in your house without connecting wires to the source of music or to power slots.
d) Connects with both my android and blackberry phone through Bluetooth. Turn the switch in the speaker to Bluetooth mode and then find the speaker on your phone through Bluetooth and connect. As  simple as that. The phone does not have to be very close.
e) It runs for a long time and does not need charging every hour. I runs it many times before taking it for charging. It has USB charging.

Lastly, do not take it to any relative or friends house who might like it so much and ask you to give it to them. I have some people like that in my family, so I don't take it along with me when I visit them lol :)

The manual for spector T230 can be found here:
http://www.spectorandco.ca/en/products/files/manuals/T230%20-%20T245%20User%20Manual.pdf

More details about the product spector T230 can be found here:
http://www.spectorandco.ca/sp/product.php?item_num=T230