locked
Null Value RRS feed

  • Question

  • User-1499457942 posted

    Hi

      How to check for Null Value & 1753-01-01 for Date DataType in Stored Procedure

    Thanks

    Saturday, November 17, 2018 11:10 AM

Answers

  • User-369506445 posted

    <g class="gr_ gr_7 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="7" data-gr-id="7">also</g> you can try below query

    select 
    isnull(
    (case 
         when ConfirmDate = CONVERT(date, '1753-01-01') then ''
         else cast(ConfirmDate as varchar)
     end),'')
    
    from #Temp

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, November 17, 2018 1:08 PM

All replies

  • User-369506445 posted

    hi

    suppose you have a table below like :

    CREATE TABLE [dbo].[MyTestTable](
    	[Id] [int] NULL,
    	[MyDate] [date] NULL
    ) ON [PRIMARY]
    
     

    now you can check it below like

    select * from [dbo].[MyTestTable]
    where MyDate is not null and myDate>CONVERT(datetime, '1753-01-01')

    also, you can use it in a stored procedure

    Saturday, November 17, 2018 11:33 AM
  • User-1499457942 posted

    Hi Vahid

      I want to check for = 

    1753-01-01

    Thanks
    Saturday, November 17, 2018 11:38 AM
  • User-369506445 posted

    below query return rows that are not null and equals with  1753-01-01

    select * from [dbo].[MyTestTable]
    where MyDate is not null and myDate=CONVERT(datetime, '1753-01-01')

    Saturday, November 17, 2018 12:08 PM
  • User-1499457942 posted

    Hi Vahid

      I have one field ConfirmDate . In select Statement i want if it is null or 1753-01-01 then it should return '' else value. I am trying like below but it is showing now 1900-01-01

    Case when (A.[ConfirmDate] = '1753-01-01') then '' else Isnull(ConfirmDate,'')

    Thanks

    Saturday, November 17, 2018 12:28 PM
  • User-369506445 posted
    but it is showing now 1900-01-01

    You can't get an empty string because you're returning <g class="gr_ gr_86 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="86" data-gr-id="86">the </g>DATE<g class="gr_ gr_86 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style multiReplace" id="86" data-gr-id="86"> value</g> type <g class="gr_ gr_87 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="87" data-gr-id="87">from </g>ISNULL<g class="gr_ gr_87 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style multiReplace" id="87" data-gr-id="87">.</g>

    you have to convert Date to  string below  like :

    Case when (A.[ConfirmDate] = '1753-01-01') then '' else isnull(cast(ConfirmDate as varchar),'')

    Saturday, November 17, 2018 12:41 PM
  • User-1499457942 posted

    Hi

      I have tried your code but it is showing 01/01/1900 in Gridview . I dont know how it is showing 01/01/1900

    Thanks

    Saturday, November 17, 2018 12:48 PM
  • User-369506445 posted

    it depended on your grid view, please open a new window in your SQL explore and put below code and run it

    create table #Temp
    (
       ConfirmDate Date
    )
    
    insert into #Temp values (null);
    insert into #Temp values (getdate())
    
    select isnull(cast(ConfirmDate as varchar),'') from #Temp

    you can see it return null for the first row, not 01/01/1900

    Saturday, November 17, 2018 12:55 PM
  • User-1499457942 posted

    Hi Vahid

      In Gridview i have below line

    <asp:label runat="server" id="lblInActiveDate" text='<%#Eval("InActiveDate", "{0:dd/MM/yyyy}") %>'></asp:label>

    Thanks

    Saturday, November 17, 2018 12:56 PM
  • User-369506445 posted

    <g class="gr_ gr_7 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="7" data-gr-id="7">also</g> you can try below query

    select 
    isnull(
    (case 
         when ConfirmDate = CONVERT(date, '1753-01-01') then ''
         else cast(ConfirmDate as varchar)
     end),'')
    
    from #Temp

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, November 17, 2018 1:08 PM