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


Top 5 Contributors of the Month
Shivakumaran
hoangnganvy1993
Post New Web Links

How to convert a column of "Apr 14 2004 12:00AM" nvarchar format to "14-Apr-2004" nvarchar format in

Posted By:      Posted Date: November 02, 2010    Points: 0   Category :Sql Server
 

I have couple columns with "Apr 14 2004 12:00AM" nvarchar format in a table. I need to convert to "14-Apr-2004" nvarchar format. How can I do that? These are not in datetime format, and I have to get the 12:00am timing out of the column. There are many columns with many rows of data. Hope someone give me some idea, thanks!




View Complete Post


More Related Resource Links

Convert NVARCHAR column to DateTime - SQL Server

  

Hi there,

Have spent few hours to get this working. I like to get help now. Please note MONTH AND DATES stored in single or double digits as they occur.

Unfortunately DATE TIME is stored in NVARCHAR Data type column as 2/9/2010,  22/10/2010 etc. Following UK Date here.

It is required to pull the data to COUNT No. of Expired. Obviously below one fails.

--AS Date AND Month stored in Single digits AND double digits , below statement 
--thorws Arthmetic overflow exception
SUM(CASE WHEN CONVERT(DATETIME,ExpiryDate,103) >= (CONVERT(datetime,'2010-01-07',103)) AND    
 CONVERT(DATETIME,ExpiryDate,103) < dateadd(month,1,(convert(datetime,'2010-01-09', 103))) THEN 1 ELSE 0 END) AS Expired  


 


Convert NVARCHAR column data into DATETIME - SQL Server 2000

  

ExpiryDate column in Table Data Type is NVARCHAR , storing Dates ONLY without time as 2/10/2010 , 12/10/2010 IN DD/MM/YYYY FORMAT.

NOW IT IS REQUIERD TO RETRIEVE ALL RECORDS WHERE EXPIRYDATE BETWEEN USER PASSED START MONTH,START YEAR AND END MONTH, END YEAR.

USING SQL SERVER 2000

SUM(CASE WHEN CONVERT(datetime,a.expirydate,103) >= (CONVERT(datetime,'2010-01-07',103)) AND 
		CONVERT(DATETIME,a.ExpiryDate,103) < dateadd(month,1,(convert(datetime,'2010-01-09', 103))) THEN 1 ELSE 0 END) AS Expired

 

Your help is greatly appreciated. Spent hours to get this resolved.


A

Bug: Does not change column type from nvarchar to ntext

  

Scenario: If you create a table with a column of type nvarchar, populate it with some content and try to modify it using the WebMatrix editor, from nvarchar to ntext, it does not display any error message or warning that this is not possible. It silently saves and reverts the change, back to nvarchar. Wasted some hours on this issue as I thought my column was ntext, but in fact was still nvarchar.

Expected behavior: It should change to ntext, or if SQL CE doesn't support that, it should notify the user that no change was applied.


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

  

Hi,

         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;

dt=DateTime.Parse(TextBox1.Text);

Response.Write(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


can i use nvarchar(300) for a column having records as website links?

  
Hi guys, small question. is it good to use nvarchar(300) for a column having records as website links. i was told the size may exceed 255 characters. so just thinking is it possible?

Automatically convert portrait format to landscape

  
Good morning, I am looking for a code to automatically print a report in landscape format if it does not fit into a portrait, something like 'If the wide of the report is larger than the longer, print landscape, else print portrait' It seems easy but I do not find anything to do so, thank you for your response Claude from Québec    

Want to convert html page into pdf format & send same pdf as a attachment

  
Dear All i want to convert html page into pdf format & i have to send that same pdf  by attachment,if some one has the sample code , please send me

How to convert seconds to HH:MM:ss format

  
Write a C# program that prompts the user to input the elapsed time for an event in seconds. The program then outputs the elapsed time in hours, minutes, and seconds. (for example, if the elapsed time is 9630 seconds, then the output is 2:40:30.)Can anyone pls help me!!Im using visual studio 2005

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 ............... 

Trouble importing strange column format report from Excel 2007 to SQL 08

  
Hi, I'm not sure whether this belongs in this section or the SSIS one so hopefully I've got it right!  Hoping someone will be able to help with a problem I'm having importing a report with header and detail rows from our antiquated POS system into SQL 2008 tables.  The reports export in Excel 2007 format and for each header row there can be one or more detail rows starting from column B.  As such I've tried several things such as openrowset, an Access 12.0 OLE DB source in SSIS and even COM automation of Excel to try and write a conditional split which will hold the header details in variables then write them to rows alongside the detail rows.  I've pasted a small sample of the data here as I wasn't able to attach it: 01/07/2010 @ 11:18 Page: 1 RECEIVING: Voucher Journal Sort: VC|Str|Vou Date|Vou #|Document SID Filter: Voucher Date: 01/06/2010@12:00a..30/06/2010@11:59p Include Item detail: DCS|Item#|Desc1|Attr|Material|Size|Qty VC Str Vou Date Vou # Qty ABC 001 15/06/2010 12345 38 E NN 66 200148 XXXXXXXX RED SHAD E NN 66 200149 YYYYYYYYY BLACK GO E PP 60 200154 ZZZZZZZZZ BLACK CDE 002 16/06/2010 13839 8 F SA 35 217500 XXXXXXXX Natural F SA FL 218674 YYYYYYYYY Chalk F SA WE 221462 ZZZZZZZZZ White FGH 001 21/06/2010 13905 3 F SH 85 126260 XXXXXXXX Navy IJK 001 23/06/2010 13914 3 E AA 61 250005 YYYYYY

How to convert raster Image to Vector image format

  
I want to covert raster image to vector image format in  C#is it possible ??Thanks

how to convert the text to mp3 speech format

  
im working on a applicatio that will speak out a text file for you. i have code to read a text file but can not directly convert and save in mp3 format.. need help.. :)

Searching a NVarChar Column with multiple wildcards returns all rows

  
I'm building a stored procedure to return a set of records, yes nothing big. The column is an NVarchar column and I'm using a select statement of Select * from Table1 where Column1 Like @Column1 Table is currently one record containing the words:  ***Some Test Word***  Is my test word I've set the value of @Column1 to: Some - no records returned Some% - no records returned %Some% - 1 record returned  % - 1 record returned *% - no records returned %*% - 1 record returned %Not Here% - 1 record returned   Can someone tell me why if I have a leading and trailing wildcard I will get all records returned?  Does it have something to do with the '*' characters in the field because some of my users are using these characters. I've also tried changing the select to: where RTRIM(Column1) Like @Column1 with the same result and where RTRIM(Column1) Like N'%' + @Column1 with the same result   What way should I give a user the way to search for a substring inside of an NVarchar field? Oh, I tried using the Substring function and got the same result. Thanks Mike    

Need to convert query string(date - 09/15/2010) to a text format - yyyymmdd

  
I am fairly new to C # and need some assistance with a form page.  I have created a simple form that requires the user to select a beginning date and ending date.  I then pass the values to my code behind page via query strings.  I have them passing, but I need the format to be change to a different format.  Unfortunately the database does not use the standard date format.  It requires the date to be in a text format as YYYYMMDD.Form: <tr> <td> Beginning Date:</td> <td><asp:TextBox runat="server" ID="begDate"></asp:TextBox> <asp:Image ID="Image2" ImageUrl="~/images/calendar_schedulehs.png" runat="server" /></td> </tr> Code Behind: protected void createReport_Click(object sender, EventArgs e) { StringBuilder url = new StringBuilder("Report/TopCustomer.aspx?"); url.Append("BegDate=" + begDate.Text); url.Append("&EndDate=" + endDate.Text); url.Append("&Brnch=" + ddlLocation.SelectedValue); Response.Redirect(url.ToString()); Current URL: TopCustomer.aspx?BegDate=09/15/2010&EndDate=09/30/2010&Brnch=300I need to have the above URL change to:TopCustomer.aspx?BegDate=20100915&E

ASP.NET-Gridview - Export to excel- column format

  
Hi All, I am exporting gridview data into excel sheet. I have a column  which is a code for which data is like 012,013 etc. But when this data is being exported , in the excel sheet I can see the data as 12,13,14. but actually it should be 012.... Is there any way to specify the format of such columns in my datatable so that they are in the same manner as in the database? Please provide some information on this.Thanks in advance.

sql 2008 convert nvarchar to float

  
Hi i need to write a condition in my query where my column value is greater than 2 my column name is sales..and when ever the value in sales in greater then 2 i should get  the O/P but datatype of my  column sales is "nvarchar"  i have used the condition cast(isnull(sales,0) as float)>2, but this didnt work. if some one could help me ...

convert date to mmddyy format before submit to database

  
Dear all,   Could anyone pls guide me on how to convert date in textbox which is in format(dd/mm/yy) to mmddyy before submit to database.    
Categories: 
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