check for date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

check for date

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-07-24 : 07:05:47

Hi,
I would like to place a null if the column value is not a date.
Is this correct please?
case
when not isdate([Column 6]) then null

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-07-24 : 07:10:17

This is what I get now.
case
when not isdate([Column 6]) then 0

Error is:
An expression of non-boolean type specified in a context where a condition is expected, near 'then'.

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-07-24 : 07:16:31

select nullif(isdate([column 6]),0)

or

select case when isdate([column 6]) = 0 then null else [column 6] end

TCC

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-07-24 : 07:21:02

quote:
Originally posted by tishri

select nullif(isdate([column 6]),0)

or

select case when isdate([column 6]) = 0 then null else [column 6] end

TCC

How can I correct this query please?

select
case
when len([Column 6]) = 0 then ''
when lower([Column 6]) = 'unknown' then ''
when isdate([Column 6]) then substring(ltrim([Column 6]), 1, 4) + substring(ltrim([Column 6]), 6, 2) + substring(ltrim([Column 6]), 9, 2)
when not isdate([Column 6]) then ''

this is the error:
An expression of non-boolean type specified in a context where a condition is expected, near 'then'.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-07-24 : 07:22:22

Please note I forgot to mention the error is on the first isdate
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 07:23:37

isdate(........) = 1

Peter Larsson
Helsingborg, Sweden

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-07-24 : 07:25:50

case
when len([Column 6]) = 0 then ''
when lower([Column 6]) = 'unknown' then ''
when isdate([Column 6]) then 1

This gives the same error.
Please note I would like to do the substring work if the column is a date.
Thanks

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-07-24 : 07:25:58

select
case
when len([Column 6]) = 0 then ''
when lower([Column 6]) = 'unknown' then ''
when isdate([Column 6]) = 1 then substring(ltrim([Column 6]), 1, 4) + substring(ltrim([Column 6]), 6, 2) + substring(ltrim([Column 6]), 9, 2)
when not isdate([Column 6]) then ''

TCC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 07:26:36

Or you could just replace all code above with

SELECT CASE WHEN ISDATE(NULLIF([Column 6], 'unknown')) = 1 THEN CONVERT(VARCHAR(8), [Column 6], 112) ELSE '' END

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 07:27:23

quote:
Originally posted by tishri

select
case
when len([Column 6]) = 0 then ''
when lower([Column 6]) = 'unknown' then ''
when isdate([Column 6]) = 1 then substring(ltrim([Column 6]), 1, 4) + substring(ltrim([Column 6]), 6, 2) + substring(ltrim([Column 6]), 9, 2)
when not isdate([Column 6]) then ''

Tishri, don't forget the last ISDATE(...) = 0
Or use my suggestion above

Peter Larsson
Helsingborg, Sweden

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-07-24 : 07:28:39

use this


select case when isdate([column 6]) =1 then 
                    substring(ltrim([Column 6]), 1, 4) + substring(ltrim([Column 6]), 6, 2) + substring(ltrim([Column 6]), 9, 2) 
       else ''
end

TCC

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-07-24 : 07:30:53

his trying to make a substring of [column 6] if column 6 is date so i think theres nothing wrong with my query.. hehehe

TCC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources