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

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

Date Covertion from Char field to a proper format

Posted By:      Posted Date: October 28, 2010    Points: 0   Category :Sql Server

Hi Gurus,

There is a field in a table with type and length as varchar(255).  It stores a value as follows 'Patch_Test_9_10_201012;00;00AM'.  I need to extract 9_10_201012;00;00AM and format it as 9/10/2010 12:00:00 AM.  I tried CONVERT and CONVERT+CAST it won't work.

Is there a way to do it? Please, any ideas , inputs are wellcome


View Complete Post

More Related Resource Links

how can I make field with date in format: dd/mm/yyyy ?

I have in model:[DisplayName("Publication date")] [DisplayFormat(DataFormatString = "{0:dd/MM/yyyy}", ApplyFormatInEditMode = true)] [Required(ErrorMessage = "Publication date is required.")] public DateTime publication_date { get; set; } And in view:<div> <%: Html.LabelFor(model => model.publication_date) %> </div> <div> <%: Html.TextBoxFor(model => model.publication_date, new { @class = "datepicker" })%> <%: Html.ValidationMessageFor(model => model.publication_date) %> </div> But when I write in this field date: 30/11/2009 I see validation error: The value '30/11/2009' is not valid for publication_date.

How do I format a date time field in a CQWP to YY-MM-DD ?

Hi. I have been trying to get the date-time to look good for some time now, but have failed. I have a Content Query web part that picks up article pages from a page library. The pages contains a field called 'article date'. I want to show just the 'Title' and the 'Article date'. Doing this default gives me: My title 10-09-03 00:00:00 I want it to be: My title 10-09-03 Is this possible? I'm starting to realize that I probably have to edit the xsl styles for this webpart but if thats the answer, how do I do that? ThanksThomas Balkeståhl - Technical Specialist - SharePoint

Why can I not group by calculated field in Date format?

After migrating to SP2010 Foundation, I've discovered that for some reason I can't group a list by a calculated date field. Ex: Create a list with a date column A. Create another column B with a simple calculated date of [A]+1 and use the field format type 'date' Modify view to group the list by column B.  You're list will load, but when you attempt to expand one of the groups you'll be greated with an exception error. Why?? Am I doing something wrong? Note: You can group fine if the data type for column B is anything except 'date' format, but I really need to group items by a calulated date. Ultimately, what I'm trying to do is group my list items by week by calculating the end of the week using a simple formula =[Must ship by]+7-WEEKDAY([Must ship by])  then I want to gropu by the [Must Ship by] column like I always did in SP 2007, but now it errors out.  I also wanted to create my own timesheet template since the Fab 40 templates aren't available for 2010, but I can't really do that effectively without being able to group timesheet entries by week.  

date not getting stored in proper format in vb 6.0 and sql server 2000

i have a form which accept date in dd/mm/yyyy format from the user but  when i am storing it into sql server , some time it is getting stored in
dd/mm/yyyy format and some time in mm/dd/yyyy format. When i try to retrive date from sql server  and fetch it into text box it is geting  displayed in other format . some time in dd/mm/yyyy  and  mm/dd/yyyy format .
my reginal date setting is in mm/dd/yyyy format .
will be great if i get a quick help on this issue

How To Set a Date Format In GridView Using ASP.NET 2.0

A very common desire is to set a column of a gridview to display just the month, day and year of a DateTime type. The problem is the by default, the HtmlEncode property of the boundfield attribute (
The problem is that if this field is enabled, you can not pass format information to the boundfield control. That is, if you try the following code, you will not get the desired result.

How to format datetime & date with century?

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


How to convert Date format from dd/MM/yyyy to mm/dd/yyyy in asp.net



         I am using a text box which captures the date from the datepicker and displays in dd/MM/yyyy format in asp.net using C#

When i try to pass this text box value into the DatTime variable i am getting the error as

"String was not recognized as a valid DateTime. "


my code goes like this

DateTime dt;




i tried to use Conver.ToDateTime() function but the same error is croping up..


Can any one plz suggest me on this.....Thnks in advance

Insert DATE to database (format 'YYYY-MM-DD')

How can I insert BIRTHDATE into column in 'YYYY-MM-DD' format. This is my code, can anyone edit my code? Tnx String = "INSERT into concfile VALUES(BIRTHDATE('" & DateTimePicker1.Text & "', 'YYYY-MM-DD'))"

Updating a List item field value in a SPD Workflow is setting Date fields to null

Hello everybody,I faced a very weird behavior while creating a workflow in SPD against a Document Library.Here is the case:1- Create a document library in sharepoint site.2- Add a custom column of type DATE.3- Upload any document to the library4- Edit the item metadata and set the custom date field to any date you want.5- Now using sharepoint designer, create a very simple Workflow with only one action that updates the current item "Title" field to any value. (Note: In my case I set the workflow to start manually and not automatically)6- Save the workflow and then start it on the uploaded item.7- Now check the value of the date column after the workflow is finished.8- You will see that the Date field is Empty... (very weird)Notes:   1-This behavior will not appear on a standard list. it is only appearing in a Document library.  2- I am using WSS 3.0 SP2 + MOSS 2007 SP2. Also SharePoint designer 2007 SP2Did anyone face this situation and does it have a fix, or am I doing something wrong here... Please help.Regards,Maheed

Calculated date field displays differently in List View and Data View web parts

In SharePoint GUI I created a simple custom list with a calculated Date/Time field (Due).  The calculation is =[Created]+7.  I'm trying to display this data on a page using a DVWP.  But first, for testing purposes, I also created a second calculated Date/Time (TestCreate) field in my custom list that is simply =[Created]. In the List View from within SharePoint, the dates show as expected (for example): Created = 8/12/2010 3:45 PM Due = 8/19/2010 3:45 PM TestCreate = 8/12/2010 3:45 PM In SharePoint Designer, I added a Data View web part and the above three data elements (each configured to display mm/dd/yyyy hh:mm:ss tt).  I see the time displayed for the example list item as: Created = 8/12/2010 3:45:30 PM Due = 8/19/2010 10:45:00 AM TestCreate = 8/12/2010 10:45:00 AM I've tested a couple scenarios and the calculated fields always display five hours in the past (along with dropping the seconds).  It seems like the DVWP is returning the date in a different time zone (with no seconds).  The lcid is set right (1033).  I didn't see any time zone settings for the DVWP specifically.  I'm basically stuck.  Could someone help me figure this one out. Thanks.

Proper upgrade path for out of date portal

I have a MOSS 2007 Portal that is woefully neglected.  A quick glance showed that its version was  The table below list all the current updates. Cumulative Update KB974989 & KB974988 Oct 2009 Cumulative Update KB973410 & KB973409 Oct 2009 Cumulative Update KB971538 & KB972569 Jul 2009 Cumulative Update KB968857, KB968850, &KB968851 Jun 2009 SP2 April 2009 Cumulative Update KB963654 & KB961755 Feb 2009 Cumulative Update KB960010 & KB960011 Dec 2008 Cumulative Update KB956056 & KB956057 Aug 2008 Infrastructure Update KB951695 & KB951297 Jul 2008 SP1 + KB948945 Feb 2008 SP1 + KB941274 Jan 2008 SP1 + KB941422 Jan 2008 SP1 October 2007 public update   Do I have to apply them all in order or can I simply jump straight to the cumulatives in order?  Are there any of other considerations I should be aware of?

Proper upgrade path for out of date SQL instance

I currently have a SQL backend that is feeding an out of date MOSS 2007 instance.  I have found the following: Version Information   Version 9.00.4053.00 Command: Select @@Version Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)   May 26 2009 14:24:20   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2) Version Number KB Article Link Release Date 9.00.4305 SQL Server 2005 SP3 CU10  June 2010 9.00.4294 SQL Server 2005 SP3 CU9 Apr 2010 9.00.4285 SQL Server 2005 SP3 CU8 Feb 2010 9.00.4273 SQL Server 2005 SP3 CU7 Dec 2009 9.00.4266 SQL Server 2005 SP3 CU6 Oct 2009 9.00.4230 SQL Server 2005 SP3 CU5 Aug 2009 9.00.4226 SQL Server 2005 SP3 CU4 June 2009 9.00.4220 SQL Server 2005 SP3 CU3 Apr 2009 9.00.4211 SQL Server 2005 SP3 CU2 Feb 2009 9.00.4207 SQL Server 2005 SP3 CU1 Dec 2008 9.00.4053 SQL Server 2005 SP3 GDR (Security Update) Oct 2009 9.00.4035 SQL Server 2005 SP3 Dec 2008 9.00.3356 SQL Server 2005 SP2 CU17 Dec 2009 Whats the best approach to updating the SQL instance?  Do them in order or can I use the latest cumulative?  Any other issues I should be concerned about?

proper format for number column

Hi  In a column i have number datatype but after filling some no it's giving like that 23,786,23 (mostly like a currency format ) but i need like that 2378623 so give me suggestion how i can implement this in sharepoint 2010 , is there need to bound some column level validation for that ............... 

Format Date in TextBoxFor

I've been trying to format the date in a view form in a textboxfor and it seems everything I try doesn't seem to work. I found this great partialview example but it doesn't work when applied to EditorFor. Again works great when using the DisplayFor HTML helper method but still spits out the time at the end when trying to format for a textbox.If anyone has any suggestions or answers I would be greatly appreciative.Jim

Date format for Year, Year-Month, and Date (Year-Month-Day) where input is not known

I have a requirement to capture historical events in a table. Now some events will have only Year, some Year/Month, and remaining where Date is known. We should be able to store them in the column and be able to sort etc. When publishing the information, we should be able to publish as was the input, Year only, Year-Month only, or Date. I looked at the newer Date data type. I can insert a 4 digit year, but on retrieving it is YYYY-01-01. I do not know if it has input YYYY or YYYY-MM-DD and is indeed a Date or a Year. I am trying to avoid saving the format information in another column or something. For sorting, when records have same Year, I would have another column to do relative sorting... So question is - what are my best options with SQL/Entity Framework combo. And what others have done, when encountering similar - if any. Thanks in advance. --Sharad 

how to change date format

Hi,   I am using Oracle 10g im my asp.net project. In my select query, i want to change the format to dd/mm/yyyy hh:mm:ss from mm/dd/yyy hh:mm:ss. The field datatype is date. How can i write a query for this?

How to get a date in ISO format in DataViewWebPart

Is it possible to get date in ISO format (2005-08-23T18:31:42Z) inside DataViewWebPart in SharePoint 2010? I am only able to get the date in my locale, which doesn't sort properly. In SharePoint 2007 there was no such problem, but in 2010 it is a very annoying one.
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