zberg007
asked on
How to convert correct DateTime function
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:
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!
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)
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!
How are to retrieving data from backend.. Are you using stored procs??
Try using type cast..
http://www.mssqltips.com/tip.asp?tip=1145
Try using type cast..
http://www.mssqltips.com/tip.asp?tip=1145
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".
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))
ASKER
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!
Incompatible types: 'String' and 'TDateTime'
Thanks!
What does the StripIT function do?
does it return a TDateTime?
does it return a TDateTime?
ASKER
Here's my StripIT function:
Function StripIT( s: string) : string;
Function StripIT( s: string) : string;
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)
ASKER
Qlemo, can you provide an example please?
Thanks again!
Thanks again!
ASKER
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...
How would be best to validate my date before calling StrToDateTime?
Thanks again...
My Delphi times are long gone, but I'll try:
strDate=StripIT(fieldByNam e('Date Received').AsString;
ParamByName('@DReceived'). Value := concat(Copy(strDate, 7, 4), LeftStr(strDate, 2), Copy(strDate, 4, 2), Copy(strDate, 11, 8));
strDate=StripIT(fieldByNam
ParamByName('@DReceived').
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
ASKER
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?
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?
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
ASKER
Qlemo, When I run this:
ParamByName('@DTaken').Val ue := 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?
ParamByName('@DTaken').Val
I get this for error:
Missing operator or semicolon
Do you know where an operator or semicolon is missing?
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;
ASKER
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?
"02/21/2011 13:01"
this included the quotations as well.
Thanks... any ideas?
Looks like the quotations may be the problem then
Modify the function to
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;
ASKER
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...
raised exception class EOleException with message 'Application uses a value of the wrong type for the current operation'.
Thanks...
ASKER
hang on ewangoya... I haven't ran your latest. be right back!
You call it this way
ParamByName('@DReceived').
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');
ASKER
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?
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?
ASKER
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:
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;
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
ASKER
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!
ASKER
I just tried this simple function:
'Conversion failed when converting datetime from character string'.
ParamByName('@DReceived').Value := ExtractWordL(1,'Date Received', ' ');
and it results in the same exact error of:'Conversion failed when converting datetime from character string'.
ASKER
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!
Thanks again!
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;
ASKER
I just looked at the stored procedure and here's what it has:
@DReceived VarChar(12),
@DReceived VarChar(12),
ASKER
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...
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...
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;
ASKER
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!
'Conversion failed when converting datetime from character string'.
I apologize for being such a pain! Thanks for all you're doing here!
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
ASKER
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)
ASKER
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:
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!
ParamByName('@DReceived').Value := StripIT(FieldByName('Date Received').AsString);
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ewangoya... that worked! Thank you, thank you for sticking with this! You're a genius!
Best to you!!
Best to you!!
Open in new window