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


Top 5 Contributors of the Month
Imran Ghani
Post New Web Links

Sql DateTime2 to .NET DateTime conversion results in out-of-range exception

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
 
I posted it in C# General because I thought that the subject was not Sql Smo related, although I use Smo extensively even in this routine for connection. Unfortunately after a few days no constructive suggestion has been advanced. Two posts the thread collected are not helpful. I am wondering if Alok or someone else with the status of MSFT Sql Server developers take a look at this very importan problem for me which became a stumbling block.Thanks. AlexB - Win_7 Pro64, SqlSer64 WinSer64


View Complete Post


More Related Resource Links

System.DateTime.AddDays: Value to add was out of range.

  

I'm getting the following error:

[ArgumentOutOfRangeException: Value to add was out of range.
Parameter name: value]
   System.DateTime.Add(Double value, Int32 scale) +7657639
   System.DateTime.AddDays(Double value) +19
   ...

The line from which this error originates has the following code:

expires = Now.AddDays(30)

It occurs irregularly and irreproducably. The server date/time is correct and set to GMT. I've heard that it could be some curiosity to do with timezones, but that is speculative.

It seems that we're some time from the year 10,000 yet, so I can't understand why adding thirty days to the current time could cause this! Any ideas? Culture settings perhaps?


ASP.NET DateTime Conversion strange behaviour

  

Hi,

I am working on ASP.NET web application where we are using javascript calendar to accept the date in textboxes. The date format is dd/mm/yyyy. The application is running in US and UK environments.

I am facing issues in US environment while storing the dates so I have added 2 keys in config file viz culture which will be either en-US or en-GB depending on the deployment location and baseCulture which will always be en-GB.

In the code, I have added a condition which checks if culture and baseCulture values are not matching then do the conversion of date in US format.

But I have found that the code converts the date from dd/mm/yyyy to mm/dd/yyyy when the key is baseCulture (en-GB) and it throws FormatException when key is culture (en-US).

I am bit confused by this behaviour. Is this normal? Can any one please explain more about this? Note: I am working on a machine which has en-US as language and culture

<add key="culture" value="en-US" />
<add key="baseCulture" value="en-GB" />
// This line works and converts the date from 26/08/2010 to 08/26/2010
// Here the baseCulture is en-GB.
DateTime dt = DateTime.Parse(txtCurrentDate,baseCulture)

// This line throws FormatException. culture 

Data Conversion - string to datetime help!!!

  
Hi all, I need help on concatenate two fields into one then display it as datetime format as such yyyy-mm-dd hh:mm:ss AM/PM. The db is on SQL 2008 SP1. The date and time columns are on the same table and store as varchar. Date has varchar(10) and time has varchar(16). Date column shows result from query is 20100827 while the time column is either 070101.000000 or 070101. The question is how go about concatenate the two together and display in the format above in a report? How should I write my select query for this report? I am using SQL 2008 R2 BI Development Studio to design my report. Thank you. VCHS

Need help in datetime conversion excluding invalid records

  
I have varachar(50) field which have date in yyyymmdd format Now i have to filter the data which are greater then server date I have following query Select * from MDB_CONTENT_INFO Where convert(datetime, CON_SCHEDULED_INDUCTION_DATE,112) >= GetDate() Problem ist aht i may have some invalid data other then valid one. I am getting error The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. or Conversion failed when converting date and/or time from character string. I wanted to exclude those records but currently getting error for the query asKamran Shahid Senior Software Engineer/Analyst (MCP,MCAD,MCSD.NET,MCTS,MCPD.net[web])

"Out-of-Range" Error When Passing DateTime (DD/MM/YYYY) into Stored Procedure

  
Hi everybody, I hope some of you will be able to help me regarding this datetime problem. The query in the stored procedure doesn't accept DD/MM/YYYY format, although all dates are being saved in the format of DD/MM/YYYY hh:mm:ss. Example of query that throws the error: @sDate = '31/8/2010' @eDate = '1/9/2010' SELECT * FROM tb_Schedule where scheduledate >= @sDate and scheduledate <= @eDate  Error:  The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value FYI, the SQL Server 2005 is defaulted to British English(2057)for all logins and the DB server itself and my server Regional and Language is set to English (United Kingdom). Any help will be appreciated. Thank you very much.  

Invalid DateTime Exception

  
Hi, I have a problem in my application using by 1.5 million users. During peak hours of usage, one or twice in a day, application started throughing exceptions, DateTime is invalid  on all pages where datetime conversion is applied (value mostly comes from database sever). And the problem only resolves if we reset the application. This is very annoying and causing frustation in users. An urgent reply needed. Thanks in advance Farrukh 

Help to resolve an error "The conversion of a char data type to a datetime data type resulted in an

  
Why am I getting an error when executing this: select convert(datetime, '2010-09-12T18:11:48', 120) The message is: "Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." I don’t get the error if I remove “T” from the string. But I need it to work with “T”.

Internal server error exception on search results page when no language is set in browser

  
We have some users who receive an internal server error exception when they enter a search query on our SPS 2010 site. The SharePoint log shows the following: Internal server error exception: System.ArgumentNullException: Value cannot be null.  Parameter name: The property RequestedLanguage cant not set to NULL     at Microsoft.Office.Server.Search.Query.Location.set_RequestedLanguage(CultureInfo value)     at Microsoft.Office.Server.Search.WebControls.CoreResultsDatasourceView.CreateLocationList()     at Microsoft.Office.Server.Search.WebControls.CoreResultsDatasourceView.SetPropertiesOnQdra()     at Microsoft.Office.Server.Search.WebControls.SearchResultsBaseWebPart.EnsureWebpartReady() System.ArgumentNullException: Value cannot be null.  Parameter name: The property RequestedLanguage cant not set to NULL     at Microsoft.Office.Server.Search.Query.Location.set_RequestedLanguage(CultureInfo value)     at Microsoft.Office.Server.Search.WebControls.CoreResultsDatasourceView.CreateLocationList()     at Microsoft.Office.Server.Search.WebControls.CoreResultsDatasourceView.SetPropertiesOnQdra()     at Microsoft.Office.Server.Search.WebControls.SearchResultsBaseWebPart.EnsureWebpartReady() After some more diggin

Concat and Conversion of char data type to smalldatetime data type resulted in an out-of-range small

  

 

Hello,

I am trying to create a new date as below and add this date to the BDMRY field (smalldatetime data type) in SQL Server 2005.

MostRecentCompletionDate -  (smalldatetime data type)

StudentBirthdate - (smalldatetime data type).

Update MAXTRAX_STUDLESSON_EXPDATE_BIRTHDATE
SET BDMRY =  CAST(CAST(YEAR(MostRecentCompletionDate) AS VARCHAR(4)) + '/' +
                      CAST(MONTH(StudentBirthdate) AS VARCHAR(2)) + '/' +
                      CAST(DAY(StudentBirthdate) AS VARCHAR(2)) AS DATETIME)

When I run this I'm getting the error as:

Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
The statement has been terminated.

It works fine on my laoptop but doesnt work on the production server.

Any help would be greatly appreciated.

thanks,

SP.


Creating workflow in SP Designer 2010 results in Exception from HRESULT: 0x80131401

  

The workflow is a 1 step Approval workflow. I'd published it a few time and it worked just fine. I then made a change to an "Email task notification" step.

I get a dialog when I try to publish the workflow:

 
Workflow Error
Errors were found when compiling the workflow. The workflow files were saved but cannot be run.
(43, 3) Loading this assembly would produce a different grant set from other instances. (Exception from HRESULT: 0x80131401))

I tried the "Check for Errors" button and I get:
The workflow contains no errors.

Images:
Error dialog
Check for Error dialog

Anyone have any clues as to where I might start looking for clues? Nothing has been returned by the usual search engines...

I've just tried to create a brand new workflow and it's still giving me the above dialogs. :'(


Conversion failed when converting datetime from character string. SQL Server @StartDate as datetime

  

Hi,

Can you please help me on this?

When I try to pass my parameter it doesn't work. See below is my code :

 

declare @SQL Varchar(4000)

declare @CRM_FilteredAppointment nvarchar(2000)

declare @CRM_FilteredSystemUser nvarchar(2000)

declare @StartDate datetime

declare @EndDate datetime





set @CRM_FilteredSystemUser = 'Select * from FilteredSystemUser'

set @StartDate = '20080101'

set @EndDate = '20100101'



set @CRM_FilteredAppointment = 'SELECT activityid, participationtypemask 

FROM FilteredAppointment 

WHERE FilteredAppointment.eu_calltype = 2

AND FilteredAppointment.statecode = 1

AND FilteredAppointment.scheduledend >= DateAdd( month, -1, GetD

DateTime conversion Error

  
I am getting the following error while executing a SSIS package.
"
[OLE DB Destination [760]] Error: There was an error with input column "RecordEffectiveDate" (828) on input "OLE DB Destination Input" (773).
 The column status returned was: "Conversion failed because the data value overflowed the specified type.".

"

Source is a non sql server with datetime datatype and the data like 10/20/2004 2:23:27 PM
and the sql server destination datatype is datetime.

TIA

DateTime Conversion Issues in SSIS

  

Hi,

I have a Weird issue with SSIS. I have some input data of dates like '01/01/1999' and '01/01/0207. First one is a valid date and later is invalid. I have to use DataConversion inorder to convert it to datetime and re-direct the errored row to Flat-file destination.

Now the Issue is:

If I convert to DateTime (DateTimeStamp) in Data Conversion, it is treating both the dates as Valid and failing in my SQL Destination as the later is an invalid date.

If I convert to DateTime2 (DateTimeStamp2 with Precesion) in Data Conversion, it is treating both the dates as Invalid?

How do i fix this in SSIS (In a simple way) to throw 1 as error and other as Valid? Any help is appreciated.

Thanks,
Srini...

 


DateTime/String Conversion

  

I have a few date fields on my report and i cannot get them to convert in the correct format.

The default is 08/28/2010 2:30:00 PM and I have used CONVERT(Varchar, EndDate, 103) to convert it to 28/08/2010. But the new date comes out as text format (string) when I copy results into Excel and I want it to stay as a Date type.

Can anyone help please?

Thanks,

ST


string to datetime conversion using date parameters in SSRS

  

Hello, I have a database field that I am pulling that is type string, but the format of each value is mm/dd/yyyy.  I want to convert this to a datetime format (minus the time) so that i can compare this value to a parameter date range (begin date and end date) that are type datetime so that i can use the calendar control in my report.  i've tried in my select statement to use select.....convert(datetime, stringfieldname, 101), but I get an error that says "Conversion failed when converting datetime to character string". 

I am also open to converting the datetime parameters to strings to do the comparison, but not sure the best way to do this either.

thanks!


getting un-representable DateTime exception? users bday?

  

Hi,

I am getting date of birth from a user within my Create User Wizard Control and inserting

to my SQL Database as Birthday (DateTime). The code i am using is below but I keep getting an error

( Year, Month, and Day parameters describe an un-representable DateTime. ) Full excepton is further below

 

Aspx

DropDownListDay

DropDownListMonth

DropDownListYear

 

Page Load


        if (!IsPostBack)
        {
            GetYearsListed(
            (DropDownList)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl
            ("DropDownListYear"));
            GetMonthsListed(
            (DropDownList)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl
            ("DropDownListMonth"));
          &

Nested CAML Querie results in exception " Some part of your SQL statement is nested too deeply. Rewr

  

All,

I am trying to mimic SQL"IN" operation using CAML. Lets take a scenario where we need fetch all the ListItems with ID within a list of IDs. The target list is realy large ( 50k records)

Possible options are from the Working with large list Whitepaper

-> CAML (Object model) -> CAML (using Webservices) -> Portal Site Map Provider -> Search API

The Custom webpart that we are developing is on live data ( NO search API) and query is user dependent (No Portal Site provider)

As our code is deployed on to the server, we can use SPobject model SPQuery, but as CAML deosn't support "IN" Operator and not more than two logical operators in we have to nested query.

When the nested query hits the 478 nested ORs then the exception is raised

"Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries "

Firstly the Row lmit 2000 is resulting in multiple queries and on top of it, spliting it further results in performance impact.

Is there any other better solution???


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