If date is null then '' instead of 1900-01-01

Question

Hi

In below Sql i want in inner select statement if record is not found then Space should be returned.

select
  [LogID]
      ,[ModuleCode]
      ,[ModuleMasterCode]
      ,[EntryCode]
      ,[EmpCID]
      ,[EmpName]
      ,[StepCode]
      ,[EntryDate]
	  ,isnull((select top 1 EntryDate from [PRQS].[dbo].[BackLog] where  and StepCode = T0.StepCode and EntryCode = T0.EntryCode),'')
  FROM [LiveData].[dbo].[EntryLog] T0
  where T0.EntryCode = '100'

Thanks

Pankaj Patel · Answer

Hi Ramco Ramco, "If date is null then '' instead of 1900-01-01" can be achieved with CASE WHEN as like following: DECLARE @Cars TABLE ( Id int NOT NULL, ModelName varchar(20), DateEntered datetime ) INSERT INTO @Cars (Id, ModelName, DateEntered) VALUES (1, 'Austen', GETDATE()) INSERT INTO @Cars (Id, ModelName, DateEntered) VALUES (2, 'Alto', GETDATE()) INSERT INTO @Cars (Id, ModelName, DateEntered) VALUES (3, 'Swift', null) INSERT INTO @Cars (Id, ModelName, DateEntered) VALUES (4, 'Safari', '') SELECT * FROM @Cars SELECT Id, ModelName, DateEntered as DateEntered_Old, CASE WHEN DateEntered = '1900-01-01 00:00:00.000' THEN NULL ELSE DateEntered END AS DateEntered_New FROM @Cars Output: Id ModelName DateEntered_Old DateEntered_New 1 Austen 2022-11-10 18:43:46.003 2022-11-10 18:43:46.003 2 Alto 2022-11-10 18:43:46.003 2022-11-10 18:43:46.003 3 Swift NULL NULL 4 Safari 1900-01-01 00:00:00.000 NULL Hope, this will help you!

Jignesh Kumar · Answer

Hi Ramco, You can use below query, select [LogID] ,[ModuleCode] ,[ModuleMasterCode] ,[EntryCode] ,[EmpCID] ,[EmpName] ,[StepCode] ,[EntryDate] ,(select top 1 ISNULL(CASE WHEN CONVERT(DATE, EntryDate ) = '1900-01-01' THEN '' ELSE CONVERT(CHAR(10),EntryDate , 103) END, '') from [PRQS].[dbo].[BackLog] where and StepCode = T0.StepCode and EntryCode = T0.EntryCode) FROM [LiveData].[dbo].[EntryLog] T0

Vishal Joshi · Answer

Hello Please check the below code. select [LogID] ,[ModuleCode] ,[ModuleMasterCode] ,[EntryCode] ,[EmpCID] ,[EmpName] ,[StepCode] ,[EntryDate] ,isnull((select top 1 EntryDate from [PRQS].[dbo].[BackLog] where and StepCode = T0.StepCode and EntryCode = T0.EntryCode),CAST('' as datetime)) FROM [LiveData].[dbo].[EntryLog] T0 where T0.EntryCode = '100'