Databases
StackOverflow2013
Postsdbo
POselect null within a select statement in SP when date is 01-01-1900
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POselect null within a select statement in SP when date is 01-01-1900
    primarykey
    data
    text
    <p>I have a stored Procedure is as follows. In my table of sales_order there is a date column <code>CP_Created_On</code>. In table for some lines date I have inserted as <code>01-01-1900</code>. Now I am selecting records from the table. I want to select <code>NULL</code> when date for some rows is <code>01-01-1900</code>. I want it in this fashion because I am exporting the records to an excel via a <code>DATASET</code>. Please help</p> <p>Thanks.</p> <p>set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go</p> <pre><code>ALTER PROCEDURE [dbo].[Sale_OrderPlan_View_Excel_test] --'EM00164466' -- Add the parameters for the stored procedure here -- Add the parameters for the stored procedure here ( @SO_AM varchar(30) ) AS BEGIN SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; declare @Cnt int CREATE TABLE #Tab (SYNC_BY VARCHAR(20)) INSERT INTO #Tab (SYNC_BY) select SYNC_BY from dbo.SYNC_Master where MONTH(SYNC_Last_Sync_Date)=MONTH(GETDATE()) and YEAR(SYNC_Last_Sync_Date)=YEAR(GETDATE()) set @Cnt=(select count(*) from #Tab) drop table #Tab -- ACCEPTING INPUT AS "title_code" HAVING VALUE (-1) SELECT SO_Code as 'Form10 Code', SO_ProfitCentre as'Profit Centre', -- SO_DivCode as 'SO_DivCode', SO_SalesOffice as 'Sales Office', SO_F_Customer as 'F-Customer Code', SO_CustName as 'F-Customer Name', SO_L_Customer as 'L-Customer', SO_RefDoc as 'Ref Doc', SO_invoiceNo as 'Invoice No', SO_DocType as 'Doc Type', SO_DocDesc as 'Doc Desc', SO_AccDocNo as 'Acc Doc No', -- SO_SplGLindicator as 'Spl GL indicator', SO_SplGLDesc as 'Spl GL Desc', convert(varchar,SO_DocDate,105) as 'Doc Date', SO_OutstandingAmt as 'Outstanding Amt', SO_CreditBal as 'Credit Balance', SO_PureAdvances as 'Pure Advances', SO_UnadjustableCrBal as 'Uneducable Cr Bal', convert(varchar,SO_BaseDate,105) as 'Base Date', SO_LegacyInvoiceRef as 'Legacy Invoice Ref', SO_WBS as 'WBS', tbl_Region_Master.REGIONNAME as 'Region', tbl_Branch_Master.BRANCHNAME as 'Branch', dbo.TBL_DIVISIONGROUP.GROUPNAME as 'Div Group', SO_SubDiv as 'Sub Div', SO_OrderType as 'Order Type', SO_Usage as 'Usage', SO_AgeInDays as 'Age In Days', SO_LYCY as 'LY/CY', convert(varchar,SO_invoiceDt,105) as 'Invoice Date', SO_InvoiceAmt as 'Invoice Amt', SO_SalesOrderNO as 'Sales Order NO', SO_OrderValue as 'Order Value', SO_PoNo as 'Po No', convert(varchar,SO_PoDate,105) as 'Po Date', Dl.NAME1+' '+Dl.NAME2 as 'Dealer Associate', SO_Write_Off as 'Write Off', SO_Adjustments as 'Adjustments', SO_CreditNote as 'CreditNote', SO_OtherDeduction as 'Other Deduction', SO_TDS as 'TDS', SO_NetCollectable as 'NetCollectable', SO_CurrentMnth as 'Current Month', SO_NextMnth as 'Next Month', SO_NexttoNextMnth as 'Next to Next Month', E1.NAME1+' '+E1.NAME2 as 'SDE', tbl_EmpMaster.NAME1+' '+tbl_EmpMaster.NAME2 as 'SME', E2.NAME1+' '+E2.NAME2 as 'AM', E3.NAME1+' '+E3.NAME2 as 'RM', E4.NAME1+' '+E4.NAME2 as 'AIH', E5.NAME1+' '+E5.NAME2 as 'COMEXE', SO_BUSFIN as 'BUSFIN', SO_CF as 'CF', SO_PlanCode as 'Plan Code', SO_WriteOff_Reason as 'WriteOff Reason', SO_Adjustments_Reason as 'Adjustments Reason', SO_CreditNote_Reason as 'CreditNote Reason', SO_OtherDeduction_Reason as 'Other Deduction Reason', convert(varchar,CP_Created_On,105) as 'Created On', E7.NAME1+' '+E7.NAME2 as 'Approved By' FROM dbo.tbl_Sale_Order left join dbo.tbl_Region_Master on tbl_Sale_Order.SO_Region=dbo.tbl_Region_Master.REGION_ID left join dbo.tbl_Branch_Master on tbl_Sale_Order.SO_Branch=dbo.tbl_Branch_Master.SALES_BRANCH_CODE left join dbo.TBL_DIVISIONGROUP on tbl_Sale_Order.SO_DivGroup=dbo.TBL_DIVISIONGROUP.GROUPCODE left join dbo.tbl_EmpMaster E1 on tbl_Sale_Order.SO_SDE=E1.PORTALUSERNAME left join tbl_EmpMaster on tbl_Sale_Order.SO_SME=tbl_EmpMaster.PORTALUSERNAME left join tbl_EmpMaster E2 on tbl_Sale_Order.SO_AM=E2.PORTALUSERNAME left join tbl_EmpMaster E3 on tbl_Sale_Order.SO_RM=E3.PORTALUSERNAME left join tbl_EmpMaster E4 on tbl_Sale_Order.SO_AIH=E4.PORTALUSERNAME left join tbl_EmpMaster E5 on tbl_Sale_Order.SO_COMEXE=E5.PORTALUSERNAME -- left join tbl_EmpMaster E6 on tbl_Sale_Order.CP_Created_By=E6.PORTALUSERNAME left join tbl_EmpMaster E7 on tbl_Sale_Order.Approved_By=E7.PORTALUSERNAME left join dbo.tbl_Dealer_Master Dl on tbl_Sale_Order.SO_Dealer_Associate=Dl.DEALERCODE WHERE tbl_Sale_Order.SO_AM=@SO_AM and SO_OutstandingAmt&gt;0 and SO_CreditBal=0 and @Cnt&gt;0 and month(Creation_date)=Month(getdate()) and year(Creation_date)=year(getdate()) END </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USAnkur
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COGoogle "Select case when", i think thats what you need
      singulars
      1. POselect null within a select statement in SP when date is 01-01-1900
      1. USRob van der Veer
    2. COI tried doing that but it is not helping. This is what I am writing case when CP_Created_On =01-01-1900 then '' when CP_Created_On !=01-01-1900 then CP_Created_On end as 'Created On' ,
      singulars
      1. POselect null within a select statement in SP when date is 01-01-1900
      1. USAnkur
    3. COYou must wrap the date into proper sql date. I'm not behind a computer now, but i don't think your syntax is right. (Case when else), but i see others are helping out with syntax..
      singulars
      1. POselect null within a select statement in SP when date is 01-01-1900
      1. USRob van der Veer
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload