• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 46
  • Last Modified:

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
0
RIAS
Asked:
RIAS
  • 10
  • 7
1 Solution
 
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now