update one table with data from another table

This blog post illustrates how to update more than one column in a table with values from columns in another table and explains how to do it in two RDBMS that I use.

Table Structures and values:
TableA has four columns: a, b, c, d (a is the primary key column)
TableB has five columns: a1, b1, c1, d1, e1 (a1 and b1 together constitute the primary key for this table)
The foreign key relationship between the two tables is based on A.a = B.a1
The data in these 2 tables is as follows:
I. TableA
a b c d
1 x y z
2 a b c
3 t x z

II. TableB
a1 b1 c1 d1 e1
1 x1 y1 z1 40
2 a1 b1 c1 50

The requirement is to write a SQL to update columns b, c and d in TableA from the columns b1, c1 and d1 from TableB where-ever the join condition satisfies and e1 > 40 in TABLEB.

Oracle:
UPDATE TABLEASET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)
/

Results after the update:
a b c d
————————————
1 x y z
2 a1 b1 c1
3 t x z


SQL Server:
UPDATE TABLEA
SET b = TABLEB.b1,
c = TABLEB.c1,
d = TABLEB.d1
FROM TABLEA, TABLEB
WHERE TABLEA.a = TABLEB.a1
AND TABLEB.e1 > 40
GO

Note: This is an extension in SQL Server i.e. the FROM clause – it does make it simple to understand and is a nice feature.
Results after the update:
a b c d
————————————
1 x y z
2 a1 b1 c1
3 t x z



Happy writing SQL

Alter table column

This is something so simple, yet can cause you to pull your hair out. All you want to do is alter the length of a column in an Oracle table:

An example table:
Members
MbrID number(5)
FirstName varchar2(30)
Surname varchar2(30)

Now you wish to increase the length of the surname to 50 characters, as someone has a longer than usual surname.
Here is the syntax to do this:

alter table members
modify surname varchar2(50)

This has now been updated to :
Members
MbrID char(5)
FirstName varchar2(30)
Surname varchar2(50)


When you wish to add a column:
alter table members
add DateOfBirth date

When you wish to remove a column:
alter table members
drop column DateOfBirth

When you wish to rename a column
alter table customers
rename column surname to lastname

Find and replace text in MySQL and Oracle

Today someone told me that they have a blog with over 300 posts and just changed their URL, the problem is that the new blog still had reference to their previous blog's url. And all the images, and reference were to the previous URL which had a 401 re-direct to the new blog. The images, etc were already deleted from the previous blog and imported into the new URL.

He was actually going to update every single entry. I recommended that it the blog has all posts in it's SQL database and had to find all the fields which contained the text (old url address), with the new text (new url ).

Now I will show you and easy way to update this.

Go to your database SQL screen and type in as follows

UPDATE [your_table_name] SET [your_table_field] = REPLACE([your_table_field], '[string_to_find]' , '[string_to_be_replaced]');

example
UPDATE my_posts SET post_content = REPLACE(post_content, 'www.oldurl.com' , 'www.newurl.com');

So armed with only one query per column table combination, you can easily update your blog or database with the new text.

Setting preferred domain

When search engines find your website, they may come across, http://yourdomain.com or http://www.yourdomain.com, which is read as two domains, causing duplicate listings. To fix this you have to decide which web domain will be the preferred domain.

To set your domain in google, go to the www.google.com/webmasters/tools/ website and set your preferred domain.

After you set the preferred domain, you should force everyone to use this domain with a 301 redirect. You do this by editing your root .htaccess file, if hosted on a Linux server. If you do not have a .htaccess file you will need to create one first as follows:

1) Create a text file and name it .htaccess
2) Modify it with the preferred domain code (as details below)
3) Upload it to the root of your server (public www)

If you want people to redirect from non-WWW to WWW:
(for example, if someone types in http://yourdomain.com, it will forward to http://www.yourdomain.com)

RewriteEngine On
RewriteCond %{HTTP_HOST} !^(.*)\.yourdomain\.com$ [NC]
RewriteRule ^(.*)$ http://www.yourdomain.com/$1 [R=301,L]

If you want people to redirect from WWW to non-WWW:
(for example, if someone types in http://www.yourdomain.com, it will forward to http://yourdomain.com)

RewriteEngine On
RewriteCond %{HTTP_HOST} ^www\.yourdomain\.com$ [NC]
RewriteRule ^.*$ http://yourdomain.com%{REQUEST_URI} [R=301,L]

Don't forget to replace “yourdomain” with the URL of your domain, otherwise the code above won't work!

Therefore by setting a preferred domain you are ensuring that Google and all the major search engines will only index your url web links from one domain. This will help your PageRank in search engine rankings.

So don't put it off, set your preferred domain, if you have not done so already! You will see your PageRank rise!

Combine text using Concatenation in Oracle

When wanting to combine text you can use the concatenation operator

Examples:
To place comma after every row returned use as follows:
SELECT table_name ',' FROM user_tables;

You may have a date that you wish to place in a CSV file, you can concentate as follows:
SELECT 'the date today is: ' to_char(sysdate, 'MM-DD-YYYY') ',' FROM sys.dual;

Reverse function in Oracle

The following shows how to use the reverse function to reverse a string or number in Oracle.
SQL> select reverse('string') from dual;
REVERS
------
gnirts

However you may not have the function installed and can use the following procedure:
SQL>
SQL> create or replace procedure reverse(x in varchar2) as
2 a char(1);
3 i number;
4 begin
5 for i in 1..length(x) loop
6 select substr(x,length(x)-i+1,1) into c from dual;
7 dbms_output.put(a);
8 end loop;
9 dbms_output.put_line(' ');
10 end;
11 /


Procedure created.

SQL> set serverout on
SQL> exec reverse('Oracle')
elcarO

SQL> exec reverse('string')
gnirts
PL/SQL procedure successfully completed.

Get last 2 financial years quarters

The 'solution' below is to retrieve the start and end dates of annual quarters of the last 2 years, using the @end_date as your input parameter.

You can replace the '--- YOUR QUERY USING the @qtr_start and @qtr_end parameters' with your query, with the @qtr_start and @qtr_end as your search criteria.


DECLARE
@qtr_start DATETIME,
@qtr_end DATETIME,
@end_date DATETIME

-- your date parameter
SET @end_date = '2009-06-30'

-- initialise dates
SET @qtr_start = DATEADD(YYYY, -2, DATEADD(D,1, @end_date))
SET @qtr_end = DATEADD(DD, -1,DATEADD(M,3,@qtr_start))


WHILE @qtr_end <= @end_date
BEGIN
SELECT 'Quarter: '+convert(VARCHAR, @qtr_start, 3)+'-'+convert(VARCHAR, @qtr_end, 3),
@qtr_start,
@qtr_end

--- YOUR QUERY USING the @qtr_start and @qtr_end parameters

-- get rest of the quarters until end date
SET @qtr_start = DATEADD(DD, 1,@qtr_end)
SET @qtr_end = DATEADD(DD, -1,DATEADD(M,3,@qtr_start))
END

Display html php code in blogger

When posting the PHP menu post, I found the post to distort the html and php sections and found the following to blog which assist to fix the display html in Blogger or even Wordpress issue:

display html, javascript and php code in blogger

PHP Include menu and footer

Header and Footer Inclusion

This is a beginners tutorial on how to create and include header/footer files at the top and bottom of all pages in your site. This comes in very handy, for example, when you want to update something that is the same on each page such as a menu or copyright notice. You would only need to update a single file to have the changes appear on all pages of your site.

Let's pretend you wish to create have 20 web pages for your site with the same design and you want to change a link in your menu. You would have to edit all 20 pages, right? Nope, there's a much quicker way allowing you to edit just one file for the mene or footer and really it's not as hard as it sounds.

What you have to do is create a header file and a footer file. The header file contains all the HTML code for the top portion of your site. The footer contains the HTML for the bottom. Content goes between the header and footer. Let's get started...

This is an entire HTML document to give you an idea of what the header and footer are and where the content fits in. The header is in blue, content is in red, and footer is in green.

create a file named :
---------------------------------------------------
index.html
---------------------------------------------------
<html>
<head>
<title>Title of your website</title>
</head>
<body text=black link=blue alink=red vlink=purple>

<!--- menu at the top --->
<p align=center>
<a href='index.html'>Home</a> | <a href='about.html'>About My Site</a> | <a href='links.html'>Links</a>
</p>

This is the content of a page on my site.

<p align=center>Copyright 2009. All Rights Reserved</p>

</body>
</html>


---------------------------------------------------

After determining which portions are the header and footer, you need to cut and paste them into new files, lets name them header.html and footer.html.
The index.html file you are using as an example should only contain the content after this. Here's an example of how things are divided up.

---------------------------------------------------
header.html <html>
---------------------------------------------------
<head>
<title>Title of Site</title>
</head>
<body text=black link=blue alink=red vlink=purple>

<!--- menu at the top --->
<p align=center>
<a href='index.html'>Home</a> | <a href='about.html'>About My Site</a> | <a href='links.html'>Links</a>
</p>
---------------------------------------------------


---------------------------------------------------
footer.html
---------------------------------------------------
<p align=center>Copyright 2002. All Rights Reserved</p>

</body>
</html>


---------------------------------------------------
index.html
---------------------------------------------------
This is the content of a page on my site.
---------------------------------------------------


You now have header and footer HTML code in their own files. This means we can include these two files at the top and bottom (with content between) of any number of pages on our site. This is very convenient! For example, if you wanted to change a link in your menu, you would just edit it in header.html instead of every page of your site!


Now you only want to call the index page and have the header.html and footer.html included. The way to do this is to rename the page to index.php or anything.php as and add the follows:

---------------------------------------------------
index.php or anything.php for other pages on your site
---------------------------------------------------
<? include("header.html"); ?>

This is the content of a page on my site.

<? include("footer.html"); ?>
---------------------------------------------------

Oracle SQL Usage: PARTITION BY

PARTITION BY: divide the groups into subgroups

This example will demonstrate grouping all sales by month, per product.

SQL> -- create your table
SQL> CREATE TABLE all_sales (
2 year INTEGER,
3 month INTEGER,
4 prd_type_id INTEGER,
5 emp_id INTEGER ,
6 amount NUMBER(8, 2)
7 );

Table created.

SQL> -- ad data into your table
SQL>
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,1 ,1 ,21 ,16034.84);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,2 ,1 ,21 ,15644.65);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,3 ,2 ,21 ,20167.83);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,4 ,2 ,21 ,25056.45);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,5 ,2 ,21 ,NULL);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,6 ,1 ,21 ,15564.66);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,7 ,1 ,21 ,15644.65);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,8 ,1 ,21 ,16434.82);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,9 ,1 ,21 ,19654.57);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,10 ,1 ,21 ,21764.19);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,11 ,1 ,21 ,13026.73);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,12 ,2 ,21 ,10034.64);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,1 ,2 ,22 ,16634.84);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,1 ,2 ,21 ,26034.84);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,2 ,1 ,21 ,12644.65);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,3 ,1 ,21 ,NULL);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,4 ,1 ,21 ,25026.45);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,5 ,1 ,21 ,17212.66);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,6 ,1 ,21 ,15564.26);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,7 ,2 ,21 ,62654.82);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,8 ,2 ,21 ,26434.82);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,9 ,2 ,21 ,15644.65);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,10 ,2 ,21 ,21264.19);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,11 ,1 ,21 ,13026.73);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,12 ,1 ,21 ,10032.64);

1 row created.



SQL>
SQL> -- retrieve the records, just to check the content
SQL> --of the values inserted above
SQL> select * from all_sales;

YEAR MONTH PRD_TYPE_ID EMP_ID AMOUNT
---------- ---------- ----------- ---------- ----------
2006 1 1 21 16034.84
2006 2 1 21 15644.65
2006 3 2 21 20167.83
2006 4 2 21 25056.45
2006 5 2 21
2006 6 1 21 15564.66
2006 7 1 21 15644.65
2006 8 1 21 16434.82
2006 9 1 21 19654.57
2006 10 1 21 21764.19
2006 11 1 21 13026.73
2006 12 2 21 10034.64
2005 1 2 22 16634.84
2005 1 2 21 26034.84
2005 2 1 21 12644.65
2005 3 1 21
2005 4 1 21 25026.45
2005 5 1 21 17212.66
2005 6 1 21 15564.26
2005 7 2 21 62654.82
2005 8 2 21 26434.82
2005 9 2 21 15644.65
2005 10 2 21 21264.19
2005 11 1 21 13026.73
2005 12 1 21 10032.64

25 rows selected.

SQL>
SQL> --PARTITION BY: divide the groups into subgroups.
SQL> --calculate the total sales amount per month
SQL>
SQL>
SQL> SELECT
2 prd_type_id, month, SUM(amount),
3 RANK() OVER (PARTITION BY month ORDER BY SUM(amount) DESC) AS rank
4 FROM all_sales
5 where amount IS NOT NULL
6 GROUP BY prd_type_id, month
7 ORDER BY prd_type_id, month;

PRD_TYPE_ID MONTH SUM(AMOUNT) RANK
----------- ---------- ----------- ----------
1 1 16034.84 2
1 2 28289.3 1
1 4 25026.45 2
1 5 17212.66 1
1 6 31128.92 1
1 7 15644.65 2
1 8 16434.82 2
1 9 19654.57 1
1 10 21764.19 1
1 11 26053.46 1
1 12 10032.64 2
2 1 42669.68 1
2 3 20167.83 1
2 4 25056.45 1
2 7 62654.82 1
2 8 26434.82 1
2 9 15644.65 2
2 10 21264.19 2
2 12 10034.64 1

19 rows selected.

SQL>
SQL> --drop table
SQL> drop table all_sales;

Table dropped.

SQL>
SQL>

Tech Stuff

vfaz6379kx

can you believe it : A real SQL joke

Well as developers we noted for being different, with a totally different kind of humour. and found a real SQL joke at reddit, it goes like this:

A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

There are heaps of comments follow them here.

Divide by zero error encountered

You may have a request to extract percentages and you encounter this dreaded error, when developing in SQL 2000, SQL 2005 or even SQL 2008.

The workaround for the Divide by zero error encountered. error is by using the NULLIF Function as the the example below demonstrates. The NULLIF function returns the first expression if the two expressions are not equivalent otherwise a NULL is returned.

Usage: SELECT NULLIF(col1, 0)

When col1 = 0 a NULL will be returned.

Example


CREATE TABLE #Products
(
Product VARCHAR(10),
Price DECIMAL(7, 2),
CostPrice DECIMAL(7, 2)
)


INSERT #Products SELECT 'A4 Books', 12.00, 6.00
INSERT #Products SELECT 'Pens', 0.10, 0.05
INSERT #Products SELECT 'Pencils', 0, 0.05


SELECT Product, (CostPrice / Price) * 100 AS PercentProfit FROM #Products


Will Produce the following Error:

(3 row(s) affected)

Server: Msg 8134, Level 16, State 1, Line 14
Divide by zero error encountered.

---------------------------------------------------------------------

SELECT Product, ((CostPrice/NULLIF(Price, 0))*100) AS PercentProfit
FROM #Products

Returns:

Product PercentProfit
---------- -----------------------
A4 Books 50.0000000000
Pens 50.0000000000
Pencils NULL


(3 row(s) affected)

Sequence number in MSSQL

Today someone asked me how to place a sequential number as a primary key value in a table. He did not want to write a loop in his procedure to increment the primary key value, just a 'plain and simple insert statement' with the table doing the calculations.

In Oracle, the SEQUENCE is the object place holder to use on a table trigger event.

In MSSQL, INCREMENT is the property and can be used as follows:

-------------------------------------------------------------------------
/*
To test this, first set up your table, with the IDENTITY property,
followed with two variables (starting int , increment by value),
in this example the ProductID starts at one, and every new record
will increase the Product counter ID by 1.
*/

CREATE TABLE product
(productID INT IDENTITY(1,1) ,
productDescription VARCHAR(20),
CONSTRAINT ID_PK PRIMARY KEY (productID)
)



/*
Insert some data into the table
*/
insert into product (productDescription) values ('bicycles')
insert into product (productDescription) values ('cars')
insert into product (productDescription) values ('trains')
insert into product (productDescription) values ('buses')
insert into product (productDescription) values ('trams')


/*
Select the data, to test the increment
*/

select * from product

/*
Drop test table
*/
drop table product

-------------------------------------------------------------------------

I hope this article can you, and as always I welcome feedback on this article.

SQL Query to get customers with one product

SQL Query using min and max aggregate function

You may want to find Customer that only has only bought one particular product in the Sales table
You will know the Product_ID is the in the Products table, in this example we use ProductID=10

In this example for each client you will find the lowest value Product_ID and the highest value Product_ID, then filters rows to those where Product_ID is the same as the variable.

--Written by Rishka Booran-Johnson
-- Get Customers with only one product

CREATE TABLE #Customers (
customer_ID int,
firstname varchar(30),
lastname varchar(30)
)

CREATE TABLE #Sales (
Transaction_ID int,
Customer_ID int,
Product_ID int,
Sale_date datetime
)


CREATE TABLE #Products (
Product_ID int,
Product_name varchar(30),
Product_description varchar(50)
)


INSERT #customers SELECT 1, 'john', 'smith'
INSERT #customers SELECT 2, 'sally', 'johnson'
INSERT #customers SELECT 3, 'joe', 'bloggs'



INSERT #Products SELECT 1, 'bicycles', ''
INSERT #Products SELECT 2, 'trains', ''
INSERT #Products SELECT 3, 'dolls', ''



INSERT #Sales SELECT 1, 1, 2, '2009-06-01'
INSERT #Sales SELECT 2, 1, 1, '2009-06-08'
INSERT #Sales SELECT 2, 1, 1, '2009-06-07'
INSERT #Sales SELECT 3, 2, 2, '2009-06-12'
INSERT #Sales SELECT 4, 2, 2, '2009-06-05'
INSERT #Sales SELECT 5, 3, 3, '2009-06-06'
INSERT #Sales SELECT 6, 3, 2, '2009-06-01'
INSERT #Sales SELECT 7, 3, 1, '2009-06-03'


declare
@vproduct_ID int

set @vproduct_ID = 2; -- this would be your input variable for trains


SELECT s.customer_ID, c.firstname, c.lastname, MIN(s.product_ID), MAX(s.product_ID)
FROM #sales s, #customers c
WHERE s.customer_ID = s.customer_ID
AND s.customer_ID = c.customer_ID
GROUP BY s.customer_ID, c.firstname, c.lastname
HAVING MIN(s.product_ID) = @vproduct_ID and MAX(s.product_ID) = @vproduct_ID


DROP TABLE #Customers
DROP TABLE #Sales
DROP TABLE #Products

How to add Adsense in blog

When setting up this blog, I tried to add the Adsense account gadget that Google provides, for the side bar. When selecting this I got an error of "An internal error occurred. Please try again."

What will fix this problem is :
Log into your Adsense account
Select My Account then account access.
once there you will see a section headed Hosts with access
you will see that blogger is listed but you need to click "grant access", then select it, which should change it to "disable access" !

Hope this helps those that have the same problem.

How to check database growth

Sometimes you’ll want to know how much and how fast your database has been growing. Usually you will have a document that tells you the size information on all the databases, but this is not always the case in the real world. Considering that you perform regular backups, the following script can give you a estimated idea of growth over time of your database.

select
BackupDate = convert(varchar(10),backup_start_date, 111),
SizeInGigs=floor( backup_size/1024000000)
from msdb..backupset
where
database_name = ‘DatabaseName’
and type = ‘d’
order by
backup_start_date desc

Furthermore the Backup type can be used for the following types as well , making it easier to track other types of objects:

• D = Database
• I = Differential database
• L = Log
• F = File or filegroup
• G =Differential file
• P = Partial
• Q = Differential partial

Keywords for your website

When looking to promote your site you want to increase traffic to your website, which is when you need good SEO (Search Engine Optimization) and is the latest buzz word around.

One of the key factors in SEO are good keywords, a keyword is what a users use to conduct a search in a search engine, such as Google, Yahoo, MSN, etc. A keyword can consist of a single word, such as “cars” or an entire phrase, like “cars for sale” or even “cars for sale in Australia”. Over time you will find out the common keyword selections website users use to find your site and you can use these keywords to try to rank your website highly in search engine results pages.

A keyword tool I use is : https://adwords.google.com/select/KeywordToolExternal

RRN function in DB2

While doing some ETL I have just learned a new command in DB2 called RRN, which stands for relative record number. It has probably been used a long time, but had the rownum familiarity from Oracle.

Here is an example:
Return the relative record number and employee name from table EMPLOYEE for those employees in department 10.

SELECT RRN(EMPLOYEE), LASTNAME
FROM EMPLOYEE
WHERE DEPTNO = 10

Formatted date in SQL Server 2008

Yesterday I found an interesting article about Formating a date in SQL Server 2008 in the http://www.databasejournal.com website.

There are quite a few occasions when a developer wants to extract a date in a specific format extracted.

Having used Oracle before I would use somthing like:

select to_char(sysdate,'MM/DD/YYYY') from dual; or

select TRUNC(DateTimeField) to get the date only.

There is no equivalent in MS SQL Server, and you always have to use CAST or CONVERT, however using the SQL script developed by the author in this article, any MS SQL developer can write one common function and write a simple command :

select dbo.format_date(GETDATE(),'YYYY-MM-DD') as Date, to retrieve a date, the SQL code for the format_date as follows below, if you cannot find the web page.

Have fun and read the comments below the article when you are using SQL 2000 and SQL 2005.



/*---------------------------------------------------------------------------------------------
SQL FORMAT DATE code
----------------------------------------------------------------------------------------------*/

/****** Object:  UserDefinedFunction [dbo].[format_date]    Script Date: 05/12/2009 23:19:35 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[format_date]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[format_date]
GO

/****** Object:  UserDefinedFunction [dbo].[format_date]    Script Date: 05/12/2009 23:19:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET CONCAT_NULL_YIELDS_NULL OFF
go
CREATE function [dbo].[format_date] (@inputdate datetime ,@format varchar(500))
returns varchar(500)
as
begin
declare @year varchar(4)    --YYYY
declare @shortyear varchar(4)   --Yr
declare @quarter varchar(4)    --QQ
declare @month varchar(2)    --MM
declare @week varchar(2)    --WW
declare @day varchar(2)     --DD
declare @24hours varchar(2)    --24HH
declare @12hours varchar(2)    --HH
declare @minutes varchar(2)    --MI
declare @seconds varchar(2)    --SS
declare @milliseconds varchar(3)  --MS
declare @microseconds varchar(6)  --MCS
declare @nanoseconds varchar(9)   --NS
declare @dayname varchar(15)   --DAY
declare @monthname varchar(15)   --MONTH
declare @shortmonthname varchar(15)     --MON
declare @AMPM  varchar(15)   --AMPM
declare @TZ  varchar(15)       --TZ
declare @UNIXPOSIX  varchar(15)  --UNIXPOSIX
          --UCASE
          --LCASE

declare @formatteddate varchar(500)  

--Assign current date and time to
if (@inputdate is NULL or @inputdate ='')
begin
set @inputdate = getdate()
end

if (@format is NULL or @format ='')
begin
set @format ='YYYY-MM-DD 12HH:MI:SS AMPM'
end

--set all values

set @year    = convert(varchar(4),year(@inputdate))
set @shortyear    = right(@year,2)
set @quarter   = convert(varchar(1),datepart(QQ,(@inputdate)))
set @month    = right('0'+convert(varchar(2),month(@inputdate)),2)
set @week    = right('0'+convert(varchar(2),datepart(ww,(@inputdate))),2)
set @day    = right('0'+convert(varchar(2),day(@inputdate)),2)
set @24hours      = right('0'+convert(varchar(2),datepart(hh,@inputdate)),2)
set @TZ           = convert(varchar(10),datename(TZ,convert(varchar(20),@inputdate)))
set @UNIXPOSIX    = convert(varchar(15),datediff(ss,convert(datetime,'01/01/1970 00:00:000'),@inputdate))

if datepart(hh,@inputdate) >12
begin
set @12hours      = right('0'+convert(varchar(2),datepart(hh,@inputdate)) -12,2)
end
else
begin
set @12hours      = right('0'+convert(varchar(2),datepart(hh,@inputdate)) ,2)
end

if datepart(hh,@inputdate) >11
begin
set @AMPM ='PM'
end
else
begin
set @AMPM ='AM'
end

set @minutes      = right('0'+convert(varchar(2),datepart(n,@inputdate)),2)
set @seconds      = right('0'+convert(varchar(2),datepart(ss,@inputdate)),2)
set @milliseconds = convert(varchar(3),datepart(ms,@inputdate))
set @microseconds = convert(varchar(6),datepart(mcs,@inputdate))
set @nanoseconds  = convert(varchar(9),datepart(ns,@inputdate))
set @dayname      = datename(weekday,@inputdate)
set @monthname    = datename(mm,@inputdate)
set @shortmonthname= left(datename(mm,@inputdate),3)
set @formatteddate = @format
set @formatteddate=replace(@formatteddate,'MONTH',@monthname)
set @formatteddate=replace(@formatteddate,'MON',@shortmonthname)
set @formatteddate=replace(@formatteddate,'AMPM',@AMPM)

set @formatteddate=replace(@formatteddate,'YYYY',@year)
set @formatteddate=replace(@formatteddate,'Yr',@shortyear)
set @formatteddate=replace(@formatteddate,'QQ',@quarter)
set @formatteddate=replace(@formatteddate,'WW',@week)
set @formatteddate=replace(@formatteddate,'MM',@month)
set @formatteddate=replace(@formatteddate,'DD',@Day)
set @formatteddate=replace(@formatteddate,'24HH',@24hours)
set @formatteddate=replace(@formatteddate,'12HH',@12hours)
set @formatteddate=replace(@formatteddate,'Mi',@minutes)
set @formatteddate=replace(@formatteddate,'SS',@seconds)
set @formatteddate=replace(@formatteddate,'MS',@milliseconds)
set @formatteddate=replace(@formatteddate,'MCS',@microseconds)
set @formatteddate=replace(@formatteddate,'NS',@nanoseconds)
set @formatteddate=replace(@formatteddate,'DAY',@dayname)
set @formatteddate=replace(@formatteddate,'TZ',@TZ)
set @formatteddate=replace(@formatteddate,'UNIXPOSIX',@UNIXPOSIX)

if charindex('ucase',@formatteddate)<>0
begin
set @formatteddate=replace(@formatteddate,'ucase','')
set @formatteddate=upper(@formatteddate)
end

if charindex('lcase',@formatteddate)<>0
begin
set @formatteddate=replace(@formatteddate,'lcase','')
set @formatteddate=lower(@formatteddate)
end


return @formatteddate
end


GO

SQL.. the beginning.

The first time I ever saw a database was in Paradox (database), in 1995. It was a huge learning curve, but I quickly learned what tables, columns and rows were. Then I learned how to build relationships and write SQL queries. (SQL is short for Structured Query Language)

This is how I then figured it was put together.

Every database has collection of one or more tables.
Tables are made up of rows and columns.
The rows contain the data and columns describe the format of the data, namely data type, for that column.

Imagine a telephone directory, information of a person’s first name, surname , address and ph number, this is how the basic table would be constructed:

Table: Phone_Directory

FirstName	LastName	Address	Phone
Jake	Black	123 Round Road, CA	123 321-1234
Sonia	Coke	19 Sailfish Road, WA	232 555-5555
Mel	Brown	32 71st Ave, NY	614 555-5432