.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links


Posted By:      Posted Date: September 23, 2010    Points: 0   Category :Sql Server

Input Data: 2010-03-23 00:00:00

Data Type: STRING

Expression Used: SUBSTRING(@[User::ExtractionDate],1,4) + "-" + SUBSTRING(@[User::ExtractionDate],6,2) + "-" + SUBSTRING(@[User::ExtractionDate],9,2) 


Current Output - 2010-03-23


However, my requirement is to get time as well 00:00:00, but only if they are 00:00:00 after the date in side the row.

OutPut Required only: 2010-03-23 00:00:00








View Complete Post

More Related Resource Links

URGENT HELP GUYS - Expression Request

Hi Guys,   I got a data coming through from derived column like this  192752_20052010 I want an expression to above column change to get only the last part to change like this 192752_20100520   Please help guys it urgent   Thanks,   D  

Need Regular Expression for d/mm/yyyy hh:mm:ss

Hi everybody,i am stuck in generating a regular expresion for this typem/dd/yyyy hh:mm:ssbecause i am dealing with some date values, for example i have  date values like bellow, 3/29/2007 13:28:343/27/2007 17:12:36so i need to find out a regular expression type in order to validate this, i already got some regular expression string like this ,^(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)[0-9]{2}$but this is working only for 03/29/2007but if i use 3/29/2007 it wont work , or if use 03/27/2007 17:12:36 still it wont work so i really need you guys help on this to generate my regular expression string thanksregrdssukavi

Arithmetic overflow error converting expression to data type smallint

Can anyone tell me what exactly this means? Arithmetic overflow error converting expression to data type smallint. 

Data type mismatch in criteria expression.

Protected Sub btnSave_OnClick(ByVal sender As Object, ByVal e As EventArgs) Dim StartDateYear As Integer = Int32.Parse(2000 + tbStartDateYear.Text) Dim StartDateMonth As Integer = Int32.Parse(ddlStartDateMonth.SelectedValue) Dim StartDateDay As Integer = Int32.Parse(ddlStartDateDay.SelectedValue) Dim startDate As Date = New Date(StartDateYear, StartDateMonth, StartDateDay) Dim folderStartDate As String = Convert.ToDateTime(startDate).ToString("dd.mm.yyyy") Dim projectName As String = tbProjectName.Text Dim projectCategory As String = ddlProjectCategory.Text Dim projectID As String = "" Dim objConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=|DataDirectory|MOPPS.mdb;") Dim insertSQL As String = "INSERT INTO Projects([Project Title], [Start Date], [Project category]) VALUES (@projectName, @startDate, @projectCategory);" Dim insertCommand As New OleDbCommand(insertSQL, objConnection) Dim IDSQL As String = "SELECT [ProjectID] FROM Projects WHERE [Project Title]='" & projectName & "' AND [Start Date]='" & startDate & "' AND [Project category]='" & projectCategory & "';" Dim IDCommand As New OleDbCommand(IDSQL, objConnecti

Expression Help -URGENT

Hi Guys, I am using foreeach loop and using data flow  task to insert data into table. I got two columns FileName and Filepart both data type are string. However I got a issue with data conversion.  Input Data = String Column Name = FileName Sample Data   FileName ProductCatalog_180915_20100518_201099.csv ProductCatalog_180915_20100520_201099.csv ProductCatalog_180915_04062010_201077_201082_04134.csv   I am using following expression for file part REPLACE(SUBSTRING(@[User::FileName],1, FINDSTRING( ( @[User::FileName] ), "_", 3) -1), ".CSV", "")   Output getting: ProductCatalog_180915_20100520 ------- Correct Format ProductCatalog_180915_04062010 ------- Wrong Format   I understand FileName from foreeach is getting that info, but before I insert that data into temptable. I want all my data in this format. ProductCatalog_180915_20100520   Can anyone shed some light how to over come this issue. Do I need to get data individually to each column (e.g. 04062010) only and change it or else can I use derived column. If I get individually how to identify which one is this (e.g. 04062010)  and this (e.g. 20100520)  thanks,   D 

Urgent: The business data connectivity metadata store is currently unavailable SP designer

Hi,   I am getting the following error when runnind SP designer and referencing external content types.   The BDC service is running on the server, however if I click the link to configure the service I get:   The server did not provide a meaningful reply; this might be caused by a contract mismatch, a premature session shutdown or an internal server error. and in the event log: The BDC Service application BDCTest is not accessible. The full exception text is: The server did not provide a meaningful reply; this might be caused by a contract mismatch, a premature session shutdown or an internal server error. An exception occurred when trying to issue security token: The server did not provide a meaningful reply; this might be caused by a contract mismatch, a premature session shutdown or an internal server error.   Can anyone please help?        

Urgent How to Create it ,Object Data Source ,Linq and sql or other any way


my query is



my data is

HR    HR    Rec    Records    Leave    Leave Records    HRL.0001    Policy and Process for leave record.    27.06.2010    30.06.2010    WIP    Edlink
HR    HR    Rec    Records    Leave    Leave Records    HRL.0002    Quarterly Review    01.07.2010    04.07.2010    Planned    Edlink
HR    HR    DB    Database    SAL    Salary    DDS.0001    Calculation of Monthly salary    08.08.2010    09.08.2010    WIP    Edlink
HR    HR    Rec    Records    Ind    Induction Records    HRI.0001    Completion of dossiers.    08.07.2010    10.07.2010  

Urgent Expression Request - Date Conversion


Input Date : 4/08/2010 6:27:00 AM


Output Required: 2010/08/04 


Expression used : SUBSTRING(@[User::ExtractionDate],1,FINDSTRING(@[User::ExtractionDate],"/",1) - 1) + "-" + SUBSTRING(@[User::ExtractionDate],FINDSTRING(@[User::ExtractionDate],"/",1) + 1,2) + "-" + SUBSTRING(@[User::ExtractionDate],FINDSTRING(@[User::ExtractionDate],"/",2) + 1,4)

Current Date is getting: 4-08-2010




Data type mismatch in criteria expression: date



I am getting a "Data type mismatch in criteria expression" error when trying the following SELECT statement.

strdt and spdate are Date types in my vb codebehind and the Contract field name is a DateTime field.

thanks for your help



nmxSQL As String = "SELECT * FROM Nymex WHERE (NymexID='" & nymID & "' And Contract>='" & strdt & "' And Contract<='" & spdate & "')"

Data Grid and HTML Encoding Issues, Need Urgent Help


Hi All,

I am facing an issue. I have a datagrid in aspx page. I am binding a dataset data to this datagrid [simple binding]. It works with out any issues. However whenever the data contains any Html tags like 'Test Data With <HTML> tags'
the data in the datagrid cell will show only 'Test Data With <> tags'. The contents inside <> tag will disappear.

My code will look like this.

DataGrid.DataSource = SomeDataSet

I am using C# as the code behind language. Also the data population to the grid is dynamic and depends on the SQL Query used for it. Hence my columns are not predefined. The DataGrid's AutoGeneratedColumns property is True [Number of columns and columns header will change dynamically depending on the query which pulls the data from the DB]

Kindly help with this.

Thanks in advance

Expression box only shows the first row of data in a repeating table...

Expression box only shows the first row of data in a repeating table, how can I display all rows of a repeating table in an expression box, preferably without code?

How to retrieve conditional XML data using XPath Expression

I have a XMLDataSource somewhat like:

How do I get data from the first form to another - urgent!!!


I can not find solution how to get control of other forms.

In the first form Default.aspx I have TextBox1 control.
How do I get data from TextBox1 into another form Default2.aspx?

Is there any example on the Internet?

I have tried in this direction, but not a solution:
$ (Function () {
                        $ ('# Btn'). Click (function () {
                            $. Ajax ({
                                url: "Default.aspx/TextBox1"
                                data: "{}",
                                success: function (html) {

showing Wrong Data while loading excel sheet, need help urgent



I am trying to load data from an excel sheet to a table in sql server. in the excel-source when i press preview button data for one column in the excel sheet is coming as null. the column has numbers and the format of the cell is 'general'..? what could be wrong and how could i resolve.? need help urgently..?

Arithmetic overflow error converting expression to data type int.


I have a table with a row called row1, this row1 is a bigint and has values from 0  To  4278190080


I am running a query such as: select top 1 * from tableA where row1 <= (((73*256+62)*256+53)*256 + 12)


How can I get this to work? I tried to do where cast(row1 as float) <= ....... but I get the same error.

How to pass control reference to its property in data binding expression


Here is data binding expression (dropdown list is nested in gridview)

<asp:DropDownList ID="ddlEmployees" 
         DataSource='<%# PayCheckBO.GetEmployeesNameIdPair() %>' 
         SelectedIndex='<%# GetSelectedIndex(HOW TO PASS REF of ddlemployee HERE)%>'/>

Code Behind has function which looks like

public int GetSelectedIndex(DropDownList ddl)
        //loop through the item in the dropdownlist
        //return index based on xyz checks

The whole reason for passing the reference is to save round trip to database and not to use SESSION

Reading data from a regular expression


Hello everyone,

I have some problems by reading data from a reqular expression.

Regex rTitle = new Regex("<td><b class=\"title\">(.*)</b></td>", RegexOptions.IgnorePatternWhitespace);
                        MatchCollection mclTitle = rTitle.Matches(myString);

In actual fact, I preferably like only the data from the (.*) part of the expression. Can anyone help me?

Thank you a lot!


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend