Displaying 1901-01-01

Ramco Ramco

3y1.1k.NET

I haveb below code in query . I want when count(*) = 0 then it should display it as '' but it is displaying 1901-01-01.

,case when(select count(*) from [PRQS].[dbo].[SentBackLog] where 
	  and ModuleCode = T0.ModuleCode and EntryType = 'Submit') = 0 THEN CAST(''  as datetime)
	  else (select top 1 EntryDate from [PRQS].[dbo].[SentBackLog] where 
	  and ModuleCode = T0.ModuleCode and EntryType = 'Submit') End as 'Last Date'
FROM [EntryLog] T0
  where T0.ModuleCode = 2

Thanks

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke3y ago
In case when ifTrue then ifFalse end, clause the data type of both ifTrue and ifFalse expression SHOULD be of the SAME data type.

In your case you are casting empty string to datetime, which defaults to 1-1.1901 by SQL Server. You can test this by a simple select statement - select cast('' as datetime).

Null is probably your best choice. Or otherwise make it string and 'No last date' and date as a string in the column.

Hope it clarifies.

Amit Mohanty3y ago

Check this, it will return as NULL instade of '1900-01-01'

case when (select count(*) from [PRQS].[dbo].[SentBackLog] 
           where ModuleCode = T0.ModuleCode and EntryType = 'Submit') = 0 THEN NULL
	 else (select top 1 EntryDate from [PRQS].[dbo].[SentBackLog] 
           where ModuleCode = T0.ModuleCode and EntryType = 'Submit') 
end as 'Last Date'

Vishal Joshi3y ago

Try this

Just Replace CAST('' as datetime) with ''

,case when(select count(*) from [PRQS].[dbo].[SentBackLog] where
	  and ModuleCode = T0.ModuleCode and EntryType = 'Submit') = 0 THEN ''
	  else (select top 1 EntryDate from [PRQS].[dbo].[SentBackLog] where
	  and ModuleCode = T0.ModuleCode and EntryType = 'Submit') End as 'Last Date'
FROM [EntryLog] T0
  where T0.ModuleCode = 2

Ramco Ramco3y ago
Hi Amit

still not working. If no record return then what will be the value

Thanks

Amit Mohanty3y ago

try this

case when (select top 1 EntryDate from [PRQS].[dbo].[SentBackLog] 
		   where ModuleCode = T0.ModuleCode and EntryType = 'Submit') IS NULL THEN ''
	 when (select top 1 EntryDate from [PRQS].[dbo].[SentBackLog] 
	       where ModuleCode = T0.ModuleCode and EntryType = 'Submit') = '1901-01-01 00:00:00' THEN ''
	 else (select top 1 EntryDate from [PRQS].[dbo].[SentBackLog] 
	       where ModuleCode = T0.ModuleCode and EntryType = 'Submit') 
End as 'Last Date'

Ramco Ramco3y ago
same issue

Amit Mohanty3y ago

try this

case when(select count(*) from [PRQS].[dbo].[SentBackLog] where
	  and ModuleCode = T0.ModuleCode and EntryType = 'Submit') = 0 THEN ''
	  else (select top 1 EntryDate from [PRQS].[dbo].[SentBackLog] where
	  and ModuleCode = T0.ModuleCode and EntryType = 'Submit') End as 'Last Date'