[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to convert correct DateTime function

Posted on 2011-03-02
37
Medium Priority
?
1,176 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 20
  • 12
  • 2
  • +2
37 Comments
 
LVL 12

Expert Comment

by:Hypo
ID: 35021163
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
ID: 35021173
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 71

Expert Comment

by:Qlemo
ID: 35021175
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35021177

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
ID: 35021304
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
ID: 35021322
What does the StripIT function do?

does it return a TDateTime?
0
 

Author Comment

by:zberg007
ID: 35021342
Here's my StripIT function:

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

Expert Comment

by:Ephraim Wangoya
ID: 35021500

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
ID: 35021501
Qlemo, can you provide an example please?

Thanks again!
0
 

Author Comment

by:zberg007
ID: 35021579
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 71

Expert Comment

by:Qlemo
ID: 35021590
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:Ephraim Wangoya
ID: 35021652

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
ID: 35021882
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:Ephraim Wangoya
ID: 35021970

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
ID: 35022092
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:Ephraim Wangoya
ID: 35022102
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
ID: 35022116
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:Ephraim Wangoya
ID: 35022161
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
 

Author Comment

by:zberg007
ID: 35022196
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
ID: 35022206
hang on ewangoya... I haven't ran your latest. be right back!
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35022237

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
ID: 35022249
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
ID: 35022328
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:Ephraim Wangoya
ID: 35022530

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
ID: 35022694
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
ID: 35022738
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
ID: 35022956
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:Ephraim Wangoya
ID: 35023017

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
ID: 35023148
I just looked at the stored procedure and here's what it has:

@DReceived VarChar(12),
0
 

Author Comment

by:zberg007
ID: 35023190
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:Ephraim Wangoya
ID: 35023212

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
ID: 35023271
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:Ephraim Wangoya
ID: 35023297

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
ID: 35023355
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
ID: 35029000
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:
Ephraim Wangoya earned 2000 total points
ID: 35029120
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
ID: 35029315
ewangoya... that worked! Thank you, thank you for sticking with this! You're a genius!

Best to you!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

649 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