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)

to

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')
Go to Top of Page

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
Go to Top of Page

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), '')
Go to Top of Page
   

- Advertisement -