Solved

How to convert correct DateTime function

Posted on 2011-03-02
37
1,141 Views
Last Modified: 2012-08-13
I have a varchar value of "02/21/2011 12:54" stored in a temporary SQL Server table.
I am retreiving it this way, in an effort to store it in my permanent table, which is a column with a datetime data type:
ParamByName('@DReceived').Value := StripIT(fieldByName('Date Received').AsString)

Open in new window


When I run it in Delphi... I get this message:
raised exception class EOleException with message 'The conversion of a char data to a datetime data type resulted in an out-of-range datetime value'.

I know that I need to run some kind of string or datetime function to get into my permanent datetime column. Question is... which function would work best? and I need the syntax spelled out for me please. Thanks!

0
Comment
Question by:zberg007
  • 20
  • 12
  • 2
  • +2
37 Comments
 
LVL 12

Expert Comment

by:Hypo
Comment Utility
Have you tried the function StrToDateTime ?
ParamByName('@DReceived').Value := StrToDateTime(StripIT(fieldByName('Date Received').AsString));

Open in new window

0
 
LVL 5

Expert Comment

by:jijeesh
Comment Utility
How are to retrieving data from backend.. Are you using stored procs??
Try using type cast..
http://www.mssqltips.com/tip.asp?tip=1145
 
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
If you already know the format of your date string, why not just provide them in the proper order by using string functions? The best order (working with any date format set up on client or server) is "yyyymmdd hh:mm:ss".
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

What query object are you using, ADO, DBExpress.

What does your function StripIT do

You need to convert the string to datetime. You can use StrTodateTime
ParamByName('@DReceived').Value := StrToDateTime(StripIT(fieldByName('Date Received').AsString))

Open in new window

0
 

Author Comment

by:zberg007
Comment Utility
I am using ADO for the query object. I tried the above suggested StrToDateTime function and it returns this error message:
Incompatible types: 'String' and 'TDateTime'

Thanks!
0
 
LVL 12

Expert Comment

by:Hypo
Comment Utility
What does the StripIT function do?

does it return a TDateTime?
0
 

Author Comment

by:zberg007
Comment Utility
Here's my StripIT function:

Function StripIT( s: string) : string;
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

StrTodatetime takes a string parameter which is what your StripIT function returns so you should not get a compiler error unless you mistakenly used DattimeToStr instead

You also need to validate your date before calling StrToDatetIme in case its an invalid date or a date that SQL server does not accept (earlier thatn 1753 I believe)
0
 

Author Comment

by:zberg007
Comment Utility
Qlemo, can you provide an example please?

Thanks again!
0
 

Author Comment

by:zberg007
Comment Utility
Thanks ewangoya. I double checked my function, and I am using StrToDateTime as instructed here... so I'm lost as to why its giving me the Incompatible Types error above.

How would be best to validate my date before calling StrToDateTime?

Thanks again...
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
My Delphi times are long gone, but I'll try:

strDate=StripIT(fieldByName('Date Received').AsString;
ParamByName('@DReceived').Value := concat(Copy(strDate, 7, 4), LeftStr(strDate, 2), Copy(strDate, 4, 2), Copy(strDate, 11, 8));
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

I'm still not sure what your StripIT function really does because you did not show the code.

To validate the Datetime, you can use this function

function ValidateDateTime(const S: string): Boolean;
var
  Temp: TDateTime;
begin
  Result := TryStrToDateTime(S, Temp);
  if Result then
    Result := Temp > StrToDateTime('01/01/1753'); //erliest date supported by SQL Server
end;

//you  can make the function return a datetime if you want

Open in new window

0
 

Author Comment

by:zberg007
Comment Utility
ewangoya, I ran your function above... different error message. Feel like I'm getting closer maybe. Here's what I get::
raised exception class EOleException with message Conversion failed when converting datetime from character string'.

Thanks again... you mentioned above that I could make the function return a datetime if I wanted, what syntax should I use here instead?
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

This means one of the dates stored as string in your database is not correct hence it can not be converted.
The decision here is what to do with invalid data, What value do you store incase the date is not correct

To return a DateTime, you can change the function to

0
 

Author Comment

by:zberg007
Comment Utility
Qlemo, When I run this:

ParamByName('@DTaken').Value := concat(Copy(strDate, 7, 4), LeftStr(strDate, 2), Copy(strDate, 4, 2), Copy(strDate, 11, 8));

I get this for error:
Missing operator or semicolon

Do you know where an operator or semicolon is missing?
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility
Sorry, I don't know why my function did not attach
function ValidateDateTime(const S: string; const ADefault: Double = 36526): TDateTime;
var
  Temp: TDateTime;
begin
  if TryStrToDateTime(S, Temp) and (Temp > StrToDateTime('01/01/1753')) then
    Result := Temp
  else
    Result := ADefault;
end;

Open in new window

0
 

Author Comment

by:zberg007
Comment Utility
ewangoya, all my stored dates in the temp table column look like this exactly:

"02/21/2011 13:01"

this included the quotations as well.

Thanks... any ideas?
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility
Looks like the quotations may be the problem then
Modify the function to
function ValidateDateTime(const S: string; const ADefault: Double = 36526): TDateTime;
var
  Temp: TDateTime;
  Temps: string;
begin
  Temps := Trim(StringReplace(S, '"', '', [rfReplaceAll]));
  if TryStrToDateTime(Temps, Temp) and (Temp > StrToDateTime('01/01/1753')) then
    Result := Temp
  else
    Result := ADefault;
end;

Open in new window

0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:zberg007
Comment Utility
ewangoya, thanks again. I replaced your older function with the one immmediately above and when I run it, I now get this error:

raised exception class EOleException with message 'Application uses a value of the wrong type for the current operation'.

Thanks...
0
 

Author Comment

by:zberg007
Comment Utility
hang on ewangoya... I haven't ran your latest. be right back!
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

You call it this way

ParamByName('@DReceived').Value := ValidateDateTime(fieldByName('Date Received').AsString)


Please also show your full query and how you create the parameters for the query
Do you have something of the kind
SQL.Add('WHERE XXX = :@YYYY');
0
 

Author Comment

by:zberg007
Comment Utility
Okay ewangoya, Just tried your latest and greatest function that included stripping out the quotations and I get this same message as with the just prior function:

raised exception class EOleException with message 'Application uses a value of the wrong type for the current operation'.

Thanks again... any other things I should be doing or looking for?
0
 

Author Comment

by:zberg007
Comment Utility
ewangoya, I double checked how I was calling it and it's correct as described. When it errors... it stops here in my code on line 26 below: ParameterObject.Value := NewValue;

Which is part of this procedure:

procedure TParameter.SetValue(const Value: Variant);
const
  SizedDataTypes = [ftUnknown, ftString, ftFixedChar, ftWideString, ftMemo,
    ftBlob, ftBytes, ftVarBytes];
var
  NewSize: Integer;
  NewValue: OleVariant;
begin
  if VarIsClear(Value) or VarIsNull(Value) then
    NewValue := Null
  else
  begin
    if DataType = ftUnknown then
      SetDataType(VarTypeToDataType(VarType(Value)));
    { Convert blob data stored in AnsiStrings into variant arrays first }
    if (DataType = ftBlob) and (VarType(Value) = varString) then
      NewValue := StringToVarArray(Value) else
      NewValue := Value;
  end;
  if DataType in SizedDataTypes then
  begin
    NewSize := VarDataSize(NewValue);
    if (Size = 0) or (NewSize > Size) then
      Size := NewSize;
  end;
  ParameterObject.Value := NewValue;
end;

Open in new window

0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

Seems your parameter is expecting a different datatype other than DateTime
However I can't tell unless I see how you are creating the query and Parameters
0
 

Author Comment

by:zberg007
Comment Utility
I'm not even sure how that's being done! lol  I inherited this code (as you can tell) and I really don't know where to look for that information. Can you point me in a direction? Thanks again!
0
 

Author Comment

by:zberg007
Comment Utility
I just tried this simple function:
ParamByName('@DReceived').Value := ExtractWordL(1,'Date Received', ' ');

Open in new window

and it results in the same exact error of:

'Conversion failed when converting datetime from character string'.
0
 

Author Comment

by:zberg007
Comment Utility
Any idea on how I can modify the stored procedure to convert the incoming varchar date string into datetime data types and store in my temporary table as such? Anyone?

Thanks again!
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

Ok, Hold up
This is a stored procedure

What is the datatype for the parameter @DReceived ?

It could be you are passing a string to a stored procedure which then does some process based on the string.

If thats the case, pass in a string after validating it to the parameter
(But again I cant tell unless I know the datatype of the Parameter being sent to StoredProcedure)

function ValidateDateTime(const S: string; const ADefault: Double = 36526): string;
var
  Temp: TDateTime;
  Temps: string;
begin
  Temps := Trim(StringReplace(S, '"', '', [rfReplaceAll]));
  if TryStrToDateTime(Temps, Temp) and (Temp > StrToDateTime('01/01/1753')) then
    Result := Temps
  else
    Result := DateTimeToStr(ADefault);
end;

Open in new window

0
 

Author Comment

by:zberg007
Comment Utility
I just looked at the stored procedure and here's what it has:

@DReceived VarChar(12),
0
 

Author Comment

by:zberg007
Comment Utility
It's strange to me because my insert looks like this from the stored procedure:

Insert Into Samples (Sample_id, Date_Taken, Date_Received,
                            Date_Posted, Date_Reported, Date_Commented)
       Values (@NewID, @DTaken, @DReceived, GetDate(), @DReported, @DCommented)

and the datatypes for the Date_Received column is DateTime. So I just figured that I needed to convert it, but now I'm not sure about that.

Thanks...
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

So it is actually expecting a string of length 12 and not a DateTime

Modify the function to
function ValidateDateTime(const S: string; const ADefault: Double = 36526): string;
var
  Temp: TDateTime;
  Temps: string;
begin
  Temps := Trim(StringReplace(S, '"', '', [rfReplaceAll]));
  if TryStrToDateTime(Temps, Temp) and (Temp > StrToDateTime('01/01/1753')) then
    Result := Temps
  else
    Result := DateTimeToStr(ADefault);

  if Length(Result) > 10 then  //truncate
    SetLength(Result, 10);
end;

Open in new window

0
 

Author Comment

by:zberg007
Comment Utility
OK... installed this function above. Ran it and I get this error:

'Conversion failed when converting datetime from character string'.

I apologize for being such a pain! Thanks for all you're doing here!
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

Thats fine

It just seems there is something I'm missing, its either in the stored procedure itself in the database, or in the TADOStoredProc Object

Can I see the stored procedure code from the db

0
 

Author Comment

by:zberg007
Comment Utility
Here's my stored procedure from the db:

USE [Trend]
GO
/****** Object:  StoredProcedure [dbo].[UpDateSamples]    Script Date: 03/02/2011 18:43:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[UpDateSamples]
         @AccountID Int,
         @LabID Char (8),
         @LabCNO VarChar(24),
         @LabTestNum VarChar (24),
         @TrackingNum VarChar (24),
         @CompType Varchar(8),
         @CompID Int,
         @Condition Int,
         @DTaken VarChar(12),
         @DReceived VarChar(12),
         @DReported VarChar (12),
         @DCommented VarChar (12),
         @EvalID Varchar(30),
         @FluidMan VarChar (20),
         @FluidBrand VarChar (50),
         @FluidGrade VarChar (30),
         @TimeOnOil VarChar (16),
         @TimeOnComp VarChar (16),
         @TimeOnUnit Int,
         @FluidAdded Int,
         @FluidChange Int,
         @FileName Varchar (64)

AS  
      Declare @NewID Int

      SET NOCOUNT ON 
      
      Select @NewID = Sample_ID
      From Samples 
      Where Lab_ID = @LabID and Lab_Test_Num = @LabTestNum and date_taken = @DTaken

      If (@NewID > 0 ) AND (@NewID is NOT NULL)
        BEGIN
           Delete from Samples where Sample_ID = @NewID

           Delete from Additional_Tests where Sample_ID = @NewID
        end

       exec @NewID = getnextint 'SAMPLES'

       Insert Into Samples (Sample_id, Account_ID, Lab_ID, Lab_CNO,  Lab_Test_Num,  Tracking_Number, 
                            Component_Type, Component_ID, Condition, Date_Taken, Date_Received,
                            Date_Posted, Date_Reported, Date_Commented, Eval_ID, Fluid_Manuf, Fluid_Brand, Fluid_Grade,
                            Time_On_Oil, Time_On_Comp, Time_On_Unit, Fluid_Added, Fluid_Changed, [File_Name])
       Values (@NewID,  @AccountID, @LabID, @LabCNO, @LabTestNum, @TrackingNum, @CompType, @CompID, 
                            @Condition, @DTaken, @DReceived, GetDate(), @DReported, @DCommented, @EvalID, 
                            @FluidMan, @FluidBrand, @FluidGrade, @TimeOnOil, @TimeOnComp, @TimeOnUnit, 
                            @FluidAdded, COALESCE(@FluidChange,0), @FileName )

       IF @@ERROR = 0
          return (@NewID)
       ELSE
          return (-1)

Open in new window

0
 

Author Comment

by:zberg007
Comment Utility
ewangoya, I made progress on this by simply removing the quotes " " from the varchar value of "02/21/2011" by using my StripIT function inline, like this:
 ParamByName('@DReceived').Value := StripIT(FieldByName('Date Received').AsString);

Open in new window


What I need now is to just remove the remaining hour:minute value as well. What leftStr function can I use with my StripIT function to just keep the left 10 characters like this: 02/21/2011

Thank you kindly!
0
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
Comment Utility
So thats what the StripIT function was doing
You can use LeftStr()

add this to your uses clause

uses
  StrUtils;

then the code becomes
ParamByName('@DReceived').Value := LeftStr(StripIT(FieldByName('Date Received').AsString), 10);

Or you can include within the StripIT function
0
 

Author Comment

by:zberg007
Comment Utility
ewangoya... that worked! Thank you, thank you for sticking with this! You're a genius!

Best to you!!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now