.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

Need help in datetime conversion excluding invalid records

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
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])

View Complete Post

More Related Resource Links

ASP.NET DateTime Conversion strange behaviour



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

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

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

Get last seven days records excluding Weekend days ?

Hi , I'm trying to extract records from table inserted in last seven working days . I'm unable to exclude Sunday and Saturday from this : select * from tb_Name where dt >=DateAdd(dd, DateDiff(dd, 0, Getdate()), 0) and dt<DateAdd(dd, DateDiff(dd, 0, GetDate())-7, 0) Any pointers would greatly  be valued and appreciated . Thanks .Rajkumar Yelugu

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

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



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.


DateTime Conversion Issues in SSIS



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.



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?



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.


ASP.NET GridView - edit records using JQuery Dialog

There are many ways in which you could modify modular data in Grid View:
1. Inline by making rows editable when u click on Edit
2. By using separate pages for Edit
3. By using popups/dialogs for adding/editing

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.


Program for Format Conversion of An Image

.NET provides extensive support for image conversion. Any image can be processed from one format to another. Most common formats to which .NET have support for are .BMP, .EMF, .GIF, .ICO, .JPG, .PNG, .TIF and .WMF.

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.

Conversion of text files from ANSI to UTF-8

reading and writing text files in ANSI format and
writing html-files in Charset ISO-8859-1 (Western Europe).

datetime server



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

DateTime.Now Is ok?

Must be something else?


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