ASP.NET: 2010

Tuesday, August 31, 2010

MS CRM QUestions

What is the difference between Role Based and Object Based Security Model in MSCRM?
Code based security is entirely coded in the application itself. Role based security uses one of the authentication/authorization mechanisms of the application server.
The fundamental concept in role-based security is that of privilege, defined at design time, on a system-wide basis. Each user has a set of privileges (there are well over a hundred privileges) that are enabled for that user. However, Policies and Roles grant privileges and simplify the process.
The other form of security applies to individual instances of objects. There is a fundamental difference between an access right and a privilege: an access right is a right granted to a user on an object), but a privilege is a right granted to a user on a class of objects. Access rights apply only after privileges have taken effect. In other words, if a user does not have the privilege to read accounts, the user will not be able to read any account, whether or not it has been shared.

Difference between Find and Advanced Find? Which one is faster and Why?
Find perform a search on an attribute for which it is defined. Advanced Find perform search on the conditions and the attributes for which user customizes or runs it. Normal Find in faster as it looks for one attributes and matches with that and returns the result whereas Advanced Find searches for all the attributes and conditions while parsing through the records.
Find is applicable on only active records and it finds only on 2 or 3 column which we have defined in the find view and even it returns any those column which is there in the view but advanced find is applicable to all the records and it finds all the columns and even it returns all the column and filter criteria can be on any column and most important - find filters on just one condition but adv find filters on multiple condition at one time.. Find is faster than Advanced Find.

Difference between CRM Discovery Service and CRM Metadataservice?
CRM service - when we need ORG related information like ORG name, Properties, CRM service path for this ORG -- then we use CRM discovery service..this is only to get the ORG related information
Meta data service - we use this when we need to interact with the CRM records – create, update or delete anything-- it is very specific to the CRM records. When we need to interact with the CRM entity and attribute -- like create an entity (not record) or attribute -- add a value to pick list-- retrieve the pick list value retrieve the entity properties-- attribute property and all we need Meta data service.

Difference between Plug-in and Workflows?
RequirementPlug-inWorkflowNeeds a synchronous action to happen before or after an event occursXThe same piece of logic will be executed for different events and possibly on different entities XxThe logic needs to be executed while offlineXNeeds elevation of privileges (impersonation)XNeeds to execute on events other than assign, create, update, set stateXThe process/logic may take a long time to complete or will be a persistent process (multiple long running steps)xNeeds an asynchronous actionXxEnd users will need to modify the process logicxChild sub processes will be triggered x

Whenever you install MSCRM what all databases get created?
MSCRM_Config and MSCRM_orgname

Whenever you install MSCRM what all user groups get created in Active Directory?
UserGroupAll Microsoft CRM users . This group is updated automatically as users are added and removed from Microsoft CRM.
ReportingGroupA group that contains all users within Microsoft CRM. This group is updated as users are added to Microsoft CRM. Users in this group have read-only access to the filtered views in the Microsoft CRM database.
PrivUserGroupPrivileged Microsoft CRM user group for special administrative functions.
SQLAccessGroupA group that contains Microsoft CRM ASP.NET account and other service accounts. Members in this group have full access to the Microsoft CRM database and this group is used by the Microsoft CRM platform layer. End users should never be added to this group

Difference between MSCRM3.0 and MSCRM4.0?
Multiple organizations can now be hosted and WSDL APIs are now unique per organization in MSCRM4.0.
The metadata API service has been extended to retrieve language information in MSCRM4.0.
Plug-ins (callouts) and workflow now use the same event framework, allowing for even more extensibility.
The SDK has been expanded to include offline access.
Now we can programmatically create, read, update and delete the metadata such as entities, attributes and relationship.
There are three services instead of two which we used to have in previous version
Many to Many Relationship was not available in MSCRM3.0
Multicurrency feature was not available in MSCRM3.0
What is 'Append' and 'Append To' privilege in MSCRM? Give one example of it?Ans: 'Append' and 'Append To' priviledges works together. 'Append To' priviledge will allow other entities to get attached with the entity. 'Append' priviledge will allow the entity to attach the records to the entity with 'Append To' privildege.Let us understand this with simple example:Let us say that you want to attach a note to a case then note entity should have 'Append' access right and case entity should have 'Append To' access right.Let us take one more example to understand this. Suppose you have two custom entities called 'TestCustomEntity1' and 'TestCustomEntity2'. You want to attach the 'TestCustomeEntity2' records to 'TestCustomEntity1'records. For this you need to have 'Append' access right on 'TestCustomEntity1' entity and 'Append To' access right on 'TestCustomEntity2'.Now guess will I be able to attach the records? Answer is " NO" because we need to create a 1:N relationship between 'TestCustomEntity1' and 'TestCustomEntity2'. Now the user who has above mentioned access right in his security role will only be able to add 'TestCustomEntity2' records to 'TestCustomEntity1'.
How to create a Custom Entity record using SDK?Using Dynamic Entity.
How to join two table using Query Expression?Using Linked entity. You should always try to minimize the number of SWS calls that we make in the database. Often during code review it is explored that the number of Microsoft CRM web-service could have been reduced by making use of the Linked-entity concept. So we should always look for the opportunity to minimize the effort.
Can we modify the name of Root Business Unit?No; we will have to re-install MSCRM.
Suppose if I have 20 user license and I have created 20users. What will happen if I create 21st User?The 21st User will get created in MSCRM but that user will be in disabled state.
What is the maximum number of tabs allowed on a Microsoft Dynamics CRM 4.0 forms? 8
How to enable/disable the form assistant? How to make sure the form assistant is expanded/collapsed on a form?Navigate to Customization >> Open the Entity >> Open Forms and Views >> Open Form >> Select Form Properties >> Open Display Tab >> Check/Uncheck the " Enable the Form Assistant" and " Expanded by Default" .The interviewer will always try to figure-out whether one is comfortable with the basic concepts of Microsoft CRM (MS CRM) or not and after that; questions will be asked from your previous experience (if you have any experience in CRM). Those questions will be something like this:
What was your role in the MSCRM implementation project that you have worked on?You should be honest while giving answer to this question and should give a brief overview of the project and your role. This is very important question because the answers of this question will trigger so many questions. You should highlight the key skills you have; this way you will divert the attention of the interviewer to your key skills and try not to expose the area in which you are less confident.
What was the most challenging task you have faced till now?Here you should give answer that exhibits your positive attitude. E.g. for a technical consultant it may be something like ... " I was new to the support and during this experience i faced challenging issue related to plug-in that improved my debugging skills. Email-to-case plug-in was really difficult as we had to take care of so many conditions. I have learnt one thing during my previous assignment and that is 'Never give-up'" .
How is CRM changing and what does the business owner need to be aware of?
At the heart of CRM is the benefit of having the customer record at the center of the data universe rather than multiple galaxies of transactions held in separate, transaction-specific apps. So the innovation lies in new and improved visibility for putting information to use in intelligent decision making. Companies that used to serve 100 are serving 10,000, and with this kind of scaling, better top-level tools and custom dashboards are where I see CRM continuing to morph and advance.
19. Who are the newcomers to the CRM landscape?
It is such a hot area right now, there are literally hundreds. But two I have been following are Zoho and HighRise. Both are niche vendors that have garnered great contact lists with their other products and created relatively simple implementations for their clients and others. They both illustrate the fact that CRM is no longer esoteric — it is going mainstream, which is a great thing for business, particularly customer service and data security.
20. How much should I plan to spend on a good solution?
That’s always a tough question, given that applications vary widely in price based on whether it’s a hosted or installed delivery model, user-based or organization-wide subscription model, or a per-gigabyte or other data model. I would use the cost-per-sale and cost-per-lead values to help determine what a system is worth to a business. For most customers, services are going to be anywhere from $20 to $350 per month per user.
What exactly should I be expecting CRM to do for me?
This is an important question, as there are many misconceptions about software and CRM in particular. Besides some fundamentals, like data security and access and ease of use, CRM will primarily help you do what you do anyway, but move it to the next level. If your main focus is customer service, CRM will help you monitor, deliver, and measure your effectiveness. If your goal is a flat organization where the right hand knows immediately what the left hand is doing, CRM will help you be informed about the customer’s world and not just what relates to your department or team.
What are your expectations for CRM in the next five years?
First, I expect CRM to become much more commonplace. Players like Zoho and 37signals (Highrise) are knocking down barriers to entry. I also expect to see some consolidation. I think the bigger players, like Salesforce, Microsoft, and SAP, will buy up some of their smaller rivals to build into their suites and migrate their user bases. As long as the acquiring provider keeps the connections intact during the migration and meets a similar price point, it will be a win-win. I also think we’ll be seeing more mobile-friendly applications, like Salesforce’s Visualforce and NetSuite’s iPhone, to maximize data access and timeliness.
What are the most common mistakes you see companies make with CRM?
Many organizations use Outlook BCM or Excel for managing their contacts, which offer no planning or setup process — just create a column or type in a field and get started. This causes problems when information is related and the flexible aspects of the previous solution are overlooked. There are real benefits that won’t happen without understanding the new vernacular; the specific way the new solution describes the data. For example, an “account” in Salesforce may not be the same as an “account” in Highrise. In fact, it might have another name altogether, such as “company.” Understanding how the particular vendor uses “leads” or “opportunities” will help to avoid a great deal of frustration.
Do you have a few key best practices someone considering CRM can use?
Yes, I have three that anyone can use. First, consider your future needs. Look down the road and ask “How many contacts will I have in five years?” “How many salespeople will I have?” “How many of my people will need real-time access to this information at home or on their phones and PDAs?” “How much would it cost me to replace these contacts?”
Second, take the opportunity to clean up your data now. Moving to a CRM solution is an opportunity to start with a clean version of accurate data. De-duplicate and otherwise scrub the data to minimize the possibility of needing to import twice. For example, the flexibility of Excel and Outlook BCM allow placing incorrectly formatted information in their fields. This data will not import well without some good planning.
Third, be sure to communicate throughout the process and get early buy-in. The biggest focus of Saleforce.com with its customers is adoption. Members of your team are influencers in their departments. Leverage their expertise and influence by building a team to help you make decisions about the solution. Even if you disagree, listening, acknowledging, and respecting will build loyalty and acceptance within the process.
What advantages might CRM have for specific verticals?
The answer to this question is not if but how much. Since CRM helps you do what you do better, if you are in a professional services company with long sales cycles, project terms, and frequent interactions and touch points, CRM will be exponentially more valuable to you. So service businesses, like lawyers, consultants, and accountants, are ripe for CRM but often have a technological aversion and a strong status quo to maintain.
Does CRM fall more to sales or marketing in most organizations?
In my experience, marketing is somewhat of a new concept in CRM. Sales is definitely involved, but most often it is operations leading the charge.
What are my best resources for finding out more about CRM?
One resource I would recommend any company creates for it is a one- to two-page document that answers the best practice questions above and includes input from the team. Send it to five vendors your team has selected and go over the proposals to see which companies address you as a unique business — not just with a customizable offering but as a discrete business. Here are a few good sites I would recommend to anyone considering a solution

Sunday, April 25, 2010

additonal email data

additional info xslt

<?xml version="1.0" encoding="UTF-8"? >
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:asp="remove" >
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:param name="requestoremail"/>
<xsl:param name="requestorlocation"/>
<xsl:param name="requestorpassword"/>
<xsl:param name="array"/>
<xsl:param name="strBreak"/>

<xsl:param name="strCurrentDate"/>

<xsl:template match="ITRAC/ServiceName">
<table border="0" cellpadding="0" cellspacing="0" width="710px" >
<tr>
<td>
<div id="content_area_Popup">
<div class="ControlHeading">
<table border="0" cellpadding="0" cellspacing="0" width="710px">
<tr>
<td>
<div id="ModalPopuprequest_heading">
<xsl:value-of select="@name"/>
</div>
</td>
</tr>
<tr>
<td>
<div id="ModalPopuprequest_Directive">
Please provide the detail request information below:
</div>
</td>
</tr>
<tr>
<td>
<div id="ServicePopupInner" >
<table border="0" cellpadding="0" cellspacing="0" class="service_contentTable" >
<tr>
<td>
<table border="0" cellpadding="2" cellspacing="0">
<tr>
<td height="5px">
</td>
</tr>
<xsl:for-each select="//Field">
<tr>
<xsl:if test="@id!='footername'">
<xsl:if test="@type = 'LabelHeadcol1'">
<td colspan="3">
<asp:Label runat="server" text="{@headtext1}" CssClass="labelHeading" />
</td>
</xsl:if>
<xsl:if test="@type = 'LabelHeadcol1'">
<td colspan="3">
<asp:Label runat="server" text="{@headtext2}" CssClass="labelHeading" />
</td>
</xsl:if>
<xsl:if test="@type = 'LabelHeadcol2'">
<td style="width:10px"></td>
<td colspan="3">
<asp:Label runat="server" text="{@headtext1}" CssClass="labelHeading" />
</td>
</xsl:if>
</xsl:if>
</tr>
<tr>
<xsl:if test="@id!='footername'">
<td style ="width:10px; padding:3px 3px 3px 3px;" >

<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='false' and @checkboxType='Checkbox'">
<asp:CheckBox id="{@checkboxid}" runat="server" />
</xsl:if>
</td>
<td>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='false' and @checkboxType='Checkboxindent1'">
<asp:CheckBox id="{@checkboxid}" runat="server" />
</xsl:if>
</td>
<td class="labelText" valign="middle" style ="padding-left:5px">
<xsl:variable name="strNew"></xsl:variable>
<xsl:choose>
<xsl:when test="contains(@label, '\n')">
<xsl:value-of select="substring-before(@label, '\n')"/>
<br/>
<p class="SubTextInPopUp"><xsl:value-of select="substring-after(@label, '\n')"/></p>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="@label"/>
</xsl:otherwise>
</xsl:choose>
<asp:Label runat="server" text="{$strNew}"/>
</td>
<td style="width:5px">
</td>
<td valign="top" style ="padding:3px 3px 3px 3px;" >
<xsl:if test="@type = 'Label' and @SecondColumn='true'">
<asp:Label id ="{@id}" runat="server" text="{@label}" Font-Bold="true" />
</xsl:if>
<xsl:if test="@type = 'TextArea'">
<asp:TextBox id="{@id}" runat="server" text="" CssClass="inputboxpopup" height="100px" TextMode="MultiLine" />
</xsl:if>
<xsl:if test="@type = 'Date'">
<asp:TextBox id="{@id}" runat="server" CssClass="date-pick" />
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='false'">
<asp:TextBox id="{@id}" runat="server" text="" CssClass="inputboxpopup"/>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='true' and @id='requestoremail'">
<asp:TextBox id="{@id}" runat="server" text="{$requestoremail}" CssClass="inputboxpopup"/>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='true' and @id='requestorlocation'">
<asp:TextBox id="{@id}" runat="server" text="{$requestorlocation}" CssClass="inputboxpopup"/>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='true' and @id='requestorregion'">
<asp:TextBox id="{@id}" runat="server" text=" " CssClass="inputboxpopup"/>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='true' and @id='requestoroffice'">
<asp:TextBox id="{@id}" runat="server" text=" " CssClass="inputboxpopup"/>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='true' and @id='requestordivision'">
<asp:TextBox id="{@id}" runat="server" text=" " CssClass="inputboxpopup"/>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='true' and @id='requestorpassword'">
<asp:TextBox id="{@id}" runat="server" text="{$requestorpassword}" CssClass="inputboxpopup" readonly="false" />
</xsl:if>
<xsl:if test="@type = 'TextBoxText' and @IsActiveDirectory='true'">
<asp:TextBox id="{@id}" runat="server" Text="{@text}" CssClass="inputboxpopup" />
</xsl:if>
<xsl:if test="@type = 'CheckBoxList'">
<asp:CheckBoxList id="{@id}" runat="server" RepeatDirection="Vertical" RepeatLayout="Table" RepeatColumns="{@RepeatColumns}">
<xsl:for-each select="Listitems/Listitem">
<asp:ListItem value="{@value}" text="{@text}" Selected="{@selected}" CellSpacing="10px" style="padding:0 35px 8px 1px; vertical-align:top; margin: 0; float:left;"></asp:ListItem>
</xsl:for-each>
</asp:CheckBoxList>
</xsl:if>
<xsl:if test="@type = 'DropDownList' and @IsActiveDirectory='false'">
<asp:DropDownList id="{@id}" runat="server" style="width:209px;">
<xsl:for-each select="Listitems/Listitem">
<asp:ListItem value="{@value}" text="{@text}" Selected="{@selected}" ></asp:ListItem>
</xsl:for-each>
</asp:DropDownList>
</xsl:if>
<xsl:if test="@type = 'ListBox'">
<asp:ListBox id="{@id}" runat="server" SelectionMode="Multiple" style="width:209px;">
<xsl:for-each select="Listitems/Listitem">
<asp:ListItem value="{@value}" text="{@text}" Selected="{@selected}" ></asp:ListItem>
</xsl:for-each>
</asp:ListBox>
</xsl:if>

<xsl:if test="@type = 'RadioButtonList'">
<asp:RadioButtonList id="{@id}" runat="server" RepeatDirection="Vertical" RepeatLayout="Table" RepeatColumns="{@RepeatColumns}">
<xsl:for-each select="Listitems/Listitem">
<asp:ListItem value="{@value}" text="{@text}" Selected="{@selected}" style="padding:0 35px 8px 1px; vertical-align:top; margin: 0; float:left;" ></asp:ListItem>
</xsl:for-each>
</asp:RadioButtonList>
</xsl:if>

<div style="padding-left:20px;">
<xsl:if test="@type = 'RadioButtonListIndent2'">
<asp:RadioButtonList id="{@id}" runat="server" RepeatDirection="Vertical" RepeatLayout="Table" RepeatColumns="{@RepeatColumns}">
<xsl:for-each select="Listitems/Listitem">
<asp:ListItem value="{@value}" text="{@text}" Selected="{@selected}" style="padding:0 35px 8px 1px; vertical-align:top; margin: 0; float:left;" ></asp:ListItem>
</xsl:for-each>
</asp:RadioButtonList>
</xsl:if>
</div>
</td>
</xsl:if>
<xsl:if test="@type = 'label' and @id='footername'">
<td class="labelHeading" colspan="6" align="left" style="padding-left:60px;">
<asp:Label runat="server" text="{@label}"/>
</td>
</xsl:if>
</tr>
</xsl:for-each>
</table>
</td>
</tr>
</table>
</div>
</td>
</tr>
</table>
</div>
</div>
</td>
</tr>
</table>
</xsl:template>
<xsl:template name="break">
<xsl:param name="text" select="."/>
<xsl:choose>
<xsl:when test="contains($text, ' ')">
<xsl:value-of select="substring-before($text, ' ')"/>
<br/>
<xsl:call-template name="break">
<xsl:with-param name="label" select="substring-after($text, ' ')"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$text"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>

</xsl:stylesheet>

additional info plain data

audit report.xslt

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
>
<xsl:output method="xml" indent="yes"/>

<xsl:template match="/">
<xsl:apply-templates select="Data"/>

</xsl:template>

<xsl:template match="Data">

<xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:o="urn:schemas-microsoft-com:office:office"

xmlns:x="urn:schemas-microsoft-com:office:excel"

xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:html="http://www.w3.org/TR/REC-html40">
<Styles>
<Style ss:ID="s24">
<Interior ss:Color="#083eb8" ss:Pattern="Solid"/>
<Alignment ss:Horizontal="Left" ss:Vertical="Center" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:Bold="1" ss:Color="#ffffff"/>

</Style>
</Styles>

<Worksheet ss:Name="Audit Report Data" >
<Table x:FullColumns="1" x:FullRows="1">
<Column ss:Width="250.25"/>
<Column ss:Width="100"/>
<Column ss:Width="150.5"/>
<Column ss:Width="113.25"/>
<Column ss:Width="110.25"/>

<xsl:for-each select="Fiscal">
<Row>
<xsl:for-each select="FiscalData">

<Cell >
<Data ss:Type="String">
<xsl:value-of select="text()" />
</Data>
</Cell>
</xsl:for-each>
</Row>
</xsl:for-each>

</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">

<Print>

<ValidPrinterInfo/>

<HorizontalResolution>600</HorizontalResolution>

<VerticalResolution>600</VerticalResolution>

</Print>

<Selected/>

<Panes>

<Pane>

<Number>3</Number>

<ActiveRow>12</ActiveRow>

<ActiveCol>1</ActiveCol>

</Pane>

</Panes>

<ProtectObjects>False</ProtectObjects>

<ProtectScenarios>False</ProtectScenarios>

</WorksheetOptions>

</Worksheet>
</Workbook>
</xsl:template>
</xsl:stylesheet>

update additional info

Wednesday, April 21, 2010

XSL

<?xml:namespace prefix = xsl /><xsl:stylesheet version="1.0" xsl="http://www.w3.org/1999/XSL/Transform" asp="remove">
<xsl:output version="1.0" indent="yes" encoding="UTF-8" method="xml"></xsl:output>
<xsl:param name="session_name"></xsl:param>
<xsl:template match="ServiceName">
<table cellspacing="0" cellpadding="0" width="700" border="0">
<tbody><tr>
<td>
<div id="content_area">
<div class="ControlHeading">
<table cellspacing="0" cellpadding="0" width="700" border="0">
<tbody><tr>
<td>
<div id="request_heading" style="TEXT-ALIGN: left">

</div>
</td>
</tr>
<tr>
<td>
<table class="service_contentTable" style="TEXT-ALIGN: left" cellspacing="0" cellpadding="0" border="0">
<tbody><tr>
<td>
<table cellspacing="0" cellpadding="0" border="0">
<tbody><tr>
<td height="5">
</td>
</tr>
<xsl:for-each select="//Field">
<tr>
<td class="labelHeading">
<xsl:choose>
<xsl:when test="contains(@label, '\n')">
<xsl:value-of select="substring-before(@label, '\n')"></xsl:value-of>
<xsl:value-of select="substring-after(@label, '\n')"></xsl:value-of>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="@label"></xsl:value-of>
</xsl:otherwise>
</xsl:choose>
</td>
<td style="WIDTH: 5px">
</td>
<td>
<xsl:if test="@type = 'TextBox'">

<xsl:value-of select="@value"></xsl:value-of>
</xsl:if>
<xsl:if test="@type = 'CheckBoxList'">
<xsl:for-each select="Listitems/Listitem">
<xsl:if test="@selected = 'True'">
<xsl:value-of select="@value"></xsl:value-of>
<xsl:value-of select="','"></xsl:value-of>
</xsl:if>
</xsl:for-each>
</xsl:if>

<xsl:if test="@type = 'DropDownList'">
<xsl:for-each select="Listitems/Listitem">
<xsl:if test="@selected = 'True'">
<xsl:value-of select="@value"></xsl:value-of>
<xsl:value-of select="','"></xsl:value-of>
</xsl:if>
</xsl:for-each>
</xsl:if>

<xsl:if test="@type = 'ListBox'">
<xsl:for-each select="Listitems/Listitem">
<xsl:if test="@selected = 'True'">
<xsl:value-of select="@value"></xsl:value-of>
<xsl:value-of select="','"></xsl:value-of>
</xsl:if>
</xsl:for-each>
</xsl:if>

<xsl:if test="@type = 'RadioButtonList'">
<xsl:for-each select="Listitems/Listitem">
<xsl:if test="@selected = 'True'">
<xsl:value-of select="@value"></xsl:value-of>
<xsl:value-of select="','"></xsl:value-of>
</xsl:if>
</xsl:for-each>
</xsl:if>

</td>
</tr>
</xsl:for-each>
</tbody></table>
</td>
</tr>
</tbody></table>
</td>
</tr>
</tbody></table>
</div>
</div>
</td>
</tr>
</tbody></table>
</xsl:template>
</xsl:stylesheet>

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

<xsl:stylesheet version="1.0" xsl="http://www.w3.org/1999/XSL/Transform" asp="remove">
<xsl:output version="1.0" indent="yes" encoding="UTF-8" method="xml"></xsl:output>
<xsl:param name="requestoremail"></xsl:param>
<xsl:param name="requestorlocation"></xsl:param>
<xsl:param name="requestorpassword"></xsl:param>
<xsl:param name="array"></xsl:param>
<xsl:param name="strBreak"></xsl:param>

<xsl:param name="strCurrentDate"></xsl:param>

<xsl:template match="ITRAC/ServiceName">
<table cellspacing="0" cellpadding="0" width="710" border="0">
<tbody><tr>
<td>
<div id="content_area_Popup">
<div class="ControlHeading">
<table cellspacing="0" cellpadding="0" width="710" border="0">
<tbody><tr>
<td>
<div id="ModalPopuprequest_heading">
<xsl:value-of select="@name"></xsl:value-of>
</div>
</td>
</tr>
<tr>
<td>
<div id="ModalPopuprequest_Directive">
Please provide the detail request information below:
</div>
</td>
</tr>
<tr>
<td>
<div id="ServicePopupInner">
<table class="service_contentTable" cellspacing="0" cellpadding="0" border="0">
<tbody><tr>
<td>
<table cellspacing="0" cellpadding="2" border="0">
<tbody><tr>
<td height="5">
</td>
</tr>
<xsl:for-each select="//Field">
<tr>
<xsl:if test="@id!='footername'">
<xsl:if test="@type = 'LabelHeadcol1'">
<td colspan="3">
<?xml:namespace prefix = asp /><asp:label text="{@headtext1}" runat="server" cssclass="labelHeading"></asp:label>
</td>
</xsl:if>
<xsl:if test="@type = 'LabelHeadcol1'">
<td colspan="3">
<asp:label text="{@headtext2}" runat="server" cssclass="labelHeading"></asp:label>
</td>
</xsl:if>
<xsl:if test="@type = 'LabelHeadcol2'">
<td style="WIDTH: 10px"></td>
<td colspan="3">
<asp:label text="{@headtext1}" runat="server" cssclass="labelHeading"></asp:label>
</td>
</xsl:if>
</xsl:if>
</tr>
<tr>
<xsl:if test="@id!='footername'">
<td style="PADDING-RIGHT: 3px; PADDING-LEFT: 3px; PADDING-BOTTOM: 3px; WIDTH: 10px; PADDING-TOP: 3px">

<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='false' and @checkboxType='Checkbox'">
<asp:checkbox id="{@checkboxid}" runat="server"></asp:checkbox>
</xsl:if>
</td>
<td>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='false' and @checkboxType='Checkboxindent1'">
<asp:checkbox id="{@checkboxid}" runat="server"></asp:checkbox>
</xsl:if>
</td>
<td class="labelText" style="PADDING-LEFT: 5px" valign="center">
<xsl:variable name="strNew"></xsl:variable>
<xsl:choose>
<xsl:when test="contains(@label, '\n')">
<xsl:value-of select="substring-before(@label, '\n')"></xsl:value-of>

<p class="SubTextInPopUp"><xsl:value-of select="substring-after(@label, '\n')"></xsl:value-of></p>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="@label"></xsl:value-of>
</xsl:otherwise>
</xsl:choose>
<asp:label text="{$strNew}" runat="server"></asp:label>
</td>
<td style="WIDTH: 5px">
</td>
<td style="PADDING-RIGHT: 3px; PADDING-LEFT: 3px; PADDING-BOTTOM: 3px; PADDING-TOP: 3px" valign="top">
<xsl:if test="@type = 'Label' and @SecondColumn='true'">
<asp:label id="{@id}" text="{@label}" runat="server" bold="true"></asp:label>
</xsl:if>
<xsl:if test="@type = 'TextArea'">
<asp:textbox id="{@id}" text="" runat="server" height="100px" cssclass="inputboxpopup" textmode="MultiLine"></asp:textbox>
</xsl:if>
<xsl:if test="@type = 'Date'">
<asp:textbox id="{@id}" text="{$strCurrentDate}" runat="server" cssclass="date-pick"></asp:textbox>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='false'">
<asp:textbox id="{@id}" text="" runat="server" cssclass="inputboxpopup"></asp:textbox>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='true' and @id='requestoremail'">
<asp:textbox id="{@id}" text="{$requestoremail}" runat="server" cssclass="inputboxpopup"></asp:textbox>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='true' and @id='requestorlocation'">
<asp:textbox id="{@id}" text="{$requestorlocation}" runat="server" cssclass="inputboxpopup"></asp:textbox>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='true' and @id='requestorregion'">
<asp:textbox id="{@id}" text=" " runat="server" cssclass="inputboxpopup"></asp:textbox>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='true' and @id='requestoroffice'">
<asp:textbox id="{@id}" text=" " runat="server" cssclass="inputboxpopup"></asp:textbox>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='true' and @id='requestordivision'">
<asp:textbox id="{@id}" text=" " runat="server" cssclass="inputboxpopup"></asp:textbox>
</xsl:if>
<xsl:if test="@type = 'TextBox' and @IsActiveDirectory='true' and @id='requestorpassword'">
<asp:textbox id="{@id}" text="{$requestorpassword}" runat="server" readonly="false" cssclass="inputboxpopup"></asp:textbox>
</xsl:if>
<xsl:if test="@type = 'TextBoxText' and @IsActiveDirectory='true'">
<asp:textbox id="{@id}" text="{@text}" runat="server" cssclass="inputboxpopup"></asp:textbox>
</xsl:if>
<xsl:if test="@type = 'CheckBoxList'">
<asp:checkboxlist id="{@id}" runat="server" repeatdirection="Vertical" repeatlayout="Table" repeatcolumns="{@RepeatColumns}">
<xsl:for-each select="Listitems/Listitem">
<asp:listitem style="PADDING-RIGHT: 35px; PADDING-LEFT: 1px; FLOAT: left; PADDING-BOTTOM: 8px; MARGIN: 0px; VERTICAL-ALIGN: top; PADDING-TOP: 0px" text="{@text}" value="{@value}" selected="{@selected}" cellspacing="10px"></asp:listitem>
</xsl:for-each>
</asp:checkboxlist>
</xsl:if>
<xsl:if test="@type = 'DropDownList' and @IsActiveDirectory='false'">
<asp:dropdownlist id="{@id}" style="WIDTH: 209px" runat="server">
<xsl:for-each select="Listitems/Listitem">
<asp:listitem text="{@text}" value="{@value}" selected="{@selected}"></asp:listitem>
</xsl:for-each>
</asp:dropdownlist>
</xsl:if>
<xsl:if test="@type = 'ListBox'">
<asp:listbox id="{@id}" style="WIDTH: 209px" runat="server" selectionmode="Multiple">
<xsl:for-each select="Listitems/Listitem">
<asp:listitem text="{@text}" value="{@value}" selected="{@selected}"></asp:listitem>
</xsl:for-each>
</asp:listbox>
</xsl:if>

<xsl:if test="@type = 'RadioButtonList'">
<asp:radiobuttonlist id="{@id}" runat="server" repeatdirection="Vertical" repeatlayout="Table" repeatcolumns="{@RepeatColumns}">
<xsl:for-each select="Listitems/Listitem">
<asp:listitem style="PADDING-RIGHT: 35px; PADDING-LEFT: 1px; FLOAT: left; PADDING-BOTTOM: 8px; MARGIN: 0px; VERTICAL-ALIGN: top; PADDING-TOP: 0px" text="{@text}" value="{@value}" selected="{@selected}"></asp:listitem>
</xsl:for-each>
</asp:radiobuttonlist>
</xsl:if>

<div style="PADDING-LEFT: 20px">
<xsl:if test="@type = 'RadioButtonListIndent2'">
<asp:radiobuttonlist id="{@id}" runat="server" repeatdirection="Vertical" repeatlayout="Table" repeatcolumns="{@RepeatColumns}">
<xsl:for-each select="Listitems/Listitem">
<asp:listitem style="PADDING-RIGHT: 35px; PADDING-LEFT: 1px; FLOAT: left; PADDING-BOTTOM: 8px; MARGIN: 0px; VERTICAL-ALIGN: top; PADDING-TOP: 0px" text="{@text}" value="{@value}" selected="{@selected}"></asp:listitem>
</xsl:for-each>
</asp:radiobuttonlist>
</xsl:if>
</div>
</td>
</xsl:if>
<xsl:if test="@type = 'label' and @id='footername'">
<td class="labelHeading" style="PADDING-LEFT: 60px" align="left" colspan="6">
<asp:label text="{@label}" runat="server"></asp:label>
</td>
</xsl:if>
</tr>
</xsl:for-each>
</tbody></table>
</td>
</tr>
</tbody></table>
</div>
</td>
</tr>
</tbody></table>
</div>
</div>
</td>
</tr>
</tbody></table>
</xsl:template>
<xsl:template name="break">
<xsl:param name="text" select="."></xsl:param>
<xsl:choose>
<xsl:when test="contains($text, ' ')">
<xsl:value-of select="substring-before($text, ' ')"></xsl:value-of>

<xsl:call-template name="break">
<xsl:with-param name="label" select="substring-after($text, ' ')"></xsl:with-param>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$text"></xsl:value-of>
</xsl:otherwise>
</xsl:choose>
</xsl:template>

</xsl:stylesheet>

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

xsl v

Monday, February 22, 2010

A potentially dangerous Request.Form value was detected from the client (TextBoxN="...")

Set ValidateRequest="false"

Unless you actually need users to be able to enter HTML you must convert the string to its html encoding equivalent - basically this means that certain characters (like "<") are converted to codes (so "<" is converted to "<"). To perform this conversion use HttpUtility.HtmlEncode, for example:
MyLabel.Text := HttpUtility.HtmlEncode(MyTextBox.Text);

public static string ConvertStringToHTML(string OldString)
{
string NewString="";
NewString=OldString.Replace("&","&");//this should be first, next & will come for < and >.
NewString=NewString.Replace("<","<").Replace(">",">");
return NewString;
}
public static string ConvertHTMLToString(string OldString)
{
string NewString="";
NewString=OldString.Replace("&","&");
NewString=NewString.Replace("<","<").Replace(">",">");
return NewString;
}

Tuesday, February 16, 2010

How to format datetime & date in Sql Server 2005

Execute the following Microsoft SQL Server T-SQL datetime and date formatting scripts in Management Studio Query Editor to demonstrate the multitude of temporal data formats available in SQL Server.

First we start with the conversion options available for sql datetime formats with century (YYYY or CCYY format). Subtracting 100 from the Style (format) number will transform dates without century (YY). For example Style 103 is with century, Style 3 is without century. The default Style values – Style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121 – always return the century (yyyy) format.

– Microsoft SQL Server T-SQL date and datetime formats

– Date time formats – mssql datetime

– MSSQL getdate returns current system date and time in standard internal format

SELECT convert(varchar, getdate(), 100) – mon dd yyyy hh:mmAM (or PM)

– Oct 2 2008 11:01AM

SELECT convert(varchar, getdate(), 101) – mm/dd/yyyy - 10/02/2008

SELECT convert(varchar, getdate(), 102) – yyyy.mm.dd – 2008.10.02

SELECT convert(varchar, getdate(), 103) – dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) – dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) – dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) – dd mon yyyy

SELECT convert(varchar, getdate(), 107) – mon dd, yyyy

SELECT convert(varchar, getdate(), 108) – hh:mm:ss

SELECT convert(varchar, getdate(), 109) – mon dd yyyy hh:mm:ss:mmmAM (or PM)

– Oct 2 2008 11:02:44:013AM

SELECT convert(varchar, getdate(), 110) – mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) – yyyy/mm/dd

SELECT convert(varchar, getdate(), 112) – yyyymmdd

SELECT convert(varchar, getdate(), 113) – dd mon yyyy hh:mm:ss:mmm

– 02 Oct 2008 11:02:07:577

SELECT convert(varchar, getdate(), 114) – hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) – yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) – yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) – yyyy-mm-ddThh:mm:ss.mmm

– 2008-10-02T10:52:47.513

– SQL create different date styles with t-sql string functions

SELECT replace(convert(varchar, getdate(), 111), ‘/’, ‘ ‘) – yyyy mm dd

SELECT convert(varchar(7), getdate(), 126) – yyyy-mm

SELECT right(convert(varchar, getdate(), 106), 8) – mon yyyy

————

– SQL Server date formatting function – convert datetime to string

————

– SQL datetime functions

– SQL Server date formats

– T-SQL convert dates

– Formatting dates sql server

CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))

RETURNS VARCHAR(32)

AS

BEGIN

DECLARE @StringDate VARCHAR(32)

SET @StringDate = @FormatMask

IF (CHARINDEX (‘YYYY’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘YYYY’,

DATENAME(YY, @Datetime))

IF (CHARINDEX (‘YY’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘YY’,

RIGHT(DATENAME(YY, @Datetime),2))

IF (CHARINDEX (‘Month’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘Month’,

DATENAME(MM, @Datetime))

IF (CHARINDEX (‘MON’,@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)

SET @StringDate = REPLACE(@StringDate, ‘MON’,

LEFT(UPPER(DATENAME(MM, @Datetime)),3))

IF (CHARINDEX (‘Mon’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘Mon’,

LEFT(DATENAME(MM, @Datetime),3))

IF (CHARINDEX (‘MM’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘MM’,

RIGHT(‘0′+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))

IF (CHARINDEX (‘M’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘M’,

CONVERT(VARCHAR,DATEPART(MM, @Datetime)))

IF (CHARINDEX (‘DD’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘DD’,

RIGHT(‘0′+DATENAME(DD, @Datetime),2))

IF (CHARINDEX (‘D’,@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, ‘D’,

DATENAME(DD, @Datetime))

RETURN @StringDate

END

GO

– Microsoft SQL Server date format function test

– MSSQL formatting dates

SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YYYY’) – 01/03/2012

SELECT dbo.fnFormatDate (getdate(), ‘DD/MM/YYYY’) – 03/01/2012

SELECT dbo.fnFormatDate (getdate(), ‘M/DD/YYYY’) – 1/03/2012

SELECT dbo.fnFormatDate (getdate(), ‘M/D/YYYY’) – 1/3/2012

SELECT dbo.fnFormatDate (getdate(), ‘M/D/YY’) – 1/3/12

SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YY’) – 01/03/12

SELECT dbo.fnFormatDate (getdate(), ‘MON DD, YYYY’) – JAN 03, 2012

SELECT dbo.fnFormatDate (getdate(), ‘Mon DD, YYYY’) – Jan 03, 2012

SELECT dbo.fnFormatDate (getdate(), ‘Month DD, YYYY’) – January 03, 2012

SELECT dbo.fnFormatDate (getdate(), ‘YYYY/MM/DD’) – 2012/01/03

SELECT dbo.fnFormatDate (getdate(), ‘YYYYMMDD’) – 20120103

SELECT dbo.fnFormatDate (getdate(), ‘YYYY-MM-DD’) – 2012-01-03

– CURRENT_TIMESTAMP returns current system date and time in standard internal format

SELECT dbo.fnFormatDate (CURRENT_TIMESTAMP,‘YY.MM.DD’) – 12.01.03

GO

————

/***** SELECTED SQL DATE/DATETIME FORMATS WITH NAMES *****/

– SQL format datetime

– Default format: Oct 23 2006 10:40AM

SELECT [Default]=CONVERT(varchar,GETDATE(),100)

– US-Style format: 10/23/2006

SELECT [US-Style]=CONVERT(char,GETDATE(),101)

– ANSI format: 2006.10.23

SELECT [ANSI]=CONVERT(char,CURRENT_TIMESTAMP,102)

– UK-Style format: 23/10/2006

SELECT [UK-Style]=CONVERT(char,GETDATE(),103)

– German format: 23.10.2006

SELECT [German]=CONVERT(varchar,GETDATE(),104)

– ISO format: 20061023

SELECT ISO=CONVERT(varchar,GETDATE(),112)

– ISO8601 format: 2008-10-23T19:20:16.003

SELECT [ISO8601]=CONVERT(varchar,GETDATE(),126)

————

– SQL Server datetime formats

– Century date format MM/DD/YYYY usage in a query

– Format dates SQL Server 2005

SELECT TOP (1)

SalesOrderID,

OrderDate = CONVERT(char(10), OrderDate, 101),

OrderDateTime = OrderDate

FROM AdventureWorks.Sales.SalesOrderHeader

/* Result

SalesOrderID OrderDate OrderDateTime

43697 07/01/2001 2001-07-01 00:00:00.000

*/

– SQL update datetime column

– SQL datetime DATEADD

UPDATE Production.Product

SET ModifiedDate=DATEADD(dd,1, ModifiedDate)

WHERE ProductID = 1001

– MM/DD/YY date format

– Datetime format sql

SELECT TOP (1)

SalesOrderID,

OrderDate = CONVERT(varchar(8), OrderDate, 1),

OrderDateTime = OrderDate

FROM AdventureWorks.Sales.SalesOrderHeader

ORDER BY SalesOrderID desc

/* Result

SalesOrderID OrderDate OrderDateTime

75123 07/31/04 2004-07-31 00:00:00.000

*/

– Combining different style formats for date & time

– Datetime formats

– Datetime formats sql

DECLARE @Date DATETIME

SET @Date = ‘2015-12-22 03:51 PM’

SELECT CONVERT(CHAR(10),@Date,110) + SUBSTRING(CONVERT(varchar,@Date,0),12,8)

– Result: 12-22-2015 3:51PM

– Microsoft SQL Server cast datetime to string

SELECT stringDateTime=CAST (getdate() as varchar)

– Result: Dec 29 2012 3:47AM

————

– SQL Server date and time functions overview

————

– SQL Server CURRENT_TIMESTAMP function

– SQL Server datetime functions

– local NYC – EST – Eastern Standard Time zone

– SQL DATEADD function – SQL DATEDIFF function

SELECT CURRENT_TIMESTAMP – 2012-01-05 07:02:10.577

– SQL Server DATEADD function

SELECT DATEADD(month,2,‘2012-12-09′) – 2013-02-09 00:00:00.000

– SQL Server DATEDIFF function

SELECT DATEDIFF(day,‘2012-12-09′,‘2013-02-09′) – 62

– SQL Server DATENAME function

SELECT DATENAME(month, ‘2012-12-09′) – December

SELECT DATENAME(weekday, ‘2012-12-09′) – Sunday

– SQL Server DATEPART function

SELECT DATEPART(month, ‘2012-12-09′) – 12

– SQL Server DAY function

SELECT DAY(‘2012-12-09′) – 9

– SQL Server GETDATE function

– local NYC – EST – Eastern Standard Time zone

SELECT GETDATE() – 2012-01-05 07:02:10.577

– SQL Server GETUTCDATE function

– London – Greenwich Mean Time

SELECT GETUTCDATE() – 2012-01-05 12:02:10.577

– SQL Server MONTH function

SELECT MONTH(‘2012-12-09′) – 12

– SQL Server YEAR function

SELECT YEAR(‘2012-12-09′) – 2012

————

– T-SQL Date and time function application

– CURRENT_TIMESTAMP and getdate() are the same in T-SQL

————

– SQL first day of the month

– SQL first date of the month

– SQL first day of current month – 2012-01-01 00:00:00.000

SELECT DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))

– SQL last day of the month

– SQL last date of the month

– SQL last day of current month – 2012-01-31 00:00:00.000

SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP)+1,0))

– SQL first day of last month

– SQL first day of previous month – 2011-12-01 00:00:00.000

SELECT DATEADD(mm,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))

– SQL last day of last month

– SQL last day of previous month – 2011-12-31 00:00:00.000

SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATEADD(MM,-1,GETDATE()))+1,0))

– SQL first day of next month – 2012-02-01 00:00:00.000

SELECT DATEADD(mm,1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))

– SQL last day of next month – 2012-02-28 00:00:00.000

SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATEADD(MM,1,GETDATE()))+1,0))

GO

– SQL first day of a month – 2012-10-01 00:00:00.000

DECLARE @Date datetime; SET @Date = ‘2012-10-23′

SELECT DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,@Date),0))

GO

– SQL last day of a month – 2012-03-31 00:00:00.000

DECLARE @Date datetime; SET @Date = ‘2012-03-15′

SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,@Date)+1,0))

GO

– SQL first day of year

– SQL first day of the year - 2012-01-01 00:00:00.000

SELECT DATEADD(yy, DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0)

– SQL last day of year

– SQL last day of the year – 2012-12-31 00:00:00.000

SELECT DATEADD(yy,1, DATEADD(dd, -1, DATEADD(yy,

DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0)))

– SQL last day of last year

– SQL last day of previous year – 2011-12-31 00:00:00.000

SELECT DATEADD(dd,-1,DATEADD(yy,DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0))

GO

– SQL calculate age in years, months, days

– SQL table-valued function

– SQL user-defined function – UDF

– SQL Server age calculation – date difference

– Format dates SQL Server 2008

USE AdventureWorks2008;

GO

CREATE FUNCTION fnAge (@BirthDate DATETIME)

RETURNS @Age TABLE(Years INT,

Months INT,

Days INT)

AS

BEGIN

DECLARE @EndDate DATETIME, @Anniversary DATETIME

SET @EndDate = Getdate()

SET @Anniversary = Dateadd(yy,Datediff(yy,@BirthDate,@EndDate),@BirthDate)

INSERT @Age

SELECT Datediff(yy,@BirthDate,@EndDate) - (CASE

WHEN @Anniversary > @EndDate THEN 1

ELSE 0

END), 0, 0

UPDATE @Age SET Months = Month(@EndDate - @Anniversary) - 1

UPDATE @Age SET Days = Day(@EndDate - @Anniversary) - 1

RETURN

END

GO

– Test table-valued UDF

SELECT * FROM fnAge(‘1956-10-23′)

SELECT * FROM dbo.fnAge(‘1956-10-23′)

/* Results

Years Months Days

52 4 1

*/

———-

– SQL date range between

———-

– SQL between dates

USE AdventureWorks;

– SQL between

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate BETWEEN ‘20040301′ AND ‘20040315′

– Result: 108

– BETWEEN operator is equivalent to >=…AND….<=

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate

BETWEEN ‘2004-03-01 00:00:00.000′ AND ‘2004-03-15 00:00:00.000′

/*

Orders with OrderDates

‘2004-03-15 00:00:01.000′ – 1 second after midnight (12:00AM)

‘2004-03-15 00:01:00.000′ – 1 minute after midnight

‘2004-03-15 01:00:00.000′ – 1 hour after midnight

are not included in the two queries above.

*/

– To include the entire day of 2004-03-15 use the following two solutions

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate >= ‘20040301′ AND OrderDate < ‘20040316′

– SQL between with DATE type (SQL Server 2008)

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE CONVERT(DATE, OrderDate) BETWEEN ‘20040301′ AND ‘20040315′

———-

– Non-standard format conversion: 2011 December 14

– SQL datetime to string

SELECT [YYYY Month DD] =

CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ‘ ‘+

DATENAME(MM, GETDATE()) + ‘ ‘ +

CAST(DAY(GETDATE()) AS VARCHAR(2))

– Converting datetime to YYYYMMDDHHMMSS format: 20121214172638

SELECT replace(convert(varchar, getdate(),111),‘/’,”) +

replace(convert(varchar, getdate(),108),‘:’,”)

– Datetime custom format conversion to YYYY_MM_DD

select CurrentDate=rtrim(year(getdate())) + ‘_’ +

right(‘0′ + rtrim(month(getdate())),2) + ‘_’ +

right(‘0′ + rtrim(day(getdate())),2)

– Converting seconds to HH:MM:SS format

declare @Seconds int

set @Seconds = 10000

select TimeSpan=right(‘0′ +rtrim(@Seconds / 3600),2) + ‘:’ +

right(‘0′ + rtrim((@Seconds % 3600) / 60),2) + ‘:’ +

right(‘0′ + rtrim(@Seconds % 60),2)

– Result: 02:46:40

– Test result

select 2*3600 + 46*60 + 40

– Result: 10000

– Set the time portion of a datetime value to 00:00:00.000

– SQL strip time from date

– SQL strip time from datetime

SELECT CURRENT_TIMESTAMP ,DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 0)

– Results: 2014-01-23 05:35:52.793 2014-01-23 00:00:00.000

/*******

VALID DATE RANGES FOR DATE/DATETIME DATA TYPES

SMALLDATETIME date range:

January 1, 1900 through June 6, 2079

DATETIME date range:

January 1, 1753 through December 31, 9999

DATETIME2 date range (SQL Server 2008):

January 1,1 AD through December 31, 9999 AD

DATE date range (SQL Server 2008):

January 1, 1 AD through December 31, 9999 AD

*******/

– Selecting with CONVERT into different styles

– Note: Only Japan & ISO styles can be used in ORDER BY

SELECT TOP(1)

Italy = CONVERT(varchar, OrderDate, 105)

, USA = CONVERT(varchar, OrderDate, 110)

, Japan = CONVERT(varchar, OrderDate, 111)

, ISO = CONVERT(varchar, OrderDate, 112)

FROM AdventureWorks.Purchasing.PurchaseOrderHeader

ORDER BY PurchaseOrderID DESC

/* Results

Italy USA Japan ISO

25-07-2004 07-25-2004 2004/07/25 20040725

*/

– SQL Server convert date to integer

DECLARE @Datetime datetime

SET @Datetime = ‘2012-10-23 10:21:05.345′

SELECT DateAsInteger = CAST (CONVERT(varchar,@Datetime,112) as INT)

– Result: 20121023

– SQL Server convert integer to datetime

DECLARE @intDate int

SET @intDate = 20120315

SELECT IntegerToDatetime = CAST(CAST(@intDate as varchar) as datetime)

– Result: 2012-03-15 00:00:00.000

————

– SQL Server CONVERT script applying table INSERT/UPDATE

————

– SQL Server convert date

– Datetime column is converted into date only string column

USE tempdb;

GO

CREATE TABLE sqlConvertDateTime (

DatetimeCol datetime,

DateCol char(8));

INSERT sqlConvertDateTime (DatetimeCol) SELECT GETDATE()

UPDATE sqlConvertDateTime

SET DateCol = CONVERT(char(10), DatetimeCol, 112)

SELECT * FROM sqlConvertDateTime

– SQL Server convert datetime

– The string date column is converted into datetime column

UPDATE sqlConvertDateTime

SET DatetimeCol = CONVERT(Datetime, DateCol, 112)

SELECT * FROM sqlConvertDateTime

– Adding a day to the converted datetime column with DATEADD

UPDATE sqlConvertDateTime

SET DatetimeCol = DATEADD(day, 1, CONVERT(Datetime, DateCol, 112))

SELECT * FROM sqlConvertDateTime

– Equivalent formulation

– SQL Server cast datetime

UPDATE sqlConvertDateTime

SET DatetimeCol = DATEADD(dd, 1, CAST(DateCol AS datetime))

SELECT * FROM sqlConvertDateTime

GO

DROP TABLE sqlConvertDateTime

GO

/* First results

DatetimeCol DateCol

2014-12-25 16:04:15.373 20141225 */

/* Second results:

DatetimeCol DateCol

2014-12-25 00:00:00.000 20141225 */

/* Third results:

DatetimeCol DateCol

2014-12-26 00:00:00.000 20141225 */

————

– SQL month sequence – SQL date sequence generation with table variable

– SQL Server cast string to datetime – SQL Server cast datetime to string

– SQL Server insert default values method

DECLARE @Sequence table (Sequence int identity(1,1))

DECLARE @i int; SET @i = 0

DECLARE @StartDate datetime;

SET @StartDate = CAST(CONVERT(varchar, year(getdate()))+

RIGHT(‘0′+convert(varchar,month(getdate())),2) + ‘01′ AS DATETIME)

WHILE ( @i < 120)

BEGIN

INSERT @Sequence DEFAULT VALUES

SET @i = @i + 1

END

SELECT MonthSequence = CAST(DATEADD(month, Sequence,@StartDate) AS varchar)

FROM @Sequence

GO

/* Partial results:

MonthSequence

Jan 1 2012 12:00AM

Feb 1 2012 12:00AM

Mar 1 2012 12:00AM

Apr 1 2012 12:00AM

*/

————

————

– SQL Server Server datetime internal storage

– SQL Server datetime formats

————

– SQL Server datetime to hex

SELECT Now=CURRENT_TIMESTAMP, HexNow=CAST(CURRENT_TIMESTAMP AS BINARY(8))

/* Results

Now HexNow

2009-01-02 17:35:59.297 0×00009B850122092D

*/

– SQL Server date part – left 4 bytes – Days since 1900-01-01

SELECT Now=DATEADD(DAY, CONVERT(INT, 0×00009B85), ‘19000101′)

GO

– Result: 2009-01-02 00:00:00.000

– SQL time part – right 4 bytes – milliseconds since midnight

– 1000/300 is an adjustment factor

– SQL dateadd to Midnight

SELECT Now=DATEADD(MS, (1000.0/300)* CONVERT(BIGINT, 0×0122092D), ‘2009-01-02′)

GO

– Result: 2009-01-02 17:35:59.290

————

————

– String date and datetime date&time columns usage

– SQL Server datetime formats in tables

————

USE tempdb;

SET NOCOUNT ON;

– SQL Server select into table create

SELECT TOP (5)

FullName=convert(nvarchar(50),FirstName+‘ ‘+LastName),

BirthDate = CONVERT(char(8), BirthDate,112),

ModifiedDate = getdate()

INTO Employee

FROM AdventureWorks.HumanResources.Employee e

INNER JOIN AdventureWorks.Person.Contact c

ON c.ContactID = e.ContactID

ORDER BY EmployeeID

GO

– SQL Server alter table

ALTER TABLE Employee ALTER COLUMN FullName nvarchar(50) NOT NULL

GO

ALTER TABLE Employee

ADD CONSTRAINT [PK_Employee] PRIMARY KEY (FullName )

GO

/* Results

Table definition for the Employee table

Note: BirthDate is string date (only)

CREATE TABLE dbo.Employee(

FullName nvarchar(50) NOT NULL PRIMARY KEY,

BirthDate char(8) NULL,

ModifiedDate datetime NOT NULL

)

*/

SELECT * FROM Employee ORDER BY FullName

GO

/* Results

FullName BirthDate ModifiedDate

Guy Gilbert 19720515 2009-01-03 10:10:19.217

Kevin Brown 19770603 2009-01-03 10:10:19.217

Rob Walters 19650123 2009-01-03 10:10:19.217

Roberto Tamburello 19641213 2009-01-03 10:10:19.217

Thierry D’Hers 19490829 2009-01-03 10:10:19.217

*/

– SQL Server age

SELECT FullName, Age = DATEDIFF(YEAR, BirthDate, GETDATE()),

RowMaintenanceDate = CAST (ModifiedDate AS varchar)

FROM Employee ORDER BY FullName

GO

/* Results

FullName Age RowMaintenanceDate

Guy Gilbert 37 Jan 3 2009 10:10AM

Kevin Brown 32 Jan 3 2009 10:10AM

Rob Walters 44 Jan 3 2009 10:10AM

Roberto Tamburello 45 Jan 3 2009 10:10AM

Thierry D’Hers 60 Jan 3 2009 10:10AM

*/

– SQL Server age of Rob Walters on specific dates

– SQL Server string to datetime implicit conversion with DATEADD

SELECT AGE50DATE = DATEADD(YY, 50, ‘19650123′)

GO

– Result: 2015-01-23 00:00:00.000

– SQL Server datetime to string, Italian format for ModifiedDate

– SQL Server string to datetime implicit conversion with DATEDIFF

SELECT FullName,

AgeDEC31 = DATEDIFF(YEAR, BirthDate, ‘20141231′),

AgeJAN01 = DATEDIFF(YEAR, BirthDate, ‘20150101′),

AgeJAN23 = DATEDIFF(YEAR, BirthDate, ‘20150123′),

AgeJAN24 = DATEDIFF(YEAR, BirthDate, ‘20150124′),

ModDate = CONVERT(varchar, ModifiedDate, 105)

FROM Employee

WHERE FullName = ‘Rob Walters’

ORDER BY FullName

GO

/* Results

Important Note: age increments on Jan 1 (not as commonly calculated)

FullName AgeDEC31 AgeJAN01 AgeJAN23 AgeJAN24 ModDate

Rob Walters 49 50 50 50 03-01-2009

*/

————

– SQL combine integer date & time into datetime

————

– Datetime format sql

– SQL stuff

DECLARE @DateTimeAsINT TABLE ( ID int identity(1,1) primary key,

DateAsINT int,

TimeAsINT int

)

– NOTE: leading zeroes in time is for readability only!

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 235959)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 010204)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 002350)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000244)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000050)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000006)

SELECT DateAsINT, TimeAsINT,

CONVERT(datetime, CONVERT(varchar(8), DateAsINT) + ‘ ‘+

STUFF(STUFF ( RIGHT(REPLICATE(‘0′, 6) + CONVERT(varchar(6), TimeAsINT), 6),

3, 0, ‘:’), 6, 0, ‘:’)) AS DateTimeValue

FROM @DateTimeAsINT

ORDER BY ID

GO

/* Results

DateAsINT TimeAsINT DateTimeValue

20121023 235959 2012-10-23 23:59:59.000

20121023 10204 2012-10-23 01:02:04.000

20121023 2350 2012-10-23 00:23:50.000

20121023 244 2012-10-23 00:02:44.000

20121023 50 2012-10-23 00:00:50.000

20121023 6 2012-10-23 00:00:06.000

*/

————

– SQL Server string to datetime, implicit conversion with assignment

UPDATE Employee SET ModifiedDate = ‘20150123′

WHERE FullName = ‘Rob Walters’

GO

SELECT ModifiedDate FROM Employee WHERE FullName = ‘Rob Walters’

GO

– Result: 2015-01-23 00:00:00.000

/* SQL string date, assemble string date from datetime parts */

– SQL Server cast string to datetime – sql convert string date

– SQL Server number to varchar conversion

– SQL Server leading zeroes for month and day

– SQL Server right string function

UPDATE Employee SET BirthDate =

CONVERT(char(4),YEAR(CAST(‘1965-01-23′ as DATETIME)))+

RIGHT(‘0′+CONVERT(varchar,MONTH(CAST(‘1965-01-23′ as DATETIME))),2)+

RIGHT(‘0′+CONVERT(varchar,DAY(CAST(‘1965-01-23′ as DATETIME))),2)

WHERE FullName = ‘Rob Walters’

GO

SELECT BirthDate FROM Employee WHERE FullName = ‘Rob Walters’

GO

– Result: 19650123

– Perform cleanup action

DROP TABLE Employee

– SQL nocount

SET NOCOUNT OFF;

GO

————

————

– sql isdate function

————

USE tempdb;

– sql newid – random sort

SELECT top(3) SalesOrderID,

stringOrderDate = CAST (OrderDate AS varchar)

INTO DateValidation

FROM AdventureWorks.Sales.SalesOrderHeader

ORDER BY NEWID()

GO

SELECT * FROM DateValidation

/* Results

SalesOrderID stringOrderDate

56720 Oct 26 2003 12:00AM

73737 Jun 25 2004 12:00AM

70573 May 14 2004 12:00AM

*/

– SQL update with top

UPDATE TOP(1) DateValidation

SET stringOrderDate = ‘Apb 29 2004 12:00AM’

GO

– SQL string to datetime fails without validation

SELECT SalesOrderID, OrderDate = CAST (stringOrderDate as datetime)

FROM DateValidation

GO

/* Msg 242, Level 16, State 3, Line 1

The conversion of a varchar data type to a datetime data type resulted in an

out-of-range value.

*/

– sql isdate – filter for valid dates

SELECT SalesOrderID, OrderDate = CAST (stringOrderDate as datetime)

FROM DateValidation

WHERE ISDATE(stringOrderDate) = 1

GO

/* Results

SalesOrderID OrderDate

73737 2004-06-25 00:00:00.000

70573 2004-05-14 00:00:00.000

*/

– SQL drop table

DROP TABLE DateValidation

Go

————

– SELECT between two specified dates – assumption TIME part is 00:00:00.000

————

– SQL datetime between

– SQL select between two dates

SELECT EmployeeID, RateChangeDate

FROM AdventureWorks.HumanResources.EmployeePayHistory

WHERE RateChangeDate >= ‘1997-11-01′ AND

RateChangeDate < DATEADD(dd,1,‘1998-01-05′)

GO

/* Results

EmployeeID RateChangeDate

3 1997-12-12 00:00:00.000

4 1998-01-05 00:00:00.000

*/

/* Equivalent to

– SQL datetime range

SELECT EmployeeID, RateChangeDate

FROM AdventureWorks.HumanResources.EmployeePayHistory

WHERE RateChangeDate >= ‘1997-11-01 00:00:00′ AND

RateChangeDate < ‘1998-01-06 00:00:00′

GO

*/

————

– SQL datetime language setting

– SQL Nondeterministic function usage – result varies with language settings

SET LANGUAGE ‘us_english’; –– Jan 12 2015 12:00AM

SELECT US = convert(VARCHAR,convert(DATETIME,‘01/12/2015′));

SET LANGUAGE ‘British’; –– Dec 1 2015 12:00AM

SELECT UK = convert(VARCHAR,convert(DATETIME,‘01/12/2015′));

SET LANGUAGE ‘German’; –– Dez 1 2015 12:00AM

SET LANGUAGE ‘Deutsch’; –– Dez 1 2015 12:00AM

SELECT Germany = convert(VARCHAR,convert(DATETIME,‘01/12/2015′));

SET LANGUAGE ‘French’; –– déc 1 2015 12:00AM

SELECT France = convert(VARCHAR,convert(DATETIME,‘01/12/2015′));

SET LANGUAGE ‘Spanish’; –– Dic 1 2015 12:00AM

SELECT Spain = convert(VARCHAR,convert(DATETIME,‘01/12/2015′));

SET LANGUAGE ‘Hungarian’; –– jan 12 2015 12:00AM

SELECT Hungary = convert(VARCHAR,convert(DATETIME,‘01/12/2015′));

SET LANGUAGE ‘us_english’;

GO

————

————

– Function for Monday dates calculation

————

USE AdventureWorks2008;

GO

– SQL user-defined function

– SQL scalar function – UDF

CREATE FUNCTION fnMondayDate

(@Year INT,

@Month INT,

@MondayOrdinal INT)

RETURNS DATETIME

AS

BEGIN

DECLARE @FirstDayOfMonth CHAR(10),

@SeedDate CHAR(10)

SET @FirstDayOfMonth = convert(VARCHAR,@Year) + ‘-’ + convert(VARCHAR,@Month) + ‘-01′

SET @SeedDate = ‘1900-01-01′

RETURN DATEADD(DD,DATEDIFF(DD,@SeedDate,DATEADD(DD,(@MondayOrdinal * 7) - 1,

@FirstDayOfMonth)) / 7 * 7, @SeedDate)

END

GO

– Test Datetime UDF

– Third Monday in Feb, 2015

SELECT dbo.fnMondayDate(2016,2,3)

– 2015-02-16 00:00:00.000

– First Monday of current month

SELECT dbo.fnMondayDate(Year(getdate()),Month(getdate()),1)

– 2009-02-02 00:00:00.000

————

ASP.NET