RIAS
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
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
ASKER
Thanks, will try and brb.
ASKER
Mark,
I am using this query in my SP and it errors at
Incorrect syntax near '19000101'.
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
ASKER
Nope, Still the same
Query in my Sp is:
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'SELECT ColDate from Table1'
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
And style code ie 101 in the convert will give you the format... or could use format()
set @sql = 'select iif(coldate < ''19000102'','''',convert(char(10), coldate, 101)) from table1'
And style code ie 101 in the convert will give you the format... or could use format()
ASKER
Thanks, trying
ASKER
Nope, The query syntax is fine but I still get '1/1/1900'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ColDate is only Date column
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'
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)
Phew... That's a big relief, thought I was going batty for a while.
Very happy it worked for you :)
Cheers,
Mark
Very happy it worked for you :)
Cheers,
Mark
ASKER
Mark is there any reason why the format of date is changed ?
Yep, that is the STYLE code I mentioned in #a42459165 above.
Easy to change read https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql#date-and-time-styles
Easy to change read https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql#date-and-time-styles
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 ''.
ASKER
Thanks Jim
but you can say:
Open in new window
edit : probably best to say < '19000102'
And will need to cast as char