I am trying to format a date and everytime I run it DBIsam gives me a nonsense error that change with each fix I do. Below is the code any help would be greatly appreciated.

"
DROP TABLE IF EXISTS Temp_Report;
CREATE TABLE Temp_Report(FieldNo VARCHAR(222));

INSERT INTO Temp_Report(FieldNo)
VALUES
('PID=xxxxxx',' ','XXXXXXX',' ','SID=xxxxxx',' ','XXXXXXXX',' ','START','  ',+CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS VARCHAR(10))+CAST(EXTRACT(MONTH FROM CURRENT_DATE) AS VARCHAR(10))+CAST(EXTRACT(WEEK FROM CURRENT_DATE) AS VARCHAR(10)),' ','3.0.0';
;
FROM blah
"

benjiej

> I am trying to format a date and everytime I run it DBIsam gives me a
nonsense error that change with each fix I do. Below is the code any help
would be greatly appreciated.
>
> "
> DROP TABLE IF EXISTS Temp_Report;
> CREATE TABLE Temp_Report(FieldNo VARCHAR(222));
>
> INSERT INTO Temp_Report(FieldNo)
> VALUES
> ('PID=xxxxxx',' ','XXXXXXX',' ','SID=xxxxxx',' ','XXXXXXXX',' ','START','
',+CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS VARCHAR(10))+CAST(EXTRACT(MONTH
FROM CURRENT_DATE) AS VARCHAR(10))+CAST(EXTRACT(WEEK FROM CURRENT_DATE) AS
VARCHAR(10)),' ','3.0.0';
> ;
> FROM blah
> "
>

Can you show an example of the string you are trying to get into fieldno?
The statement above doesn't make it all thar clear.

John

benjiej


I have no idea what you're trying to achieve but this

INSERT INTO Temp_Report(FieldNo)
VALUES
('PID=xxxxxx'+' '+
'XXXXXXX'+' '+
'SID=xxxxxx'+' '+
'XXXXXXXX'+' '+
'START'+''+
CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS VARCHAR(10))+
CAST(EXTRACT(MONTH FROM CURRENT_DATE) AS VARCHAR(10))+
CAST(EXTRACT(WEEK FROM CURRENT_DATE) AS VARCHAR(10))+' '+'3.0.0')


works

Roy Lambert [Team Elevate]

Roy thank you for the help. What I am trying to do is format a report and my spacing and date well everything has to be exact. I am good with simple SQL queries but this is the most complicated I have tried to date. As I keep saying my GEEK is hanging out because I am learning a lot of new things. I took what you showed me and had to make a few changes but now the field is populating but the date isn't entirely correct. I am try to get yymmdd. Right now I get yymd. I have attached my query.
"
INSERT INTO Temp_Report(FieldNo)
VALUES
('PID=xxxxxx'+' '+
'XXXXXXXX'+' '+
'SID=xxxxxx'+' '+
'XXXXXXXX'+' '+
'START'+'  '+
RIGHT(CAST(EXTRACT(YEAR FROM CURRENT_DATE)AS VARCHAR(10)),2)+
IF(LENGTH(MONTH)=1,'0'+MONTH,MONTH)(CAST(EXTRACT(MONTH FROM CURRENT_DATE)AS VARCHAR(10)))+
IF(LENGTH(DAY)=1,'0'+DAY,DAY)(CAST(EXTRACT(WEEK FROM CURRENT_DATE)AS VARCHAR(10)))+' '+'3.0.0')
;
"

benjiej

I am try to get yymmdd. Right now I get yymd. I have attached my query.
> "
> INSERT INTO Temp_Report(FieldNo)
> VALUES
> ('PID=xxxxxx'+' '+
> 'XXXXXXXX'+' '+
> 'SID=xxxxxx'+' '+
> 'XXXXXXXX'+' '+
> 'START'+'  '+
> RIGHT(CAST(EXTRACT(YEAR FROM CURRENT_DATE)AS VARCHAR(10)),2)+
> IF(LENGTH(MONTH)=1,'0'+MONTH,MONTH)(CAST(EXTRACT(MONTH FROM
CURRENT_DATE)AS VARCHAR(10)))+
> IF(LENGTH(DAY)=1,'0'+DAY,DAY)(CAST(EXTRACT(WEEK FROM CURRENT_DATE)AS
VARCHAR(10)))+' '+'3.0.0')

For the date part try

right(replace('-' with '' in cast(current_date as char(10))),8)

John

Use this for the last part of the expression:

RIGHT(CAST(EXTRACT(YEAR FROM CURRENT_DATE)AS VARCHAR(10)),2)+
RIGHT('0'+CAST(EXTRACT(MONTH FROM CURRENT_DATE)AS VARCHAR(10)),2)+
RIGHT('0'+CAST(EXTRACT(DAY FROM CURRENT_DATE)AS VARCHAR(10)),2)+

in order to get yymmdd.

--
Tim Young
Elevate Software
www.elevatesoft.com

benjiej

I am try to get yymmdd. Right now I get yymd. I have attached my query.
> "
> INSERT INTO Temp_Report(FieldNo)
> VALUES
> ('PID=xxxxxx'+' '+
> 'XXXXXXXX'+' '+
> 'SID=xxxxxx'+' '+
> 'XXXXXXXX'+' '+
> 'START'+'  '+
> RIGHT(CAST(EXTRACT(YEAR FROM CURRENT_DATE)AS VARCHAR(10)),2)+
> IF(LENGTH(MONTH)=1,'0'+MONTH,MONTH)(CAST(EXTRACT(MONTH FROM
CURRENT_DATE)AS VARCHAR(10)))+
> IF(LENGTH(DAY)=1,'0'+DAY,DAY)(CAST(EXTRACT(WEEK FROM CURRENT_DATE)AS
VARCHAR(10)))+' '+'3.0.0')

oops that sould be

right(replace('-' with '' in cast(current_date as char(10))),6)

John

Thanks everyone the RIGHT gave me the format I needed. I don't know why I stopped with just the year. This is the first SQL forum I have used that has been helpful. Keep up the good work.

Benjie

benjiej

I don't know which version  of DBISAM you're using but if its one of the more modern ones I would check out engine custom functions. These allow you to write a function in Delphi and use it in your SQL

Custom Functions
You can now add custom functions for use with filters and SQL
statements. They can be used anywhere that a normal, nonaggregate
function would be used. All arguments to the functions are
required and there is no facility currently for optional arguments. The
Functions property of the TDBISAMEngine component allows you to
specify the functions and their arguments, and the
OnCustomFunction event of the TDBISAMEngine component allows
you to implement the functions. Please see the Customizing the
Engine topic for more information.


They are a little less efficient than raw SQL but allow you to produce results like your's quickly, easily and most importantly comprehensibly so that in a years time you can still read it and understand what its doing.

Roy Lambert[Team Elevate]