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
RIASAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
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
0
RIASAuthor Commented:
Thanks, will try and brb.
0
RIASAuthor Commented:
Mark,
I am using this query in my SP and it errors at

Incorrect syntax near '19000101'.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Mark WillsTopic AdvisorCommented:
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

0
RIASAuthor Commented:
Nope, Still the same
Query in my Sp is:



 DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'SELECT   ColDate from Table1'
0
Mark WillsTopic AdvisorCommented:
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()
0
RIASAuthor Commented:
Thanks, trying
0
RIASAuthor Commented:
Nope, The query syntax is fine but I still get '1/1/1900'
0
Mark WillsTopic AdvisorCommented:
which is why we have the convert in there - if it thinks it is delivering a datetime, then it will produce a zero date which is 19000101

declare @sql varchar(2000)

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

exec (@sql)

Open in new window

Gives my test :
create table #table1 (coldate datetime)
insert #table1 values ('19000101'),('19000202'),(getdate())
/*
coldate
                                               -- 19000101 is blank
02/02/1900
02/06/2018
*/

Open in new window


Or maybe coldate is not a datetime ?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RIASAuthor Commented:
ColDate is only Date column
0
RIASAuthor Commented:
Mark it worked mate!Thanks a lot!
0
Mark WillsTopic AdvisorCommented:
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

0
Mark WillsTopic AdvisorCommented:
Phew... That's a big relief, thought I was going batty for a while.

Very happy it worked for you :)

Cheers,
Mark
0
RIASAuthor Commented:
Mark is there any reason why the format of date  is changed ?
0
Mark WillsTopic AdvisorCommented:
0
RIASAuthor Commented:
Thanks!
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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 ''.
0
RIASAuthor Commented:
Thanks Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.