Help with validating date

[kizziebutler]

I am exporting a lot of data using BCP. I know that some of the fields that I am exporting are in the incorrect format or not valid date. I need to write a function or procedure to check against the date and put the correct date out in the correct format. Please could I ask assistance on this, I was looking at something like this:

CREATE function dbo.DateValid(@DateOut varchar(4), @DateIn char(3))
returns date
AS

BEGIN
declare@ret date getdate()
SET @ret = 0
IF (@Date LIKE 'YYYY/MM/DD HH:MM:SS' or
etc...
) AND
@PostcodeIn LIKE ' '
set@ret = Validdate
RETURN @ret
end

 

[vongrunt]

ISDATE()?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[kizziebutler]

Isdate() ? any more help

 

[amrita418]

isdate(datefield) returns 1 if the value is a valid date. Else returns 0.

Try something like this to list all not date values:

select     column_name
from       TableA
where      isdate(column_name) = 0

Regards,
AA

 

[kizziebutler]

I have tried the above, but it doesnot return it as a valid date as it is stored as a char field rather than a daetime field.

 

[jbenson001]

The above code only tells you if it is NOT a valid date.
1 = Valid Date
0 = Invalid Date

 

[kizziebutler]

The field it self contains valid date, but because it is set up as a char character is fails to recognise the date. Trying to find out how to convert field into a datetime and then carryout the validation.

 

[jbenson001]

how is the data stored in the column...

mm/dd/yyyy ??

 

[kizziebutler]

The data is contained as a date field such as 20050701 I can display this correctly if I do a convert(varhchar(9),field),120).

 

[jbenson001]

i just tried your code and it displays like this:

2005-07-0.. not a valid date...

 

[kizziebutler]

change it yo varchar(10)

 

[vongrunt]

Again: what is wrong with

select isdate('20050701')

?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[kizziebutler]

I need to validate against all the fiels in the database.

 

[vongrunt]

Sorry, my Vulcan head hurts

CREATE function dbo.DateValid(@DateOut varchar(4), @DateIn char(3))
returns date

Confusion #1: what are @DateOut and @DateIn, why varchar(4) and char(3)... and where @PostCodeIn came from?

I have tried the above, but it doesnot return it as a valid date as it is stored as a char field rather than a daetime field.

That's the point of validation: check whether string expression can be evaluated/converted as a valid date or not.

Trying to find out how to convert field into a datetime and then carryout the validation.

Not possible. If conversion fails, script stops.

I need to validate against all the fiels in the database.

That's another problem. Let's solve single validation issues first.

Some questions:

- incoming string "dates" are in yyyymmdd format, right?
- if validation fails, what do you want to be returned?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[TJRTech]

Kizziebutler says:

"I need to validate against all the fiels in the database."

I really suspect you don't mean that.

Because when you say fields, I hear "columns", not values or or records.

I suspect you mean that there are several known var/char fields in several tables that you need to validate to see that the record values of said are datetimes.

If so, the the general way to check ONE value is to use isdate(). To check ALL the records, you need to use isdate() as part of a WHERE clause used to select out good or bad records.

TJR

 

[kizziebutler]

The datebase that I am validating against was set up before my time, so I was ask to extract all the data using bcp. However, one of my biggest problem is one field that contains fields such as 20050302 and it also contains null values. I thought of writing a function to validate the date and return it as 2005/03/02 or if it is a null values then return getdate(), because it is a char field I field that the isdate is not going to work. I thought of something like this, what do you think.

CREATE Function [dbo].[udf_Validdate] (@Pattern varchar(10),@String varchar(10))
Returns Bit
AS


BEGIN
SET @Pattern=Replace(Replace(@Pattern,'[1900 - 2006]','[1-12]'),'[1-31]')
RETURN CASE WHEN @String LIKE @Pattern THEN 1 ELSE 0 END
End

 

[kizziebutler]

or perphaps!!! your expertise is important

CREATE FUNCTION dbo.DateToIso
(
@DateForConversion datetime
)
RETURNS varchar(10)
AS
BEGIN

DECLARE @IsoDate varchar(8)

SET @IsoDate = CONVERT(varchar(8),@DateForConversion,112)

RETURN SUBSTRING(@IsoDate , 1 , 4) + '/' + SUBSTRING(@IsoDate, 5,2) + '/' + SUBSTRING(@IsoDate, 7, 2)

END

 

[TJRTech]

Kizzie, you have two threads where you are working on the same problem; that is frowned upon here.

The other thread is:

http://www.tek-tips.com/viewthread.cfm?qid=1102759

Note that isdate() does work against char fields, even with nulls.

Consider the following and their results in comments:

select isdate('1/20/2005')    -- 1
select isdate('20050125')     -- 1   
select isdate('1/20/2005xxx') -- 0
select isdate('01202005')     -- 0
select isdate(NULL)           -- 0

I think that you need to simply write an update statement, and run it against each table and column to fix the dates in a batch mode...I wouldn't create a UDF unless you have some reason to reuse it elsewhere.

Assuming you have a table called FOO and a char column called DATESTR, the following update sets all NON-date values to the current date time:

UPDATE FOO
SET DATESTR=convert(varchar, GetDate())
WHERE isdate(DATESTR)=0

TJR

 

[kizziebutler]

isdate definitely does not work with char fields. The above will update every single field with the getdate, will not work. I was perphaps trying something like this:

CREATE FUNCTION fn_GetDateFromIntegerDate(@IntegerDate int)
RETURNs VARCHAR(10)
AS


BEGIN
DECLARE @YEAR INT
DECLARE @MONTH INT
DECLARE @DAY INT
DECLARE @DISPLAY VARCHAR(12)
SELECT @YEAR = DATEPART( YY , DATEADD( DD, @IntegerDate , '1900-01-01') ) ,
@MONTH = DATEPART( MM , DATEADD( DD, @IntegerDate , '1900-01-01') ) ,
@DAY = DATEPART( DD , DATEADD( DD, @IntegerDate , '1900-01-01') )
SET @DISPLAY = CONVERT(VARCHAR(2),@MONTH) +'-'+CONVERT(VARCHAR(2),@DAY)+'-'+CONVERT(VARCHAR(4),@YEAR)
RETURN(@DISPLAY)
END

 

[TJRTech]

Sorry, but I am sure you are wrong. Isdate DOES work on char fields...that is what it was meant for.

This test shows that it works:

CREATE TABLE #FOO (
  DATESTR varchar(25),
  bWasDate bit,
)

INSERT INTO #FOO values('1/20/2005', 1)  
INSERT INTO #FOO values('20050125', 1)       
INSERT INTO #FOO values('1/20/2005xxx', 0) 
INSERT INTO #FOO values('01202005', 0)     
INSERT INTO #FOO values(NULL, 0 ) 

UPDATE #FOO
SET DATESTR=convert(varchar, GetDate())
WHERE isdate(DATESTR)=0

SELECT * FROM #FOO

--DROP TABLE #FOO

Clearly you can see the values inserted INTO the #FOO table, the first two are CORRECT date strings, the last three are NOT (one is a null even).

The results in the table AFTER running the update are:

DATESTR bWasDate
----------------------- ----------------
1/20/2005 1
20050125 1
Aug 4 2005 8:52PM 0
Aug 4 2005 8:52PM 0
Aug 4 2005 8:52PM 0

Clearly the use of isdate in the WHERE clause of the update only found and updated the records where DATESTR wasn't a date string.

Try it on your system, see what you get.

TJR