iainmac
asked on
Q about date validation in a BRIO SQR program
I asked this Q in the DB Reporting TA but I'm not getting any takers so I thought I'd give it a shot here.
I have written a program which reads an input text file (tab delimited fields), validates the data and inserts some records into a MSSQL 2000 database.
One of the fields is a date, meant to be in the format DD/MM/YYYY.
Under declare-variable I have described $mydate as a date field.
I use the command ...... let $mydate = strtodate(&inputdate,'DD/M
But when I put an invalid date (eg 30/02/2004) into my test data, the BRIO program crashes.
Which is fair enough I guess. So I need to validate the date before I put it thru the strtodate command.
Which brings us to my question. I don't want to reinvent the wheel and write a comprehensive date validation routine in a language I'm struggling with. Does BRIO offer a tool or command I can use? Or do any of you guys have some BRIO code that would be helpful?
Thanks for any advice.
I have written a program which reads an input text file (tab delimited fields), validates the data and inserts some records into a MSSQL 2000 database.
One of the fields is a date, meant to be in the format DD/MM/YYYY.
Under declare-variable I have described $mydate as a date field.
I use the command ...... let $mydate = strtodate(&inputdate,'DD/M
But when I put an invalid date (eg 30/02/2004) into my test data, the BRIO program crashes.
Which is fair enough I guess. So I need to validate the date before I put it thru the strtodate command.
Which brings us to my question. I don't want to reinvent the wheel and write a comprehensive date validation routine in a language I'm struggling with. Does BRIO offer a tool or command I can use? Or do any of you guys have some BRIO code that would be helpful?
Thanks for any advice.
ASKER
Hi Esteban,
I'm using a BRIO SQR program to read a flat file (text file) of records and then insert records into various tables in a Microsoft SQL 2000 database.
I can't see an IsDate() function in BRIO. Do you mean it is in BRIO, or is it an MSSQL thing? What I need is a BRIO solution.
I'm using a BRIO SQR program to read a flat file (text file) of records and then insert records into various tables in a Microsoft SQL 2000 database.
I can't see an IsDate() function in BRIO. Do you mean it is in BRIO, or is it an MSSQL thing? What I need is a BRIO solution.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Lowfatspread,
Thanks for your input.
Yeah, I have stumbled upon and used the SQL error checking but the instruction that crashes my program is not SQL. It is "strtodate", which lets you convert a string containing a date into a date field.
It's beginning to sound as though I will need to develop my own SQR date validation routine. Quel drag.
Thanks for your input.
Yeah, I have stumbled upon and used the SQL error checking but the instruction that crashes my program is not SQL. It is "strtodate", which lets you convert a string containing a date into a date field.
It's beginning to sound as though I will need to develop my own SQR date validation routine. Quel drag.
then i'd suggest again
insert into a staging table in sql server
and then use the sqr - sql interface
and use isdate/isnumeric sqk server funbction etc on the select to achieve what you require....
insert into a staging table in sql server
and then use the sqr - sql interface
and use isdate/isnumeric sqk server funbction etc on the select to achieve what you require....
ASKER
Thanks guys,
I will try the staging table approach.
Iain
I will try the staging table approach.
Iain
the IsDate() functions do this...
btw, you are using Microsoft SQl server, right?
Esteban Felipe
www.estebanf.com