Conversion Error From String 30-dec-1899
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up SQL Server DateTime conversion failure up vote 3 down vote favorite I have a large table with 1 million+ records. Unfortunately, the person who created the table decided to put dates in a varchar(50) field. I need to do a simple date comparison - datediff(dd, convert(datetime, lastUpdate, 100), getDate()) < 31 But it fails on the convert(): Conversion failed when converting datetime from character string. Apparently there is something in that field it doesn't like, and since there are so many records, I can't tell just by looking at it. How can I properly sanitize the entire date field so it does not fail on the convert()? Here is what I have now: select count(*) from MyTable where isdate(lastUpdate) > 0 and datediff(dd, convert(datetime, lastUpdate, 100), getDate()) < 31 @SQLMenace I'm not concerned about performance in this case. This is going to be a one time query. Changing the table to a datetime field is not an option. @Jon Limjap I've tried adding the third argument, and it makes no difference. @SQLMenace The problem is most likely how the data is stored, there are only two safe formats; ISO YYYYMMDD; ISO 8601 yyyy-mm-dd Thh:mm:ss:mmm (no spaces) Wouldn't the isdate() check take care of this? I don't have a need for 100% accuracy. I just want to get most of the records that are from the last 30 days. @SQLMenace select isdate('20080131') -- returns 1 select isdate(
TDateTime; Description The StrToTime function attempts to convert a time as a string Date into a TDateTime value. The time string must adhere to the format of the LongTimeFormat value, and use the TimeSeparator character to separate the hour, minute and second values. The default format in the UK is hour:minute:second.milli-seconds, where : The hour must be 0..23 The minute must be 0..59 (optional) The second must be 0..59 (optional) The milli-secs must http://stackoverflow.com/questions/28110/sql-server-datetime-conversion-failure be 0..999 (optional) You may use the current AM and PM value (as defined by TimeAMString, TimePMString), or the literals 'AM', 'am', 'PM' and 'pm' before or after the time. The date is set to 30 dec 1899, one day short of the 19th century. Any errors in the time string http://www.delphibasics.co.uk/RTL.asp?Name=StrToTime will yield EConvertError. Version 2 of this function is for use within threads. You furnish the FormatSettings record before invoking the call. It takes a local copy of global formatting variables that make the routine thread safe. Notes Warning : the date value is set to 1 day short of the end of the 19th century. Exactly why is unclear. Related commands LongTimeFormat Long version of the time to string format StrToDate Converts a date string into a TDateTime value StrToDateTime Converts a date+time string into a TDateTime value TimeAMString Determines AM value in DateTimeToString procedure TimePMString Determines PM value in DateTimeToString procedure TimeSeparator The character used to separate display time fields TimeToStr Converts a TDateTime time value to a string Author links Buy Website Traffic at Buywebsitetrafficexperts.com Buy Proxies atBuyproxies.io Download this web site as a Windows program. Example code : Showing with and without minutes and/or seconds var myTime : TDateTime; begin myTime := StrToTime('3PM'); ShowMessage('3PM= '+TimeToStr(mytime)); myTim
Hello, > I use IB6. I want to update a timestamp field in a table. > My SQL-statement is: > UPDATE sometable SET mydatetimefield = '11:15:00' > http://www.delphigroups.info/2/6b/284075.html This statement works in ISQL on the server and on the client. > When I change the statement to: > UPDATE sometable SET mydatetimefield = '14:15:00' > it works on https://msdn.microsoft.com/en-us/library/ms141036.aspx the client, but on the server I got the error: > Conversion error from string ''14:15:00'' > Does someone has any idea? > -- > Peter > Pet...@nLogchem.nl Peter van conversion error Logche Delphi Developer Sun, 28 Sep 2003 20:31:21 GMT Re:Time conversion error Hello, I use IB6. I want to update a timestamp field in a table. My SQL-statement is: UPDATE sometable SET mydatetimefield = '11:15:00' This statement works in ISQL on the server and on the client. When I change the statement to: UPDATE sometable SET mydatetimefield = '14:15:00' it works conversion error from on the client, but on the server I got the error: Conversion error from string ''14:15:00'' Does someone has any idea? -- Peter Pet...@nLogchem.nl Peter van Logche Delphi Developer Sun, 28 Sep 2003 21:05:15 GMT Re:Time conversion error In ISQL I can see that the client dialect on both machines is 3. Peter Pet...@nLogchem.nl "Peter van Logchem"
resources Windows Server 2012 resources Programs MSDN subscriptions Overview Benefits Administrators Students Microsoft Imagine Microsoft Student Partners ISV Startups TechRewards Events Community Magazine Forums Blogs Channel 9 Documentation APIs and reference Dev centers Retired content Samples We’re sorry. The content you requested has been removed. You’ll be auto redirected in 1 second. Integration Services Data Flow Data in Data Flows Data in Data Flows Integration Services Data Types Integration Services Data Types Integration Services Data Types Integration Services Data Types Parsing Data Comparing String Data TOC Collapse the table of content Expand the table of content This documentation is archived and is not being maintained. This documentation is archived and is not being maintained. Integration Services Data Types SQL Server 2016 Other Versions SQL Server 2014 SQL Server 2012 SQL Server 2008 R2 Applies To: SQL Server 2016When data enters a data flow in a package, the source that extracts the data converts the data to an Integration Services data type. Numeric data is assigned a numeric data type, string data is assigned a character data type, and dates are assigned a date data type. Other data, such as GUIDs and Binary Large Object Blocks (BLOBs), are also assigned appropriate Integration Services data types. If data has a data type that is not convertible to an Integration Services data type, an error occurs.Some data flow components convert data types between the Integration Services data types and the managed data types of the Microsoft .NET Framework. For more information about the mapping between Integration Services and managed data types, see Working with Data Types in the Data Flow.The following table lists the Integration Services data types. Some of the data types in the table have precision and scale information that applies to them. For more information