Category Archives: Databases

Databases SQL Server Tips & tricks Transact-SQL

How to get different SQL Server date formats

Below is a list of formats and an example of the output.  The date used for all of these examples is “2006-12-30 00:38:54.840”.

DATE ONLY FORMATS
Format # Query Sample
1 select convert(varchar, getdate(), 1) 12/30/06
2 select convert(varchar, getdate(), 2) 06.12.30
3 select convert(varchar, getdate(), 3) 30/12/06
4 select convert(varchar, getdate(), 4) 30.12.06
5 select convert(varchar, getdate(), 5) 30-12-06
6 select convert(varchar, getdate(), 6) 30 Dec 06
7 select convert(varchar, getdate(), 7) Dec 30, 06
10 select convert(varchar, getdate(), 10) 12-30-06
11 select convert(varchar, getdate(), 11) 06/12/30
12 select convert(varchar, getdate(), 12) 061230
23 select convert(varchar, getdate(), 23) 2006-12-30
101 select convert(varchar, getdate(), 101) 12/30/2006
102 select convert(varchar, getdate(), 102) 2006.12.30
103 select convert(varchar, getdate(), 103) 30/12/2006
104 select convert(varchar, getdate(), 104) 30.12.2006
105 select convert(varchar, getdate(), 105) 30-12-2006
106 select convert(varchar, getdate(), 106) 30 Dec 2006
107 select convert(varchar, getdate(), 107) Dec 30, 2006
110 select convert(varchar, getdate(), 110) 12-30-2006
111 select convert(varchar, getdate(), 111) 2006/12/30
112 select convert(varchar, getdate(), 112) 20061230
TIME ONLY FORMATS
8 select convert(varchar, getdate(), 8) 00:38:54
14 select convert(varchar, getdate(), 14) 00:38:54:840
24 select convert(varchar, getdate(), 24) 00:38:54
108 select convert(varchar, getdate(), 108) 00:38:54
114 select convert(varchar, getdate(), 114) 00:38:54:840
DATE & TIME FORMATS
0 select convert(varchar, getdate(), 0) Dec 12 2006 12:38AM
9 select convert(varchar, getdate(), 9) Dec 30 2006 12:38:54:840AM
13 select convert(varchar, getdate(), 13) 30 Dec 2006 00:38:54:840AM
20 select convert(varchar, getdate(), 20) 2006-12-30 00:38:54
21 select convert(varchar, getdate(), 21) 2006-12-30 00:38:54.840
22 select convert(varchar, getdate(), 22) 12/30/06 12:38:54 AM
25 select convert(varchar, getdate(), 25) 2006-12-30 00:38:54.840
100 select convert(varchar, getdate(), 100) Dec 30 2006 12:38AM
109 select convert(varchar, getdate(), 109) Dec 30 2006 12:38:54:840AM
113 select convert(varchar, getdate(), 113) 30 Dec 2006 00:38:54:840
120 select convert(varchar, getdate(), 120) 2006-12-30 00:38:54
121 select convert(varchar, getdate(), 121) 2006-12-30 00:38:54.840
126 select convert(varchar, getdate(), 126) 2006-12-30T00:38:54.840
127 select convert(varchar, getdate(), 127) 2006-12-30T00:38:54.840
FORMATS WITH ISSUES
130 select convert(varchar, getdate(), 130) 10 ?? ????? 1427 12:38:54:840A
131 select convert(varchar, getdate(), 131) 10/12/1427 12:38:54:840AM

You can also format the date or time without dividing characters, as well as concatenate the date and time string:

Sample statement Output
select replace(convert(varchar, getdate(),101),’/’,”) 12302006
select replace(convert(varchar, getdate(),101),’/’,”) + replace(convert(varchar, getdate(),108),’:’,”) 12302006004426

read more »

Code Snippets MySQL SQL

Mysql Proper Case

    DROP FUNCTION IF EXISTS proper;
    SET GLOBAL  log_bin_trust_function_creators=TRUE;
    DELIMITER |
    CREATE FUNCTION proper( str VARCHAR(128) )
    RETURNS VARCHAR(128)
    BEGIN
      DECLARE c CHAR(1);
      DECLARE s VARCHAR(128);
      DECLARE i INT DEFAULT 1;
      DECLARE bool INT DEFAULT 1;
      DECLARE punct CHAR(17) DEFAULT ' ()[]{},.-_!@;:?/';
      SET s = LCASE( str );
      WHILE i < LENGTH( str ) DO 
        BEGIN
          SET c = SUBSTRING( s, i, 1 );
          IF LOCATE( c, punct ) > 0 THEN
            SET bool = 1;
          ELSEIF bool=1 THEN 
            BEGIN
              IF c >= 'a' AND c <= 'z' THEN 
                BEGIN
                  SET s = CONCAT(LEFT(s,i-1),UCASE(c),SUBSTRING(s,i+1));
                  SET bool = 0;
                END;
              ELSEIF c >= '0' AND c <= '9' THEN
                SET bool = 0;
              END IF;
            END;
          END IF;
          SET i = i+1;
        END;
      END WHILE;
      RETURN s;
    END;
    |
    DELIMITER ;
Databases

DBeaver is free and open source (GPL) universal database tool

DBeaver is free and open source (GPL) universal database tool for developers and database administrators.

  • Usability is the main goal of this project, program UI is carefully designed and implemented.
  • It is freeware.
  • It is multiplatform.
  • It is based on opensource framework and allows writing of various extensions (plugins).
  • It supports any database having a JDBC driver.
  • It may handle any external datasource which may or may not have a JDBC driver.
  • There is a set of plugins for certain databases (MySQL and Oracle in version 1.x) and different database management utilities (e.g. ERD)

http://dbeaver.jkiss.org/about/ read more »

MySQL

MySql Group_Concat

The result of GROUP_CONCAT is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024.

This will cause  data-destroying bugs in production. For this reason you should probably not use GROUP_CONCAT . At least you must set the value of group_concat_max_len to an insanely high value on every database server your application runs on.

Like MAX or COUNT, GROUP_CONCAT is a MySQL aggregation function you can use whenever your query contains a GROUP BY. You can use it to retrieve a comma-separated list of all values in a given column within each group.

select  client
,Month
,year
,Sum(Amount) as Amount_Total
,GROUP_CONCAT(Amount) as Amount_Detail
,GROUP_CONCAT(InvoiceNum) as Invoices
from mytable
 group by Client
Backup MySQL Security SQL Tips & tricks Tutorials web services Windows server

Schedule Mysql Backups to Amazon S3 in Windows server 2008 R2

1 – Access Amazon Services, S3
2 – Create a New Bucket if there’s no one.
3 – Create credentials in  IAM Amazon Services
4 – Download the tool s3.exe for windows, from s3.codeplex.com

read more »

Code Snippets Databases MySQL SQL

mysql ‘Proper case’ formating a column?

How to do something like:

Proper(“GALHANO.COM”) = “Galhano.com”

there’s no builtin function to do this but we combine CONCAT and SUBSTRING:

CONCAT(UCASE(SUBSTRING(`fieldName`, 1, 1)),LOWER(SUBSTRING(`fieldName`, 2)))
Databases MariaDB

MariaDB, SQL server

MariaDB strives to be the logical choice for database professionals looking for a robust, scalable, and reliable SQL server. To accomplish this, Monty Program works to hire the best and brightest developers in the industry, work closely and cooperatively with the larger community of users and developers in the true spirit of Free and open source software, and release software in a manner that balances predictability with reliability. Much more information is available on the Monty Program website, and we heartily encourage you to spend some time reading before installing or deploying MariaDB.

Here are some important resources to get you started. All links will redirect you to external sites, noted in parentheses.

http://mariadb.org

MySQL

MySQL, CREATE FUNCTION Syntax

The general syntax of Creating a Function is :

 CREATE FUNCTION func_name ([func_parameter[,...]]) RETURNS type routine_body

func_name : Function name
func_parameter : param_name type
type : Any valid MySQL datatype
routine_body : Valid SQL procedure statement

 The RETURN clause is mandatory for FUNCTION . It used to indicate the return type of function.

Now we are describing you a simple example a function. This function take a parameter and it is used to perform an operation by using an SQL function and return the result. In this example there is no need to use delimiter because it contains no internal ; statement delimiters. Example :

CREATE FUNCTION func(str CHAR(20))
RETURNS CHAR(50)
RETURN CONCAT('WELCOME TO, ',str,'!');

Query OK, 0 rows affected (0.00 sec)

 SELECT func('Galhano.com');

 

+------------------------+
| func('Galhano.com')      |
+------------------------+
| WELCOME TO, Galhano.com |
+------------------------+
1 row in set (0.00 sec)

http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

http://www.roseindia.net/mysql/mysql5/stored-procedures-and-functions.shtml

Informix Tips & tricks

Error: Unable to load DLL (iclit09b.dll)

Problem:

An unhandled exception of type ‘System.DllNotFoundException’ occurred in ibm.data.informix.dll

Additional information: Unable to load DLL (iclit09b.dll).
The type initializer for \”IBM.Data.Informix.IfxConnection\” threw an exception.

The system Operating is Windows 2003 Server with Share point and
the server informix is INFORMIX-SQL Version 7.30.HC4

Solution:

If you are trying to use IBM.Data.Informix (Client 2.81 TC3, 2.9, 3.5, etc.) from IIS, and you still have this problem, I have a solution. I found that IIS 5.1 has a bug!!. IIS cannot read the entire PATH environment variable if it’s too long (my PATH has 1364 characters). So if your informix dir is near the end of your PATH and your PATH is too long, IIS will never read your informix dir (“C:\Program Files\IBM\Informix\Client-SDK\bin”).

Just put your informix dir at the beginning of your PATH variable, and very important execute an “iisreset” and the issue will gone away. I don’t know if this bug remains in IIS 6.0 or later. But you can try this.

If I’m correct this will help to solve other problems when you’re trying to access unamanaged dlls from IIS:
“Unable to load DLL ‘foo.dll’: The specified module could not be found.”

Source link

MySQL Open Source SQL Utils

SQLyog – GUI Tool for MySQL

  • The leading Free and Open Source MySQL GUI Tools for the last 5 years
  • Uses Native C MySQL Client – the fastest way to access MySQL

Enterprise Version vs Community version

Homepage: http://www.webyog.com