date format - 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 2008 Forums

Transact-SQL (2008)

date format

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2015-04-30 : 11:06:14

The sql below give me the following:
From 04-30-15 to 01-01-01

I don't want "01-01-01".... I want
From 04-30-15 to <Blank>

declare @irequireddate date = null
declare @drequireddate date = '2015-04-30'

select 'From ' + CONVERT(varchar(10), ISNULL(@drequireddate,'0001-01-01'), 10) + ' to ' + CONVERT(varchar(10), ISNULL(@irequireddate,'0001-01-01'), 10)
where ISNULL( @irequireddate,'0001-01-01') <> ISNULL(@drequireddate,'0001-01-01')

Dave
Helixpoint Web Development
http://www.helixpoint.com

Kristen
Test

22859 Posts

Posted - 2015-04-30 : 11:27:49

Change


CONVERT(varchar(10), ISNULL(@irequireddate,'0001-01-01'), 10)


CONVERT(varchar(10), ISNULL(@irequireddate, ''), 10)

Note that you should not use '2015-04-30' style for your dates as it is ambiguous and not guaranteed to work consistently. Use "yyyymmdd" instead (which is guaranteed UNambiguous) e.g. '20150430'. Same for your '0001-01-01' values

e.g. if the current user had a different language setting:


SET LANGUAGE FRENCH
GO
select convert(datetime, '2015-04-30')

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2015-04-30 : 12:43:36

I used
CONVERT(varchar(10), ISNULL(CONVERT(VARCHAR(23), @irequireddate,10),''))

Dave
Helixpoint Web Development
http://www.helixpoint.com

Kristen
Test

22859 Posts

Posted - 2015-04-30 : 14:10:50

Sorry, that would work better!!

You only need this much though:


COALESCE(CONVERT(varchar(10), @irequireddate, 10), '')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources