Link to home
Start Free TrialLog in
Avatar of RIAS
RIASFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Date format

Hello,
How to return a blank when the date column in an sql table has value  '1/1/1900'

I am looking for a ' select ColDate from Table1'

Regards
Avatar of Mark Wills
Mark Wills
Flag of Australia image

well for a datetime, 1/1/1900 is a 'zero' date.

but you can say:

Select iif(coldate <= '19000101', '',coldate) as coldate from table1

Open in new window


edit : probably best to say < '19000102'
And will need to cast as char
Avatar of RIAS

ASKER

Thanks, will try and brb.
Avatar of RIAS

ASKER

Mark,
I am using this query in my SP and it errors at

Incorrect syntax near '19000101'.
Referring to my post above, rather than cryptic comments
select iif(coldate < '19000102','',cast(coldate as char(8))) coldate from table1

Open in new window

Avatar of RIAS

ASKER

Nope, Still the same
Query in my Sp is:



 DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'SELECT   ColDate from Table1'
Ahhh... Dynamic SQL = need to double up the single quotes

set @sql = 'select iif(coldate < ''19000102'','''',convert(char(10), coldate, 101)) from table1'

Open in new window


And style code  ie 101 in the convert will give you the format... or could use format()
Avatar of RIAS

ASKER

Thanks, trying
Avatar of RIAS

ASKER

Nope, The query syntax is fine but I still get '1/1/1900'
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RIAS

ASKER

ColDate is only Date column
Avatar of RIAS

ASKER

Mark it worked mate!Thanks a lot!
Still, it does work for me if I change coldate to DATE datatype. Strange.

Try this quick 'test'
create table #table1 (coldate date)
insert #table1 values ('19000101'),('19000202'),(getdate())

declare @sql varchar(2000)

set @sql = 'select iif(coldate < ''19000102'','''',convert(char(10), coldate, 101)) coldate from #table1'

exec (@sql)

Open in new window

Phew... That's a big relief, thought I was going batty for a while.

Very happy it worked for you :)

Cheers,
Mark
Avatar of RIAS

ASKER

Mark is there any reason why the format of date  is changed ?
Avatar of RIAS

ASKER

Thanks!
Blank '' is not allowed in a date/datetime data type, so most developers I know would pass a NULL in this situation, and let the presentation/reporting layer interpret that and display the blank ''.
Avatar of RIAS

ASKER

Thanks Jim