SQL server dates in different formats

Here is the list of conversions that facilitates retrieving date time in different formats in T - SQL.

Replace Date in Convert with your field Name.

'YYYYMMDD'

CONVERT(CHAR(8), Date, 112)

'YYYY-MM-DD'

CONVERT(CHAR(10), Date, 23)

'YYMMDD'

CONVERT(VARCHAR(8), Date, 12)

'YY-MM-DD'

STUFF(STUFF(CONVERT(VARCHAR(8), Date, 12),
5, 0, '-'), 3, 0, '-')

'MMDDYY'

REPLACE(CONVERT(CHAR(8), Date, 10), '-', SPACE(0))

'MM-DD-YY'

CONVERT(CHAR(8), Date, 10)

'MM/DD/YY'

CONVERT(CHAR(8), Date, 1)

'MM/DD/YYYY'

CONVERT(CHAR(10), Date, 101)

'DDMMYY'

REPLACE(CONVERT(CHAR(8), Date, 3), '/', SPACE(0))

'DD-MM-YY'

REPLACE(CONVERT(CHAR(8), Date, 3), '/', '-')

'DD/MM/YY'

CONVERT(CHAR(8), Date, 3)

'DD/MM/YYYY'

CONVERT(CHAR(10), Date, 103)

'HH:MM:SS 24'

CONVERT(CHAR(8), Date, 8)

'HH:MM 24'

LEFT(CONVERT(VARCHAR(8), Date, 8), 5)

'HH:MM:SS 12'

LTRIM(RIGHT(CONVERT(VARCHAR(20), Date, 22), 11))

'HH:MM 12'

LTRIM(SUBSTRING(CONVERT(
VARCHAR(20), Date, 22), 10, 5)
+ RIGHT(CONVERT(VARCHAR(20), Date, 22), 3))

For more SQL tips and tricks , Subscribe here or click here to get updates via email
Read more…

How to enter free text in a ajax asp.net combo box ?

A day before I had requirement where the user wants a combox box that gives him access to enter free text also inside it. Thanks to Ajax Control Toolkit which is a open source that lets us modify the code according to our requirements . So How do we let the user enter free text in a ASP.NET AJAX combo box ?

1. Download the source code for AJAX control toolkit from here .
2. Once you open the solution, Under Ajax Control toolkit you will find a folder 'ComboBox'
3. In that try to open Combobox.debug.js . If you dont have it , open Comboxbox.js .
4. Now try to find the following piece of code in it and comment it.

if (this.get_selectedIndex() == -1 && this.get_dropDownStyle() == AjaxControlToolkit.ComboBoxStyle.DropDownList) {
this.get_textBoxControl().value = '';
e.preventDefault();
e.stopPropagation();
return false;
}

This code is basically responsible to prevent entering free text when you dont have any item in the list that matches with what you had typed.

5. Now build the solution, add a reference to the dll , add the combo box control to your web page and you should be able to enter free text in ComboBox.

If you encounter any errors, throw a comment.I will help you out.

Want to know more on ASP.NET ajax, Subscribe here or click here to get updates via email

Read more…

How to set date time as text to a excel in DD/MM/YYYY format using ASP.NET ?

Did you ever try to set a date as text to Excel cell using asp.net ? I had this interesting problem today. The date that I have been passing from my code is something like this '02/06/2009' (dd/mm/yyyy) .But When I check the Excel it appeared as '06/02/2009' (mm/dd/yyyy) . Dates with date > 12 will appear correctly in the excel . I tried by setting the format of cells to dd/mm/yyyy but no luck. So How will we achieve it ?

Solution :

To avoid this problem all you need to do is append a single quote (') before your date . i.e '02/06/2009 should be set as text to excel and you will see the date in correct format.

For more solutions to day to day ASP.NET problems , Subscribe here or click here to get updates via email

Read more…

Dear Users

Thanks for reading Technade. Lot of things keep my day busy these days. I dont find much time to write a article on technade (absolutely sorry about it). I will try to get atleast one article posted on the blog everyday in the coming month.Please bear with us in the mean time.

Note : I am looking for guest authors from my niche area ( Technology ).If you are interested, please let me know.

Prerequisite : Sound knowledge of Windows and PC's
Good English Knowledge.

Yours
Subhash V
Read more…

How to call a WCF service using web service task in SSIS ? WCF and SSIS 2005

A day back I had a requirement where I had to write a simple web service which generates a Excel Report and had to get it executed as a daily job . So I thought of writing a SSIS package which will call a web service . But I am much into learning WCF ( Windows Communication Foundation ) these days .So I thought why dont I create a WCF service instead of Web service !! So I ended up creating a WCF service.

Now the question is Can I call a WCF service using Web service task in SSIS 2005 ?

The answer is Yes you can . All you have to do is follow the steps below .

Before we create a package , we need to make few changes to the Windows Communication foundation host and client so that we can make things work .

Make the following changes to the web.config of WCF host :

There will be a entry called as Bindings in the System.servicemodel tag in web.config of WCF host . You have to change the type of bindings in it as follows .

<bindings>
<basicHttpBinding>
<binding name="BasicHttpBinding_ReportService"
closeTimeout="00:05:00"
openTimeout="00:05:00"
receiveTimeout="00:05:00"
sendTimeout="00:05:00"
maxBufferPoolSize="524288"
maxReceivedMessageSize="2147483647">
<readerQuotas
maxDepth="2147483647"
maxStringContentLength="2147483647"
maxArrayLength="2147483647"
maxBytesPerRead="2147483647"
maxNameTableCharCount="2147483647" />
<security mode="None" />
</binding>
</basicHttpBinding>
</bindings>

By default it will be wsHttpBindings in a WCF service . But in a web service it used to be basic Http Binding and web service task has been configured to work only with basic HTTP binding in SSIS 2005 . Since WCF service supports communication over different protocols , we can always set bindings and achieve the required functionality .

If your web service is also consumed by your web application or something , then you need to configure its web.config as below to enable it use the same binding (BasicHttp) which host exposes .

<bindings>
<basicHttpBinding >
<binding name="BasicHttpBinding_MPCService"
closeTimeout="00:01:00"
openTimeout="00:01:00"
receiveTimeout="00:10:00"
sendTimeout="00:01:00"
allowCookies="false"
bypassProxyOnLocal="false"
hostNameComparisonMode="StrongWildcard"
maxBufferPoolSize="2147483647"
maxReceivedMessageSize="2147483647"
messageEncoding="Text"
textEncoding="utf-8"
useDefaultWebProxy="true">
<readerQuotas
maxDepth="2147483647"
maxStringContentLength="2147483647"
maxArrayLength="2147483647"
maxBytesPerRead="2147483647"
maxNameTableCharCount="2147483647" />
<security mode="None">
<transport clientCredentialType="None" proxyCredentialType="None" realm="" />
<message clientCredentialType="UserName" algorithmSuite="Default" />
</security>
</binding>
</basicHttpBinding>
</bindings>

If you observe the web.config entries , you can observe maxDepth ,maxContentstringLength and other attributes being set to 2147483647 . This has been set as my output of webservice (Excel file ) was very large . You can remove them if you want .

Now lets create a SSIS package using BIDS 2005 .

• The first thing you have to do is setup your HTTP connection using New Connection Wizard .
• Now save the WSDL of the service in a local path . You can get the WSDL by using the link you have used as server URL in the HTTP connection manager above . The link will be something like http://localhost/WCFservice/ReportService.svc?wsdl.Save it as [Filename].wsdl .
• Now add a web service task from the toolbox of your Business Intelligence development studio ( BIDS ) .
• Open the Properties of your webservice task and configure it as below.

• Web Service Task Configuration :In the HTTP connection , select the HTTP connection you added above .
  
• In the WSDL File field , give the local path to where you have stored your WSDL file .
  
• Set OverwriteWSDLFile to true and click on download WSDL .
• Now click on input in the left side pane .
  
• In the input screen you can select your service and the corresponding Web methods .
  
• Select the required one and go to output option .
• In the output screen, you can set output type to either some variable or file connection as per your requirement .
• Now click on OK and you are done with configuring your SSIS package to call a WCF service .

Now you execute your task and you can see it working . If you still have any problems feel free to contact me .

For more solutions to day to day ASP.NET problems , Subscribe here or click here to get updates via email .

Read more…