tag:blogger.com,1999:blog-53953761662574944532024-03-13T20:19:31.866-07:00CyberpreneurshipPrilihttp://www.blogger.com/profile/10783694458472515220noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-5395376166257494453.post-43244903027476048412009-01-13T05:23:00.000-08:002009-01-13T05:24:25.811-08:00Joining data and differences of using UNION and UNION ALL in SQL Server<p><u><b>Problem<br /></b></u>Sometimes there is a need to combine data from multiple tables or views into one comprehensive dataset. This may be for like tables within the same database or maybe there is a need to combine like data across databases or even across servers. I have read about the UNION and UNION ALL commands, but how do these work and how do they differ?</p> <p><u><b>Solution<br /></b></u>In SQL Server you have the ability to combine multiple datasets into one comprehensive dataset by using the UNION or UNION ALL operators. There is a big difference in how these work as well as the final result set that is returned, but basically these commands join multiple datasets that have similar structures into one combined dataset.</p> <p>Here is a brief description:</p> <ul><li><u>UNION</u> - this command will allow you to join multiple datasets into one dataset and will remove any duplicates that exist. Basically it is performing a DISTINCT operation across all columns in the result set. </li><li><u>UNION ALL</u> - this command again allows you to join multiple datasets into one dataset, but it does not remove any duplicate rows. Because this does not remove duplicate rows this process is faster, but if you don't want duplicate records you will need to use the UNION operator instead.</li></ul> <p>Rules to union data:</p> <ul><li>Each query must have the same number of columns </li><li>Each column must have compatible data types </li><li>Column names for the final result set are taken from the first query </li><li>ORDER BY and COMPUTE clauses can only be issued for the overall result set and not within each individual result set </li><li>GROUP BY and HAVING clauses can only be issued for each individual result set and not for the overall result set</li></ul> <div align="center"> <table width="80%" border="1" cellpadding="4" cellspacing="0"> <tbody> <tr> <td><b><span style="font-family:Arial;font-size:85%;">Tip</span></b> <p><span style="font-family:Arial;font-size:85%;">If you don't have the exact same columns in all queries use a default value or a NULL value such as:</span></p> <p><span style="font-family:Courier New;font-size:85%;color:#0000ff;">SELECT firstName, lastName, company FROM businessContacts<br />UNION ALL<br />SELECT firstName, lastName, NULL FROM nonBusinessContacts</span></p> <p><span style="font-family:Courier New;font-size:85%;">or</span></p> <p><span style="font-family:Courier New;font-size:85%;color:#0000ff;">SELECT firstName, lastName, createDate FROM businessContacts<br />UNION ALL<br />SELECT firstName, lastName, getdate() FROM nonBusinessContacts</span></p></td></tr></tbody></table></div> <p>Examples:</p> <p>Let's take a look at a few simple examples of how these commands work and how they differ. As you will see the final resultsets will differ, but there is some interesting info on how SQL Server actually completes the process.</p> <p>In this first example we are using the UNION ALL operator against the Employee table from the AdventureWorks database. This is probably not something you would do, but this helps illustrate the differences of these two operators. </p> <p>There are 290 rows in table dbo.Employee.</p> <table width="100%" border="1" cellpadding="4" cellspacing="0"> <tbody> <tr> <td><span style="font-family:Courier New;font-size:85%;color:#0000ff;">SELECT * FROM dbo.Employee<br />UNION ALL<br />SELECT * FROM dbo.Employee<br />UNION ALL<br />SELECT * FROM dbo.Employee</span></td></tr></tbody></table> <p>When this query is run the result set has 870 rows. This is the 290 rows returned 3 times. The data is just put together one dataset on top of the other dataset.</p> <p>Here is the execution plan for this query. We can see that the table was queried 3 times and SQL Server did a Concatenation step to concatenate all of the data.</p> <p><img src="http://www.mssqltips.com/tipimages/1387_u1.jpg" border="1" /></p> <hr /> <p>In this next example we are using the UNION operator against the Employee table again from the AdventureWorks database. </p> <table width="100%" border="1" cellpadding="4" cellspacing="0"> <tbody> <tr> <td><span style="font-family:Courier New;font-size:85%;color:#0000ff;">SELECT * FROM dbo.Employee<br />UNION<br />SELECT * FROM dbo.Employee<br />UNION<br />SELECT * FROM dbo.Employee</span></td></tr></tbody></table> <p>When this query is run the result set has 290 rows. Even though we combined the data three times the UNION operator removed the duplicate records and therefore returns just the 290 unique rows.</p> <p>Here is the execution plan for this query. We can see that SQL Server first queried 2 of the tables, then did a Merge Join operation to combine the first two tables and then it did another Merge Join along with querying the third table in the query. So we can see there was much more worked that had to be performed to get this result set compared to the UNION ALL.</p> <p><img src="http://www.mssqltips.com/tipimages/1387_u2.jpg" border="1" /></p> <hr /> <p>If we take this a step further and do a SORT of the data using the Clustered Index column we get these execution plans. From this we can see that the execution plan that SQL Server is using is identical for each of these operations even though the final result sets will still contain 870 rows for the UNION ALL and 290 rows for the UNION.</p> <p><u>UNION ALL query</u></p> <p><img src="http://www.mssqltips.com/tipimages/1387_u3.jpg" border="1" /></p> <p><u>UNION query</u></p> <p><img src="http://www.mssqltips.com/tipimages/1387_u4.jpg" border="1" /></p> <hr /> <p>Here is another example doing the same thing, but this time doing a SORT on a non indexed column. As you can see the execution plans are again identical for these two queries, but this time instead of using a MERGE JOIN, a CONCATENATION and SORT operations are used.</p> <p><u>UNION ALL query</u></p> <p><img src="http://www.mssqltips.com/tipimages/1387_u5.jpg" border="1" /></p> <p><u>UNION query</u></p> <p><img src="http://www.mssqltips.com/tipimages/1387_u6.jpg" border="1" /></p>Prilihttp://www.blogger.com/profile/10783694458472515220noreply@blogger.com0tag:blogger.com,1999:blog-5395376166257494453.post-20073683303360058612009-01-13T05:19:00.000-08:002009-01-13T05:20:19.071-08:00Fungsi-fungsi String Pada SQL Server 2005Beberapa fungsi string pada SQL Server adalah sebagai berikut:<br /><br />ASCII,<br />Digunakan untuk mengembalikan kode ASCII dari ekspresi yang kita masukkan pada parameter fungsi ini.<br />Contohnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">select ascii('A')</span><br /><br />Hasilnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">-----------<br />65<br /><br />(1 row(s) affected)</span><br /><br />CHAR,<br />Digunakan untuk konversi suatu ekspresi integer yang berupa kode ASCII menjadi sebuah karakter.<br />Contohnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">select char(65)</span><br /><br />Hasilnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">----<br />A<br /><br />(1 row(s) affected)</span><br /><br />CHARINDEX,<br />Digunakan untuk menampilkan posisi awal dari suatu string terhadap string lainnya.<br />Contohnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">select charindex('AS','PASCAL')</span><br /><br />Hasilnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">-----------<br />2<br /><br />(1 row(s) affected)</span><br /><br />DIFFERENCE,<br />Digunakan untuk menampilkan nilai perbedaan antara dua buah nilai ekspresi SOUNDEX. Nilai perbedaan disini berkisar antara 0 sampai 4. Angka 4 menyatakan bahwa dua buah ekspresi SOUNDEX tadi sangat identik. Mengenai deskripsi fungsi SOUNDEX silakan Anda baca pada bagian bawah.<br />Contohnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">select soundex('army'), soundex('armee'), difference('army','armee')</span><br /><br />Hasilnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">----- ----- -----------<br />A650 A650 4<br /><br />(1 row(s) affected)</span><br /><br />LEFT,<br />Digunakan untuk menampilkan suatu ekspresi string dari sebelah kiri sebanyak N karakter.<br />Contohnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">select left('ABCD',2)</span><br /><br />Hasilnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">----<br />AB<br /><br />(1 row(s) affected)</span><br /><br />LEN,<br />Digunakan untuk menampilkan panjang suatu ekspresi string.<br />Contohnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">select len('SONY AK')</span><br /><br />Hasilnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">-----------<br />7<br /><br />(1 row(s) affected)</span><br /><br />LOWER,<br />Digunakan untuk membuat ekspresi string menjadi huruf kecil semua.<br />Contohnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">select lower('Hebat Sekali')</span><br /><br />Hasilnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">------------------------<br />hebat sekali<br /><br />(1 row(s) affected)</span><br /><br />LTRIM,<br />Digunakan untuk menghilangkan semua karakter blank (spasi) pada awal suatu ekspresi string.<br />Contohnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">select ltrim(' Tiga spasi di depan')</span><br /><br />Hasilnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">----------------------<br />Tiga spasi di depan<br /><br />(1 row(s) affected)</span><br /><br />NCHAR,<br />Digunakan untuk menampilkan suatu karakter Unicode dari suatu nilai integer yang diberikan.<br />Contohnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">select nchar(251)</span><br /><br />Hasilnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">----<br />û<br /><br />(1 row(s) affected)</span><br /><br />PATINDEX,<br />Digunakan untuk mengetahui posisi awal dari suatu pattern string pada kesempatan pertama. PATINDEX akan memberikan nilai 0 jika pattern tidak ditemukan.<br />Contohnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">SELECT PATINDEX('%band%', 'ada band')</span><br /><br />Hasilnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">-----------<br />5<br /><br />(1 row(s) affected)</span><br /><br />REPLACE,<br />Digunakan untuk mengganti string yang diberikan pada parameter kedua dengan string pada parameter ketiga pada string pada parameter pertama. Bingung ya? Lihat saja contohnya di bawah ini.<br />Contohnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">select replace('sony arianto kurniawan','an','??')</span><br /><br />Hasilnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">-----------------------<br />sony ari??to kurniaw??<br /><br />(1 row(s) affected)</span><br /><br />QUOTENAME,<br />Digunakan untuk mendapatkan string dengan demiliter sesuai keinginan kita dan valid menurut SQL Server. Delimiter disini bisa berupa single quotation mark ('), kurung siku ([]) atau double quotation mark ("). Jika parameter ini tidak disertakan maka secara otomatis akan menggunakan tanda kurung siku.<br />Contohnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">SELECT QUOTENAME('sony arianto','''')</span><br /><br />Hasilnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">---------------<br />'sony arianto'<br /><br />(1 row(s) affected)</span><br /><br />Contoh 2:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">SELECT QUOTENAME('sony arianto')</span><br /><br />Hasilnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">---------------<br />[sony arianto]<br /><br />(1 row(s) affected)</span><br /><br />REPLICATE,<br />Digunakan untuk mengulang sebuah ekspresi karakter sebanyak beberapa kali yang Anda inginkan.<br />Contohnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">select replicate('sony.com ',3)</span><br /><br />Hasilnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">---------------------------<br />sony.com sony.com sony.com<br /><br />(1 row(s) affected)</span><br /><br />REVERSE,<br />Digunakan untuk membalik ekspresi string yang diberikan.<br />Contohnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">select reverse('kasur rusak 2')</span><br /><br />Hasilnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">-------------<br />2 kasur rusak<br /><br />(1 row(s) affected)</span><br /><br />RIGHT,<br />Digunakan untuk mengambil string sebanyak n-buah diambil dari sebelah kanan.<br />Contohnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">select right('web development',4)</span><br /><br />Hasilnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">----<br />ment<br /><br />(1 row(s) affected)</span><br /><br />RTRIM,<br />Digunakan untuk mendapatkan string serta menghapus semua blank yang ada di belakang string tersebut.<br />Contohnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">select rtrim('good boy ')</span><br /><br />Hasilnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">----------<br />good boy<br /><br />(1 row(s) affected)</span><br /><br />SOUNDEX,<br />Digunakan untuk mendapatkan empat karakter kode SOUNDEX untuk mengevaluasi kesamaan terhadap dua buah string.<br />Contohnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">select soundex('cold'), soundex('colt')</span><br /><br />Hasilnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">----- -----<br />C430 C430<br /><br />(1 row(s) affected)</span><br /><br />SPACE,<br />Digunakan untuk menghasilkan string yang terdiri dari sejumlah karakter spasi yang diulang sebanyak n-kali.<br />Contohnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">select 'Sony'+space(4)+'AK'</span><br /><br />Hasilnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">----------<br />Sony AK<br /><br />(1 row(s) affected)</span><br /><br />STR,<br />Digunakan untuk mengkonversikan data numerik kedalam bentuk string.<br />Contohnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">select str(65.73)</span><br /><br />Hasilnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">-----<br />66<br /><br />(1 row(s) affected)</span><br /><br />STUFF,<br />Digunakan untuk menghapus suatu substring dari suatu string dan meng-insert suatu substring lainnya pada suatu posisi tertentu.<br />Contohnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">select stuff('web depment',7,0,'velo')</span><br /><br />Hasilnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">---------------<br />web development<br /><br />(1 row(s) affected)</span><br /><br />SUBSTRING,<br />Digunakan untuk mendapatkan suatu substring dari suatu string, bisa dimulai dari posisi tertentu dan sebanyak n-buah karakter.<br />Contohnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">select substring('sony-ak.com',5,1)</span><br /><br />Hasilnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">----<br />-<br /><br />(1 row(s) affected)</span><br /><br />UNICODE,<br />Digunakan untuk mendapatkan nilai integer dari suatu string Unicode. Ini merupakan kebalikan dari NCHAR.<br />Contohnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">select unicode('û')</span><br /><br />Hasilnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">-----------<br />251<br /><br />(1 row(s) affected)</span><br /><br />UPPER,<br />Digunakan untuk merubah suatu string menjadi hurufnya besar semua.<br />Contohnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">select upper('ini lower')</span><br /><br />Hasilnya:<br /><br /><span style="font-family:Courier New;font-size:85%;color:#008000;">---------<br />INI LOWER<br /><br />(1 row(s) affected)</span>Prilihttp://www.blogger.com/profile/10783694458472515220noreply@blogger.com0tag:blogger.com,1999:blog-5395376166257494453.post-51720009865845687842009-01-13T05:12:00.000-08:002009-01-13T05:18:16.587-08:00Date and Time Manipulation in SQL Server 2005<span id="intelliTxt"><span id="_ctl0_ArticleText"><p>SQL Server 2000 does not have separate data types for date and time. Instead the Microsoft SQL Server Team chose to combine both the data types into one and store it as a datetime data type. Date and time can be stored in SQL Server in datetime or smalldatetime. The datetime data type can store dates from January 1, 1753 to December 31, 9999 to an accuracy of up to 0.003 part of a second. The smalldatetime data type can store data from January 1, 1900 to June 6, 2079 with accuracy of up to the minute.</p> <p>SQL Server takes into account a system reference date, which is called the base date for SQL Server. This base date is January 1st, 1900. It is from here that the main problem stems. SQL Server stores the datetime data type internally as two 4 byte integers and smalldatetime as two 2 byte integers. The first integer in both the cases stores the number of day difference from the base date. The second integer part stores the number of milliseconds/minutes since midnight. </p> <div class="sh">Date and Time Data Entry</div> <p>When only the time part is provided as input, the base date is appended to the time. If only the date part is provided the time appended is as of midnight. Some example code to observe the same is as follows:</p> <table class="ct2" id="Table2" width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td> <p>use pubs </p> <p>go</p> <p>---------------- Inserting only the time part into a datetime column --------------<br />/* Creating a Test Table */<br />Create Table MyDateTest99<br />(<br /> DateColumn datetime<br />)<br />go<br /></p> <p>/* Inserting the test value into the table */<br />insert into MyDateTest99 values ('10:00 AM') <br />go<br /></p> <p>/* Selecting the result */<br />select DateColumn from MyDateTest99<br />go<br /></p> <p>/* Performing Cleanup */<br />drop table MyDateTest99<br />go<br /></p> <p>---------------- Inserting only the date part into a datetime column --------------<br />use pubs </p> <p>go</p> <p>/* Creating a Test Table */<br />Create Table MyDateTest99<br />(<br /> DateColumn datetime<br />)<br />go<br /></p> <p>/* Inserting the test value into the table */<br />insert into MyDateTest99 values ('January 1, 2000')<br />go<br /></p> <p>/* Selecting the result */<br />select DateColumn from MyDateTest99<br />go<br /></p> <p>/* Performing Cleanup */<br />drop table MyDateTest99<br />go<br /></p> </td></tr></tbody></table> So, the most common question that is asked is: <p>Q: How do I get SQL Server to return only the Date component or only the Time component from the datetime data type?<br />A: By using the <span class="ct1">Convert</span> function. The syntax for using the convert function is: </p> <table class="ct2" id="Table3" width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td>CONVERT <b>( </b><i>data_type </i>[ <b>( </b><i>length </i><b>) </b>] <b>,</b> <i>expression</i> [ <b>,</b> <i>style </i>] <b>)</b></td></tr></tbody></table> <p>By varying the datatype and length, we can get the desired component. Moreover, the style argument in the <span class="ct1">Convert</span> function is provided exclusively for use with date and time data. Some sample code illustrating the same is as follows: </p> <table class="ct2" id="Table3" width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td> <p>use pubs </p> <p>go</p> <p>---------------- Selecting only the date part from a datetime column --------------<br />/* Creating a Test Table */<br />Create Table MyDateTest99<br />(<br /> DateColumn datetime<br />)<br />go<br /></p> <p>/* Inserting the test value into the table */<br />insert into MyDateTest99 values (getdate())<br />go<br /></p> <p>/* Selecting the result */<br />select convert(varchar,DateColumn,101) from MyDateTest99<br />go<br /></p> <p>/* Performing Cleanup */<br />drop table MyDateTest99<br />go<br /></p> <p>use pubs </p> <p>go</p> <p>---------------- Selecting only the date part from a datetime column --------------<br />/* Creating a Test Table */<br />Create Table MyDateTest99<br />(<br /> DateColumn datetime<br />)<br />go<br /></p> <p>/* Inserting the test value into the table */<br />insert into MyDateTest99 values (getdate())<br />go<br /></p> <p>/* Selecting the result */<br />select convert(varchar,DateColumn,108) from MyDateTest99<br />go<br /></p> <p>/* Performing Cleanup */<br />drop table MyDateTest99<br />go</p></td></tr></tbody></table> <p>The list of styles that can be used are:</p> <table class="ct2" id="Table1" width="571" border="0" cols="2"> <tbody> <tr valign="top"> <th width="21%"> <p align="left">Style ID</p></th> <th width="29%"> <p align="left">Style Type</p></th></tr> <tr valign="top"> <td width="21%">0 or 100 </td> <td width="29%">mon dd yyyy hh:miAM (or PM)</td></tr> <tr valign="top"> <td width="21%">101</td> <td width="29%">mm/dd/yy</td></tr> <tr valign="top"> <td width="21%">102</td> <td width="29%">yy.mm.dd</td></tr> <tr valign="top"> <td width="21%">103</td> <td width="29%">dd/mm/yy</td></tr> <tr valign="top"> <td width="21%">104</td> <td width="29%">dd.mm.yy</td></tr> <tr valign="top"> <td width="21%">105</td> <td width="29%">dd-mm-yy</td></tr> <tr valign="top"> <td width="21%">106</td> <td width="29%">dd mon yy</td></tr> <tr valign="top"> <td width="21%">107</td> <td width="29%">Mon dd, yy</td></tr> <tr valign="top"> <td width="21%">108</td> <td width="29%">hh:mm:ss</td></tr> <tr valign="top"> <td width="21%">9 or 109 </td> <td width="29%">mon dd yyyy hh:mi:ss:mmmAM (or PM)</td></tr> <tr valign="top"> <td width="21%">110</td> <td width="29%">mm-dd-yy</td></tr> <tr valign="top"> <td width="21%">111</td> <td width="29%">yy/mm/dd</td></tr> <tr valign="top"> <td width="21%">112</td> <td width="29%">yymmdd</td></tr> <tr valign="top"> <td width="21%">13 or 113 </td> <td width="29%">dd mon yyyy hh:mm:ss:mmm(24h)</td></tr> <tr valign="top"> <td width="21%">114</td> <td width="29%">hh:mi:ss:mmm(24h)</td></tr> <tr valign="top"> <td width="21%">20 or 120 </td> <td width="29%">yyyy-mm-dd hh:mi:ss(24h)</td></tr> <tr valign="top"> <td width="21%">21 or 121 </td> <td width="29%">yyyy-mm-dd hh:mi:ss.mmm(24h)</td></tr> <tr valign="top"> <td width="21%">126</td> <td width="29%">yyyy-mm-dd Thh:mm:ss.mmm(no spaces)</td></tr> <tr valign="top"> <td width="21%">130</td> <td width="29%">dd mon yyyy hh:mi:ss:mmmAM</td></tr> <tr valign="top"> <td width="21%">131</td> <td width="29%">dd/mm/yy hh:mi:ss:mmmAM</td></tr></tbody></table> <p>These styles are the format of input to be used when converting character data into datetime and format of output while converting datetime data into characters:</p> <table class="ct2" id="Table4" width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td> <p>use pubs</p> <p>go</p> <p>---------------- Example for the demonstration of use of style while input of data--------------<br />/* Creating a Test Table */<br />Create Table MyDateTest99<br />(<br /> DateColumn datetime<br />)<br />go</p> <p><br />/* Inserting the test values into the table */<br />-- Inserting in US format<br />insert into MyDateTest99 select convert(datetime,'05/08/2004',101)<br />-- Inserting in UK format<br />insert into MyDateTest99 select convert(datetime,'08/05/2004',103)<br />-- Inserting in ISO Format<br />insert into MyDateTest99 select convert(datetime,'20040508',112)<br />go<br /></p> <p>/* Selecting the result */<br />select DateColumn from MyDateTest99<br />go<br /></p> <p>/* Performing Cleanup */<br />drop table MyDateTest99<br />go<br /></p> <p>use pubs</p> <p>go </p><p>---------------- Example for the demonstration of use of style while output of data-------------- </p><p>/* Creating a Test Table */<br />Create Table MyDateTest99<br />(<br /> DateColumn datetime<br />)<br />go<br />/* Inserting the test values into the table */<br />insert into MyDateTest99 select convert(datetime,'05/08/2004',101)</p> <p>go</p> <p>/* Selecting the result */<br />-- In US Format<br />select convert(varchar,DateColumn,101) from MyDateTest99<br />-- In UK Format<br />select convert(varchar,DateColumn,103) from MyDateTest99<br />-- In ISO Format<br />select convert(varchar,DateColumn,112) from MyDateTest99</p> <p>go<br />/* Performing Cleanup */<br />drop table MyDateTest99<br />go<br /></p> </td></tr></tbody></table> <p>Some other functions that can be used for various purposes are <a href="http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_3vtw.asp" target="_blank">DATEADD</a>, <a href="http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_5vxi.asp" target="_blank">DATEDIFF</a>, <a href="http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_1dph.asp" target="_blank">DATENAME</a>, <a href="http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_2mic.asp" target="_blank">DATEPART</a>, <a href="http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_2oqh.asp" target="_blank">DAY</a>, <a href="http://msdn.microsoft.com/library/en-us/tsqlref/ts_ga-gz_4z51.asp" target="_blank">GETDATE</a>, <a href="http://msdn.microsoft.com/library/en-us/tsqlref/ts_ma-mz_0j8o.asp" target="_blank">MONTH</a>, and <a href="http://msdn.microsoft.com/library/en-us/tsqlref/ts_ya-yz_0ab6.asp" target="_blank">YEAR</a>. Here's some further detail on these functions as well as a code sample showing their use:</p> <p><strong>Dateadd</strong>: Returns a new datetime value based on adding an interval to the specified date.</p> <p><em>Syntax: DATEADD ( datepart, number, date )</em> </p> <p><strong>Datediff</strong>: Returns the number of date and time boundaries crossed between two specified dates. </p> <p><em>Syntax: DATEDIFF ( datepart, startdate, enddate ) </em></p> <p><strong>Datename</strong>: Returns a character string representing the specified datepart of the specified date.</p> <p><em>Syntax: DATENAME ( datepart, date )</em></p> <p><strong>Datepart</strong>: Returns an integer representing the specified datepart of the specified date.</p> <p><em>Syntax: DATEPART ( datepart, date ) </em></p> <p><strong>Day: </strong>Returns an integer representing the day datepart of the specified date.</p> <p><em>Syntax: DAY ( date ) </em></p> <p><strong>Getdate</strong>: Returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime<strong> </strong>values.</p> <p><em>Syntax: GETDATE ( )</em></p> <p><strong>Month:</strong> Returns an integer that represents the month part of a specified date.</p> <p><em>Syntax: MONTH ( date ) </em></p> <p><strong>Year: </strong>Returns an integer that represents the year part of a specified date.</p> <p><em>Syntax: YEAR ( date ) </em></p> <table class="ct2" id="Table5" width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td> <p>declare @datevar datetime<br />select @datevar = getdate()</p> <p>/*Example for getdate() : getting current datetime*/ <br />select getdate() [Current Datetime]</p> <p>/*Example for dateadd : getting date 7 days from current datetime*/<br />select dateadd(dd, 7, @datevar) [Date 7 days from now]</p> <p>/*Example for datediff : getting no of days passed since 01-01-2004*/<br />select datediff(dd,'20040101',@datevar) [No of days since 01-01-2004]</p> <p>/*Example for datename : getting month name*/<br />select datename(mm, @datevar) [Month Name]</p> <p>/*Example for datepart : getting week from date*/<br />select datepart(wk, @datevar ) [Week No] </p> <p>/*Example for day : getting day part of date*/<br />select day (@datevar) [Day]</p> <p>/*Example for month : getting month part of date*/<br />select month(@datevar) [Month]</p> <p>/*Example for year : getting year part of date*/<br />select year(@datevar) [Year] <br /></p></td></tr></tbody></table> <p>Now I will provide you with some code samples which you can use for various tasks. I will try to include as many examples I can think of, but this list is not exhaustive:</p> <p>1. To find the first day of a month:</p> <table class="ct2" id="Table6" width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td> <p>select dateadd(dd,-(day(DateColumn)-1),DateColumn) </p></td></tr></tbody></table> <p>2. To find last day of a month:</p> <table class="ct2" id="Table7" width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td> <p>select dateadd(dd,-(day(dateadd(mm,1,DateColumn))),dateadd(mm,1,DateColumn)) </p></td></tr></tbody></table> <p>3. To find birthdays in next seven days:</p> <table class="ct2" id="Table8" width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td> <p>use pubs</p> <p>go</p> <p>/* Creating a Test Table */<br />Create Table MyDateTest99<br />(<br /> Birthday datetime<br />)<br />go<br />/* Inserting the test value into the table */<br />insert into MyDateTest99 select convert (varchar(10),'19780129',120)<br />insert into MyDateTest99 select convert (varchar(10),'19670821',120)<br />insert into MyDateTest99 select convert (varchar(10),'19910112',120)<br />insert into MyDateTest99 select convert (varchar(10),dateadd(dd,2,getdate()),120)<br />insert into MyDateTest99 select convert (varchar(10),'19791016',120)</p> <p><br />go<br />/* Selecting the result */<br />select <br /> Birthday<br />from<br /> MyDateTest99 <br />where<br /> datediff<br /> (<br /> dd<br /> ,convert(datetime,'1900/'+cast(month(getdate()) as varchar)+'/'+cast (day(getdate()) as varchar),111)<br /> ,convert(datetime,'1900/'+cast(month(Birthday) as varchar)+'/'+cast (day(Birthday) as varchar),111)<br /> ) between 0 and 7<br />go<br />/* Performing Cleanup */<br />drop table MyDateTest99<br />go</p></td></tr></tbody></table> <p>4. Number of hours until weekend, that is until Friday at 5 PM (my favorite):</p> <table class="ct2" id="Table9" width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td> <p>use pubs </p> <p>go</p> <p>Create function udf_Time_to_Weekend (@d1 datetime) returns datetime<br />as<br />begin<br /> declare @d2 datetime<br /> select @d2 = case when (datepart(hh,dateadd(dd,(7-datepart(dw,@d1)),@d1)) >= 17 and 7-datepart(dw,@d1) = 0)<br /> then dateadd(hh,17,convert(varchar(10),dateadd(dd,7,@d1),101)) <br /> else dateadd(hh,17,convert(varchar(10),dateadd(dd,(7-datepart(dw,@d1)),@d1),101))<br /> end<br /> return @D2<br />END<br />go<br />Create procedure HoursTillWeekend as<br />set datefirst 6<br />select DATEDIFF(MI,GETDATE(),dbo.udf_Time_to_Weekend(getdate()))/60 "Hours Till Weekend"<br />go<br />exec HoursTillWeekend <br />go<br />drop procedure HoursTillWeekend<br />go<br />drop function udf_Time_to_Weekend <br />go</p></td></tr></tbody></table> <p>5. First and last days of quarter, in which a date falls:</p> <table class="ct2" id="Table10" width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td> <p>use pubs<br />go<br />/* Creating a Test Table */<br />Create Table MyDateTest99<br />(<br /> DateColumn datetime<br />)<br />go<br />/* Inserting the test value into the table */<br />insert into MyDateTest99 select convert (varchar(10),'19780129',120)<br />insert into MyDateTest99 select convert (varchar(10),'19670821',120)<br />insert into MyDateTest99 select convert (varchar(10),'19910112',120)<br />insert into MyDateTest99 select convert (varchar(10),'19791016',120)<br />go<br />/* Selecting the result */<br />select <br /> datepart(qq,DateColumn) QuarterNo<br /> ,dateadd(qq,datepart(qq,DateColumn),dateadd(dd,-(datepart(dy,DateColumn)-1),DateColumn)) FirstDayOfQuarter<br /> ,dateadd(qq,datepart(qq,DateColumn)+1,dateadd(dd,-(datepart(dy,DateColumn)),DateColumn)) LastDayOfQuarter<br />from <br /> MyDateTest99<br />go<br />/* Performing Cleanup */<br />drop table MyDateTest99<br />go</p></td></tr></tbody></table> <p>6. Number of days in a month:</p> <table class="ct2" id="Table11" width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td> <p>Create Function <br /> udf_getNoOfDaysInMonth<br /> (<br /> @month int<br /> ,@year int<br /> )<br />returns <br /> int<br />as<br />begin<br /> return datepart( dd,dateadd(dd,-1,(dateadd(mm,@month,dateadd( yyyy,@year-1900,'19000101')))))<br />end</p> <p>go</p> <p>select dbo.udf_getNoOfDaysInMonth(2,2004)</p> <p>go</p></td></tr></tbody></table> <p>A very common question asked in forums is regarding the change from a character column to a datetime column. The error encountered by developers is :</p> <p><em>The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.</em></p> <p>This is common because the varchar column does not provide any validations against the data and as a result, some invalid entries creep in. So, while converting to datetime, SQL Server is not able to change the character data to datetime and throws up an error. The easiest way to identify the rows that are causing problems and contain invalid datetime data is by using the <span class="ct1">isdate()</span> function:</p> <table class="ct2" id="Table12" width="100%" border="0" cellpadding="0" cellspacing="0"> <tbody> <tr> <td> <p>/* Example to show how to find invalid records */</p> <p>use pubs<br />go<br />/* Creating a Test Table */<br />Create Table MyDateTest99<br />(<br /> DateColumn varchar(8)<br />)<br />go<br />/* Inserting the test value into the table */<br />insert into MyDateTest99 select '19780129'<br />insert into MyDateTest99 select '19670229'<br />insert into MyDateTest99 select '19910112'<br />insert into MyDateTest99 select '19791016'<br />go<br />/* Selecting the result */<br />select <br /> DateColumn<br />from <br /> MyDateTest99<br />where <br /> isdate(DateColumn) = 0<br />go<br />/* Performing Cleanup */<br />drop table MyDateTest99<br />go</p></td></tr></tbody></table> <p>Another common mistake made by developers is that while searching for all records on a particular day a where clause is used like <span class="ct1">"where logdate = @logdate"</span>, when they are passing <span class="ct1">@logdate</span> as <span class="ct1">'01/01/2004'</span>. <span class="ct1">'01/01/2004'</span> really means <span class="ct1">'01/01/2004 00:00:00.000'</span> and will not return data for the complete day. The problem can be solved by using the between clause. The where clause for such a query should be <span class="ct1">"where logdate between @logdate and @logdate2"</span>, where <span class="ct1">@logdate2</span> is <span class="ct1">@logdate + 1</span>. The between clause can make use of an index if it exists, where using a convert function like <span class="ct1">"where convert(varchar,logdate,101) = @logdate"</span> would not and it would slow down the query.</p></span></span>Prilihttp://www.blogger.com/profile/10783694458472515220noreply@blogger.com0