In your Web.config:
<configuration>
<system.web>
<webServices>
<protocols>
<add name="HttpGet"/>
</protocols>
</webServices>
</system.web>
</configuration>
referer: http://stackoverflow.com/questions/1140028/add-http-get-method-to-c-web-serviceThe Loop is used by WordPress to display each of your posts. Using The Loop, WordPress processes each of the posts to be displayed on the current page and formats them according to how they match specified criteria within The Loop tags. Any HTML or PHP code placed in the Loop will be repeated on each post. When WordPress documentation states "This tag must be within The Loop", such as for specific Template Tag or plugins, the tag will be repeated for each post.
For example, among the information The Loop displays by default in WordPress 1.5 are: the Title (the_title()), Time (the_time()), and Categories (the_category()) for each post. Other information about each post can be displayed with the appropriate Template Tags or (for advanced users) by accessing the $post variable, which is set with the current post's information while The Loop is running.
For a beginners look at The Loop, see The Loop in Action.
The Loop should be placed in index.php and in any other Templates used to display post information, but looks slightly different depending on your version of WordPress. You should first "find what version of WordPress you have".
Be sure to include the call for the header Template at the top of your Theme's Templates. If you are using The Loop inside your own design (and your own design is not a template), set WP_USE_THEMES to false.
<?php define('WP_USE_THEMES', false); get_header(); ?>The loop starts here:
<?php if ( have_posts() ) : while ( have_posts() ) : the_post(); ?>
and ends here:
<?php endwhile; else: ?>
<p><?php _e('Sorry, no posts matched your criteria.'); ?></p>
<?php endif; ?>
Be sure to include the call for wp-blog-header.php at the top of your index page. Remember, the path for wp-blog-header.php must be set to the location of your wp-blog-header.php file:
<?php /* Don't remove this line. */ require('./wp-blog-header.php'); ?>The loop starts here:
<?php if ( $posts ) : foreach ( $posts as $post ) : start_wp(); ?>
and ends here:
<?php endforeach; else: ?>
<p><?php _e('Sorry, no posts matched your criteria.'); ?></p>
<?php endif; ?>
This example displays each post with its Title (which is used as a link to the Post's Permalink), Categories, and Content. It is a simple, bare-bones example; likely your Templates will display more information in a way making things easier to style with CSS.
But, in order to be a little more instructive, this example also allows posts in a category with Category ID '3' to be styled differently. To accomplish this, the in_category() Template Tag is used.
The <!-- --> tags are HTML comment tags; if you use this example, these tags will not display in web browsers. They serve no purpose other than to annotate the code below.
<!-- Start the Loop. -->
<?php if ( have_posts() ) : while ( have_posts() ) : the_post(); ?>
<!-- The following tests if the current post is in category 3. -->
<!-- If it is, the div box is given the CSS class "post-cat-three". -->
<!-- Otherwise, the div box will be given the CSS class "post". -->
<?php if ( in_category('3') ) { ?>
<div class="post-cat-three">
<?php } else { ?>
<div class="post">
<?php } ?>
<!-- Display the Title as a link to the Post's permalink. -->
<h2><a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a></h2>
<!-- Display the date (November 16th, 2009 format) and a link to other posts by this posts author. -->
<small><?php the_time('F jS, Y') ?> by <?php the_authors_post_link() ?></small>
<!-- Display the Post's Content in a div box. -->
<div class="entry">
<?php the_content(); ?>
</div>
<!-- Display a comma separated list of the Post's Categories. -->
<p class="postmetadata">Posted in <?php the_category(', '); ?></p>
</div> <!-- closes the first div box -->
<!-- Stop The Loop (but note the "else:" - see next line). -->
<?php endwhile; else: ?>
<!-- The very first "if" tested to see if there were any Posts to -->
<!-- display. This "else" part tells what do if there weren't any. -->
<p>Sorry, no posts matched your criteria.</p>
<!-- REALLY stop The Loop. -->
<?php endif; ?>
Note: Anytime you want to use HTML code, you must be outside the <?php ?> tags. PHP code (even things as simple as curly braces: } ) must be inside the <?php ?> tags. You can start and stop the PHP code in order to intersperse HTML code even within if and else statements, as shown in the above example.
This example can be used to exclude a certain Category or Categories from being displayed. In this case, posts from Categories 3 and 8 are excluded. The example is different than the example above in that it makes a change to the query itself.
<?php query_posts($query_string . '&cat=-3,-8'); ?>
<?php if ( have_posts() ) : while ( have_posts() ) : the_post(); ?>
<div class="post">
<!-- Display the Title as a link to the Post's permalink. -->
<h2><a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a></h2>
<!-- Display the date (November 16th, 2009 format) and a link to other posts by this posts author. -->
<small><?php the_time('F jS, Y') ?> by <?php the_authors_post_link() ?></small>
<div class="entry">
<?php the_content(); ?>
</div>
<p class="postmetadata">Posted in <?php the_category(', '); ?></p>
</div> <!-- closes the first div box -->
<?php endwhile; else: ?>
<p>Sorry, no posts matched your criteria.</p>
<?php endif; ?>
Note: If you use this example for your main page, you should use a different Template for your Category archives, otherwise, WordPress will exclude all posts in Category 3, even when viewing that Category Archive! However, if you want to use the same template file, you can avoid this by using the is_home() tag to ensure posts from Category 3 will only be excluded from the main page:
...
<?php if ( is_home() ) {
query_posts($query_string . '&cat=-3');
}
?>
...
There are other Conditional Tags that can be used to control the output depending on whether or not a particular condition is true with respect to the requested page.
This section deals with advanced use of The Loop. It's a bit technical – but don’t let that scare you. We’ll start off at easy and work up from there. With a little common sense, patience, and enthusiasm, you too can do multiple loops.
First off, "why would one want to use multiple loops?" In general, the answer is that you might want to do something with one group of posts, and do something different to another group of posts, but display both groups on the same page. Something could mean almost anything; you are only limited by your PHP skill and your imagination.
We will get into examples below, but first you should read about the basics. Take a look at the basic Loop. It consists of:
<?php if (have_posts()) : ?>
<?php while (have_posts()) : the_post(); ?>
<!-- do stuff ... -->
<?php endwhile; ?>
<?php endif; ?>
In English (PHP types and people familiar with code speak can skip to below), the above would be read: If we are going to be displaying posts, then get them, one at a time. For each post in the list, display it according to <!-- do stuff ... -->. When you hit the last post, stop. The do stuff line(s), are template dependent.
A little aside on Do stuff: in this example it is simply a placeholder for a bunch of code that determines how to format and display each post on a page. This code can change depending on how you want your WordPress to look. If you look at the Kubrick theme’s index.php the do stuff section would be everything below:
<?php while (have_posts()) : the_post(); ?>
To above:
<?php comments_popup_link('No Comments »', '1 Comment »', '% Comments »'); ?>An explanation for the coders out there: The have_posts() and the_post() are convenience wrappers around the global $wp_query object, which is where all of the action is. The $wp_query is called in the blog header and fed query arguments coming in through GET and PATH_INFO. The $wp_query takes the arguments and builds and executes a DB query that results in an array of posts. This array is stored in the object and also returned back to the blog header where it is stuffed into the global $posts array (for backward compatibility with old post loops).
Once WordPress has finished loading the blog header and is descending into the template, we arrive at our post Loop. The have_posts() simply calls into $wp_query->have_posts() which checks a loop counter to see if there are any posts left in the post array. And the_post() calls $wp_query->the_post() which advances the loop counter and sets up the global $post variable as well as all of the global post data. Once we have exhausted the loop, have_posts() will return false and we are done.
Below are three examples of using multiple loops. The key to using multiple loops is that $wp_query can only be called once. In order to get around this it is possible to re-use the query by calling rewind_posts() or by creating a new query object. This is covered in example 1. In example 2, using a variable to store the results of a query is covered. Example 3 documents the use of the update_post_caches(); function to avoid common plugin problems. Finally, ‘multiple loops in action’ brings a bunch of ideas together to document one way of using multiple loops to promote posts of a certain category on your blog’s homepage.
In order to loop through the same query a second time, call rewind_posts(). This will reset the loop counter and allow you to do another loop.
<?php rewind_posts(); ?>
<?php while (have_posts()) : the_post(); ?>
<!-- Do stuff... -->
<?php endwhile; ?>
If you are finished with the posts in the original query, and you want to use a different query, you can reuse the $wp_query object by calling query_posts() and then looping back through. The query_posts() will perform a new query, build a new posts array, and reset the loop counter.
// Get the last 10 posts in the special_cat category.
<?php query_posts('category_name=special_cat&posts_per_page=10'); ?>
<?php while (have_posts()) : the_post(); ?>
<!-- Do special_cat stuff... -->
<?php endwhile;?>
If you need to keep the original query around, you can create a new query object.
<?php $my_query = new WP_Query('category_name=special_cat&posts_per_page=10'); ?>
<?php while ($my_query->have_posts()) : $my_query->the_post(); ?>
<!-- Do special_cat stuff... -->
<?php endwhile; ?>The query object my_query is used because you cannot use the global have_posts() and the_post() since they both use $wp_query. Instead, call into your new $my_query object.
Another version of using multiple Loops takes another tack for getting around the inability to use have_posts() and the_post(). To solve this, you need to store the original query in a variable, then re-assign it with the other Loop. This way, you can use all the standard functions that rely on all the globals.
For example:
// going off on my own here
<?php $temp_query = $wp_query; ?>
<!-- Do stuff... -->
<?php query_posts('category_name=special_cat&posts_per_page=10'); ?>
<?php while (have_posts()) : the_post(); ?>
<!-- Do special_cat stuff... -->
<?php endwhile; ?>
// now back to our regularly scheduled programming
<?php $wp_query = $temp_query; ?>
Note: In PHP 5, objects are referenced with the "="-operator instead of copied like in PHP 4. To make Example 2 work in PHP 5 you need to use the following code:
// going off on my own here
<?php $temp_query = clone $wp_query; ?>
<!-- Do stuff... -->
<?php query_posts('category_name=special_cat&posts_per_page=10'); ?>
<?php while (have_posts()) : the_post(); ?>
<!-- Do special_cat stuff... -->
<?php endwhile; ?>
// now back to our regularly scheduled programming
<?php $wp_query = clone $temp_query; ?>
However, this second example does not work in WordPress 2.1.
It has been found that some plugins don’t play nice with multiple loops. In these cases it was discovered that some plugins which deal with the keyword(s) and tagging of posts, only work for the first instance of a loop in a page where that loop consists of a subset of total posts. If you find that this is the case, you might want to try the following implementation of the basic loop which adds the update_post_caches($posts) function. This function resets the post cache and is as yet undocumented. This implementation would be used on the second loop in a page only if the first loop retrieves a subset of posts.
Simply amend:
<?php if (have_posts()) : ?>
<?php while (have_posts()) : the_post(); ?>
<!-- Do stuff... -->
<?php endwhile; ?>
to become:
<?php if (have_posts()) : ?>
<?php while (have_posts()) : the_post(); update_post_caches($posts); ?>
<!-- Do stuff... -->
<?php endwhile; ?>
The best way to understand how to use multiple loops is to actually show an example of its use. Perhaps the most common use of multiple loops is to show two (or more) lists of posts on one page. This is often done when a webmaster wants to feature not only the very latest post written, but also posts from a certain category.
Leaving all formatting and CSS issues aside, let us assume we want to have two lists of posts. One which would list the most recent posts (the standard 10 posts most recently added), and another which would contain only one post from the category ‘featured’. Posts in the ‘featured’ category should be shown first, followed by the second listing of posts (the standard). The catch is that no post should appear in both categories.
Step 1. Get only one post from the ‘featured’ category.
<?php $my_query = new WP_Query('category_name=featured&posts_per_page=1');
while ($my_query->have_posts()) : $my_query->the_post();
$do_not_duplicate = $post->ID; ?>
<!-- Do stuff... -->
<?php endwhile; ?>In English the above code would read:
Set $my_query equal to the result of querying all posts where the category is named featured and by the way, get me one post only. Also, set the variable $do_not_duplicate equal to the ID number of the single post returned. Recall that the Do stuff line represents all the formatting options associated for the post retrieved.
Note that we will need the value of $do_not_duplicate in the next step to ensure that the same post doesn't appear in both lists.
Step 2. The second loop, get the X latest posts (except one).
The following code gets X recent posts (as defined in WordPress preferences) save the one already displayed from the first loop and displays them according to Do stuff.
<?php if (have_posts()) : while (have_posts()) : the_post();
if( $post->ID == $do_not_duplicate ) continue; update_post_caches($posts); ?>
<!-- Do stuff... -->
<?php endwhile; endif; ?>
In English the above code would read:
Get all posts, where a post equals $do_not_duplicate then just do nothing (continue), otherwise display all the other the posts according to Do stuff. Also, update the cache so the tagging and keyword plugins play nice. Recall, $do_not_duplicate variable contains the ID of the post already displayed.
The End Result
Here is what the final piece of code looks like without any formatting:
<?php $my_query = new WP_Query('category_name=featured&posts_per_page=1');
while ($my_query->have_posts()) : $my_query->the_post();
$do_not_duplicate = $post->ID;?>
<!-- Do stuff... -->
<?php endwhile; ?>
<!-- Do other stuff... -->
<?php if (have_posts()) : while (have_posts()) : the_post();
if( $post->ID == $do_not_duplicate ) continue; update_post_caches($posts); ?>
<!-- Do stuff... -->
<?php endwhile; endif; ?>The end result would be a page with two lists. The first list contains only one post -- the most recent post from the 'feature' category. The second list will contain X recent posts (as defined in WordPress preferences) except the post that is already shown in the first list. So, once the feature post is replaced with a new one, the previous feature will show up in standard post list section below (depending on how many posts you choose to display and on the post frequency). This technique (or similar) has been used by many in conjunction with knowledge of the Template Hierarchy to create a different look for home.php and index.php. See associated resources at the bottom of this page.
Note for Multiple Posts in the First Category
If posts_per_page=2 or more, you will need to alter the code a bit. The variable $do_not_duplicate needs to be changed into an array as opposed to a single value. Otherwise, the first loop will finish and the variable $do_not_duplicate will equal only the id of the latest post. This will result in duplicated posts in the second loop. To fix the problem replace
<?php $my_query = new WP_Query('category_name=featured&posts_per_page=1');
while ($my_query->have_posts()) : $my_query->the_post();
$do_not_duplicate = $post->ID;?>with
<?php $my_query = new WP_Query('category_name=featured&posts_per_page=2');
while ($my_query->have_posts()) : $my_query->the_post();
$do_not_duplicate[] = $post->ID ?>Note that "posts_per_page" can be any number. This changes $do_not_duplicate into an array. Then replace
<?php if (have_posts()) : while (have_posts()) : the_post(); if( $post->ID ==
$do_not_duplicate ) continue; update_post_caches($posts); ?>
with
<?php if (have_posts()) : while (have_posts()) : the_post();
if (in_array($post->ID, $do_not_duplicate)) continue;
update_post_caches($posts); ?>
Where you continue the pattern for whatever posts_per_page is set equal to (2 in this case).
Alternatively you can pass the entire $do_not_duplicate array to $wp_query and only entries that match your criteria will be returned:
<?php query_posts(array('post__not_in'=>$do_not_duplicate));
if (have_posts()) : while (have_posts()) : the_post();
update_post_caches($posts); ?> Note that instead a string, the query parameter was an associative array, with post__not_in option.
This article on multiple loops is a combination of Ryan Boren and Alex King's discussion about the Loop on the Hackers Mailing List as well as the tutorial written at MaxPower.
To learn more about the WordPress Loop, and the various template tags that work only within the Loop, here are more resources.
FTP server service on a host that comes with advanced web hosting package such as VPS (Virtual Private Server), VDS (Virtual Dedicated Server), hybrid server, or dedicated server, regardless of whether it’s powered by proftpd, vsftpd, or pure-ftpd, etc., usually been set up and configured by default to restrict and jail the FTP users to have only limited access to files and folders located within their own home directory. The restriction is set by chroot directive where FTP users are locked within chrooted folder, normally their home folder.
If FTP users need to access or change to another folders out of chrooted home directory, they have to be allowed to access other directories and files by disabling chroot control. Disable chroot will allow FTP users to break out the chroot to go to directory in different location.
To disable chroot cage for FTP users in their home directories, locate the chroot configuration in FTP server configuration file and set the value to “NO”. Some directive name and configuration file name with its location for common FTP server software is listed below.
Pure-FTPd: /etc/pure-ftpd.conf
ChrootEveryone=NO
ProFTPd: /etc/proftpd.conf
DefaultRoot ~ (delete this line)
VSFTPd: /etc/vsftpd.conf
chroot_local_user=NO
It is very easy to add Regular Expressions or other custom functionality to SQL Server 2005 because it support CLR. This means all you have to do it create a custom Assembly, mark it up with some attributes (similar to the way you do web services), and click the deploy button, and that is it.
Microsoft really does make it easy using Visual Studio 2005 (and 2008 I assume). All you have to do it create a SQL Server Project. You can use VB.NET or C#, just pick the appropriate SQL Server Project. It pretty much walks you through creating the project. After it is done, you will need to right-click the project | Add | User-Defined Function... Give it whatever name you want. It gives you a simple stub. Just build and deploy. It deploys the assembly to the database it helps you with initially, and makes User-Defined functions (that call the assembly). You can then call your function like any other User-Defined function. The name and parameters show up in the User-Defined functions section under Database | Programmability | Functions | Scalar-valued Functions.
It was also recommended by someone (see references) to in execute the following SQL (I only did it the first time I deployed) to enable CLR and install required support.
sp_configure 'clr enabled',1
reconfigure
There is one VERY important thing you need add to any method you want to be able to access from SQL. You must add the attribute [SqlFunction]. The method must also be public and static I believe. The parameters and return value have to be SQL types like: SqlChars, SqlString, SqlInt32, etc. You can use standard C# and VB.NET types everywhere within your method, but the parameters and return value MUST be SQL types.
Below is my implementation (or at least part what I wrote and part an adaptation of parts from what other people wrote... see references) of three key Regular Expression methods I think are very useful.
reference : http://justgeeks.blogspot.com/2008/08/adding-regular-expressions-regex-to-sql.html
One of the new features introduced in C# 3.0 which will be available in Visual Studio "Orcas" (currently in CTP version) is anonymous type. Anonymous type is something very similar to tuple type from Cω [1] (which is based on tuple types known from many functional programming languages including F#). Anonymous types are extremely useful in LINQ queries, because it allows you to construct type with several properties without declaring the type (with all the properties). Example of query with anonymous type looks like this:
var q = from c in db.Customers
where c.Country = "Czech Republic"select new { FullName=c.Name+" "+c.Surname, Address=c.Address };
Ok, it's probabbly not the best example, but it demonstrates the point - you want to return some information from query and you don't need to declare type that contains FullName and Address properties before, because you need it only for this single query (and you want to return only these two fields, so you don't transfer additional data that you don't need from database).
Now let's get to the second point - because anonymous types are really anonymous, you can't use them as return types from methods. This is intentional limitation of C# 3 - if you want to return it from method it should have some name to make the code more readable. In some sitations you can return anonymous type as an object and access it's properties using reflection, but this will be slow (so it's usually better to refactor the code and create named type).
Internally, the runtime still needs to know the type - because everything stays strongly typed. Therefore the compiler generates type to represent the anonymous type and gives it some unique name:
// Sample anonymous type..var ann = new { City="Prague", Name="Tomas" };Signature of the class generated by C# compiler looks like this:
[CompilerGenerated]
public sealed class <Projection>f__0
{
// Methodspublic <Projection>f__0();
public override bool Equals(object);
public override int GetHashCode();
public override string ToString();
// Propertiespublic string City { get; set; }
public string Name { get; set; }
// Fieldsprivate string _City;
private string _Name;
}
There is one interesting quote in the C# 3.0 specification: "Within the same program, two anonymous object initializers that specify a sequence of properties of the same names and types in the same order will produce instances of the same anonymous type". This implies that when you use two anonymous types with same properties in two different places of same program, the compiler will use same generated class for these two types (this actually isn't true for the May CTP, but in the latest "Orcas" CTP it works as described).
This is interesting fact, because thanks to this it is possible to write the following "little trick" that makes it possible to return instance of anonymous type from method and cast it back to the original type (so you can directly access to properties of the object instead of using reflection). For the casting we need to know the type, but because the type is anonymous it's not that simple, however thanks to the type inference that is used when calling methods you can get the type without writing the actuall type name - you'll just need another variable (or expression) of the same type:
// Method that returns anonymous type as objectobject ReturnAnonymous()
{
return new { City="Prague", Name="Tomas" };
}
// Application entry-pointvoid Main()
{
// Get instance of anonymous type with 'City' and 'Name' propertiesobject o = ReturnAnonymous();
// This call to 'Cast' method converts first parameter (object) to the
// same type as the type of second parameter - which is in this case
// anonymous type with 'City' and 'Name' propertiesvar typed = Cast(o, new { City="", Name="" });
Console.WriteLine("Name={0}, City={1}", typed.Name, typed.City);
}
// Cast method - thanks to type inference when calling methods it
// is possible to cast object to type without knowing the type name
T Cast<T>(object obj, T type)
{
return (T)obj;
}
This trick uses type inference algorithm that is used when calling methods. If you create anonymous type with the same properties in second class, the compiler knows the type and can use it as type parameter that is passed to the Cast method. This assigns name of the anonymous type to the generic parameter T and you can cast the value (passed as a first parameter) back to the anonymous type.
So you can return anonymous types from method and use them in another method without reflection (the only overhead is one method call, one created object instance and one type cast). The question is whether you should do this?
Although this code works quite well (in latest "Orcas" CTP) it is quite risky to use it - first this behavior might change in the final version. Even if this will still work in final version, you can use it only for types in one assembly (two anonymous types from two different assemblies will be internally compiled to two different types that can't be converted using this trick). It also bypasses C# 3.0 limitation that has quite good reason - when you're returning object instead of named type it will make the code less readable and it will be difficult to understand for someone else. For me, it is more an interesting trick than something I'd like to see in my code :-), but if you know of any good reasons for using it, please let me know!
http://tomasp.net/blog/cannot-return-anonymous-type-from-method.aspx
Upgrade your Internet Experience
Welcome Sign in
Suggestions
SQL Server Developer Center
HomeLibraryLearnDownloadsTroubleshootingCommunityForums
Classic | Lightweight Beta | ScriptFree
Click to Rate and Give Feedback
Give feedback on this content
Please Wait
Please Wait
Servers and Enterprise Development
Please Wait
Please Wait
Please Wait
Please Wait
Please Wait
CAST and CONVERT
Transact-SQL Reference (SQL Server 2000)
CAST and CONVERT
Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.
Using CAST:
CAST ( expression AS data_type )
Using CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
expression
Is any valid Microsoft® SQL Server™ expression. For more information, see Expressions.
data_type
Is the target system-supplied data type, including bigint and sql_variant. User-defined data types cannot be used. For more information about available data types, see Data Types.
length
Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types.
style
Is the style of date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or the string format when converting float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).
SQL Server supports the date format in Arabic style, using Kuwaiti algorithm.
In the table, the two columns on the left represent the style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).
Without century (yy)
With century (yyyy)
Standard
Input/Output**
-
0 or 100 (*)
Default
mon dd yyyy hh:miAM (or PM)
1
101
USA
mm/dd/yy
2
102
ANSI
yy.mm.dd
3
103
British/French
dd/mm/yy
4
104
German
dd.mm.yy
5
105
Italian
dd-mm-yy
6
106
-
dd mon yy
7
107
-
Mon dd, yy
8
108
-
hh:mm:ss
-
9 or 109 (*)
Default + milliseconds
mon dd yyyy hh:mi:ss:mmmAM (or PM)
10
110
USA
mm-dd-yy
11
111
JAPAN
yy/mm/dd
12
112
ISO
yymmdd
-
13 or 113 (*)
Europe default + milliseconds
dd mon yyyy hh:mm:ss:mmm(24h)
14
114
-
hh:mi:ss:mmm(24h)
-
20 or 120 (*)
ODBC canonical
yyyy-mm-dd hh:mi:ss(24h)
-
21 or 121 (*)
ODBC canonical (with milliseconds)
yyyy-mm-dd hh:mi:ss.mmm(24h)
-
126(***)
ISO8601
yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
-
130*
Hijri****
dd mon yyyy hh:mi:ss:mmmAM
-
131*
Hijri****
dd/mm/yy hh:mi:ss:mmmAM
* The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).
** Input when converting to datetime; output when converting to character data.
*** Designed for XML use. For conversion from datetime or smalldatetime to character data, the output format is as described in the table. For conversion from float, money, or smallmoney to character data, the output is equivalent to style 2. For conversion from real to character data, the output is equivalent to style 1.
****Hijri is a calendar system with several variations, of which Microsoft® SQL Server™ 2000 uses the Kuwaiti algorithm.
Important By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That is, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. Many client applications, such as those based on OLE Automation objects, use a cutoff year of 2030. SQL Server provides a configuration option (two digit year cutoff) that changes the cutoff year used by SQL Server and allows the consistent treatment of dates. The safest course, however, is to specify four-digit years.
When you convert to character data from smalldatetime, the styles that include seconds or milliseconds show zeros in these positions. You can truncate unwanted date parts when converting from datetime or smalldatetime values by using an appropriate char or varchar data type length.
This table shows the style values for float or real conversion to character data.
Value
Output
0 (default)
Six digits maximum. Use in scientific notation, when appropriate.
1
Always eight digits. Always use in scientific notation.
2
Always 16 digits. Always use in scientific notation.
In the following table, the column on the left represents the style value for money or smallmoney conversion to character data.
Value
Output
0 (default)
No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.
1
Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.
2
No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.
Returns the same value as data type 0.
Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit conversions are those conversions that require the CAST (CONVERT) function to be specified. This chart shows all explicit and implicit data type conversions allowed for SQL Server system-supplied data types, including bigint and sql_variant.
Note Because Unicode data always uses an even number of bytes, use caution when converting binary or varbinary to or from Unicode supported data types. For example, this conversion does not return a hexadecimal value of 41, but of 4100: SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary)
Automatic data type conversion is not supported for the text and image data types. You can explicitly convert text data to character data, and image data to binary or varbinary, but the maximum length is 8000. If you attempt an incorrect conversion (for example, if you convert a character expression that includes letters to an int), SQL Server generates an error message.
When the output of CAST or CONVERT is a character string, and the input is a character string, the output has the same collation and collation label as the input. If the input is not a character string, the output has the default collation of the database, and a collation label of coercible-default. For more information, see Collation Precedence.
To assign a different collation to the output, apply the COLLATE clause to the result expression of the CAST or CONVERT function. For example:
SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS
There is no implicit conversion on assignment from the sql_variant data type but there is implicit conversion to sql_variant.
When converting character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in this table.
From data type
To data type
Result
int, smallint, or tinyint
char
*
varchar
*
nchar
E
nvarchar
E
money, smallmoney, numeric, decimal, float, or real
char
E
varchar
E
nchar
E
nvarchar
E
* Result length too short to display.
E Error returned because result length is too short to display.
Microsoft SQL Server guarantees that only roundtrip conversions, conversions that convert a data type from its original data type and back again, will yield the same values from release to release. This example shows such a roundtrip conversion:
DECLARE @myval decimal (5, 2)
SET @myval = 193.57
SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5))
-- Or, using CONVERT
SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval))
Do not attempt to construct, for example, binary values and convert them to a data type of the numeric data type category. SQL Server does not guarantee that the result of a decimal or numeric data type conversion to binary will be the same between releases of SQL Server.
This example shows a resulting expression too small to display.
USE pubs
SELECT SUBSTRING(title, 1, 25) AS Title, CAST(ytd_sales AS char(2))
FROM titles
WHERE type = 'trad_cook'
Here is the result set:
Title
------------------------- --
Onions, Leeks, and Garlic *
Fifty Years in Buckingham *
Sushi, Anyone? *
(3 row(s) affected)
When data types are converted with a different number of decimal places, the value is truncated to the most precise digit. For example, the result of SELECT CAST(10.6496 AS int) is 10.
When data types in which the target data type has fewer decimal points than the source data type are converted, the value is rounded. For example, the result of CAST(10.3496847 AS money) is $10.3497.
SQL Server returns an error message when non-numeric char, nchar, varchar, or nvarchar data is converted to int, float, numeric, or decimal. SQL Server also returns an error when an empty string (" ") is converted to numeric or decimal.
When binary or varbinary data is converted to character data and an odd number of values is specified following the x, SQL Server adds a 0 (zero) after the x to make an even number of values.
Binary data consists of the characters from 0 through 9 and from A through F (or from a through f), in groups of two characters each. Binary strings must be preceded by 0x. For example, to input FF, type 0xFF. The maximum value is a binary value of 8000 bytes, each of which is FF. The binary data types are not for hexadecimal data but rather for bit patterns. Conversions and calculations of hexadecimal numbers stored as binary data can be unreliable.
When specifying the length of a binary data type, every two characters count as one. A length of 10 signifies that 10 two-character groupings will be entered.
Empty binary strings, represented by 0x, can be stored as binary data.
Each example retrieves the titles for those books that have a 3 in the first digit of year-to-date sales, and converts their ytd_sales to char(20).
-- Use CAST.
USE pubs
GO
SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles
WHERE CAST(ytd_sales AS char(20)) LIKE '3%'
GO
-- Use CONVERT.
USE pubs
GO
SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles
WHERE CONVERT(char(20), ytd_sales) LIKE '3%'
GO
Here is the result set (for either query):
Title ytd_sales
------------------------------ -----------
Cooking with Computers: Surrep 3876
Computer Phobic AND Non-Phobic 375
Emotional Security: A New Algo 3336
Onions, Leeks, and Garlic: Coo 375
(4 row(s) affected)
This example calculates a single column computation (Copies) by dividing the total year-to-date sales (ytd_sales) by the individual book price (price). This result is converted to an int data type after being rounded to the nearest whole number.
USE pubs
GO
SELECT CAST(ROUND(ytd_sales/price, 0) AS int) AS 'Copies'
FROM titles
GO
Here is the result set:
Copies
------
205
324
6262
205
102
7440
NULL
383
205
NULL
17
187
16
204
418
18
1263
273
(18 row(s) affected)
This example concatenates noncharacter, nonbinary expressions using the CAST data type conversion function.
USE pubs
GO
SELECT 'The price is ' + CAST(price AS varchar(12))
FROM titles
WHERE price > 10.00
GO
Here is the result set:
------------------
The price is 19.99
The price is 11.95
The price is 19.99
The price is 19.99
The price is 22.95
The price is 20.00
The price is 21.59
The price is 10.95
The price is 19.99
The price is 20.95
The price is 11.95
The price is 14.99
(12 row(s) affected)
This example uses CAST in the select list to convert the title column to a char(50) column so the results are more readable.
USE pubs
GO
SELECT CAST(title AS char(50)), ytd_sales
FROM titles
WHERE type = 'trad_cook'
GO
Here is the result set:
ytd_sales
-------------------------------------------------- ---------
Onions, Leeks, and Garlic: Cooking Secrets of the 375
Fifty Years in Buckingham Palace Kitchens 15096
Sushi, Anyone? 4095
(3 row(s) affected)
This example converts an int column (the ytd_sales column) to a char(20) column so that it can be used with the LIKE clause.
USE pubs
GO
SELECT title, ytd_sales
FROM titles
WHERE CAST(ytd_sales AS char(20)) LIKE '15%'
AND type = 'trad_cook'
GO
Here is the result set:
title ytd_sales
------------------------------------------------------------ -----------
Fifty Years in Buckingham Palace Kitchens 15096
(1 row(s) affected)
© 2010 Microsoft Corporation. All rights reserved. Terms of Use | Trademarks | Privacy Statement
