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


Top 5 Contributors of the Month
david stephan
Asad Ali
Fauzul Azmi
Post New Web Links

Convert NVARCHAR column to DateTime - SQL Server

Posted By:      Posted Date: October 19, 2010    Points: 0   Category :ASP.Net
 

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  


 




View Complete Post


More Related Resource Links

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

Convert NVarchar to DateTime - SQL Server 2000

  

Hi there,

FROM USER INTERFACE END USER SELECTS START MONTH, START YEAR AND END MONTH, END YEAR. IT IS REQUIRED TO RETRIEVE DATA WHERE ONE OF THE FIELDS I.E, ExpiryDate is of type NVARCHAR, stored dates as 2/10/2010 [DD/MM/YYYY] , 10/10/2010 ETC.

Using SQL Server 2000.

ALTER PROCEDURE [dbo].[CountUsers]
		(
			@startMonth nvarchar(20) =	NULL,
			@startYear nvarchar(20) = NULL,
			@endMonth  nvarchar(20) = NULL,
			@endYear  nvarchar(20) = NULL,
			@startDate nvarchar(50) = NULL,
			@endDate  nvarchar(50) = NULL
		)
		AS BEGIN

		SET @startDate = @startYear + '-01-' + @startMonth; 
		SET @endDate  = @endYear + '-01-' + 

SQL Server DateTime Convert pattern

  

I need the datetime to be in the following format for SQL server 2005

dd/MM/YYYY HH:MI in 24 Hour Format

note: seconds are not included

 

Thanks for any help in advance


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

  

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!


Essential SQL Server Date, Time and DateTime Functions

  
The essential date and time functions that every SQL Server database should have to ensure that you can easily manipulate dates and times without the need for any formatting considerations at all.

datetime server

  

Hi,

I'd like to have the date and time from server.

DateTime.Now Is ok?

Must be something else?


thanks.




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.


Upsize Your Database: Convert Your Microsoft Access Application to Take Advantage of SQL Server 7.0

  

What if you need to convert an existing Microsoft Access 97 database application into a true client-server application that is based on a SQL Server back end? If you know a little about Visual Basic and SQL Server, it's easy to make your app take advantage of the power and scalability provided by SQL Server 7.0. Using some concrete code examples, this article takes you step by step through converting the native Jet queries in your Access application into stored procedures and pass-through queries that SQL Server can use. You'll also learn how to pass on parameters when your client-server app calls these SQL Server stored procedures and queries.

Michael McManus

MSDN Magazine June 2000


How Convert Datetime (YYYY/MM/dd HH:mm:ss) To yyyy-MM-dd

  

Hello,

How do you write a date value to an XMLElement in .NET 3.5 XMLDocument object. 

 

 I am using the following:

 

DateTime orderDate = new DateTime();
orderDate = DateTime.Now; // 2010-08-24 16:45:46

string st = XmlConvert.ToString(orderDate, "yyyy-MM-dd");     // st="2010-08-24"
DateTime dt2 = XmlConvert.ToDateTime (st, "yyyy-MM-dd");   // dt2 = "2010-08-24 00:00:00"

I would like to remove the time section "00:00:00". I only need to write a date in the XML file with the following format :

<OrderDate>2010-08-24</OrderDate>

Order date is defined like this in the xsd file : <xs:element name="OrderDate" type="xs:date"/>

 

Thanks for your support.


convert to ASP.NET set objHttp = Server.CreateObject("Microsoft.XMLHTTP")

  

Hi all,

i don't know nothing to ASP and i need your help to convert the following code to ASP.NET

set objHttp = Server.CreateObject("Microsoft.XMLHTTP") 
objHttp.open "POST", "https://api.c4sms.com/?" & strQueryString, false 
objHttp.setRequestHeader "Content-type", "application/x-www-form-urlencoded" 
objHttp.Send 


 

I need help for this.

Thank you very much.

 

If you known the full code is:

<% 
   Option Explicit 

   '**************** 
   'Replace values with your C4 username and password 
   CONST c4USERNAME = "Your C4 Username Here" 
   CONST c4PASSWORD = "Your C4 Password Here" 
   '**************** 
   Dim strReturnValue 

   'Function to send the SMS message 
   Function sendSMS(strSendToNumber,strMessage,strFromNumber) 

      'Create new variables 
      Dim strQueryString      'Holds data to post to C4 website 
      Dim objHttp            'The XMLHTTP object to make the post request 
      Dim strResponseTxt      'The response from the C4 server 

      'Build the query string 
      strQueryString = "username=" & c4USERNAME & "&password=" & c4PASSWORD & "&send

Convert XML DateTime to SQL DateTime

  
I have tried to convert XML datetime value to SQL datetime by executing the below statement, though I feel there might be some other way of converting the value in SQL server 2000. declare @TestXMLTime as varchar(50) set @TestXMLTime ='2010-01-01T00:00:07+05:30' select cast(left(@TestXMLTime,10)+' '+right(left(@TestXMLTime,19),8) as datetime)    

Datetime column with [Computed column specification] formula

  
i have a datetime field in my SQL Server table i dont want to add weekend dates in this column. For this reason i always have to check the calendar. how can i use [Computed column specification] formula so that i never add weekend dates?

How to get a Table name from the column Value in Sql Server?

  
Hi All,I have a number of tables in the database and i have a column value as "abc" coming from one of the tables in the database,Now i need to find the table name from where this column value is coming?

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?

SQL Server Unicode Characters in a column

  
I have an ETL and it fails because of some Unicode Characters. The unicode characters are getting inserted from somewhere in the application and we are still trying to ascertain the source of the same.But till then I need to run a T-SQL Query and find out the rows( I know the column name ) that are causing this problem, modify the data to the correct format and then run the ETL. Would i need to use a cursor? can anyone please advise? It was a bit urgent thus any help would be great! Thanks Nimish
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