SQL语句写法，大家来看看。

2012-12-18

求一个SQL语句写法，大家来看看。。。在数据库中有个字段为ReturnDate，为datetime类型，该列有个系统给的默认值

求一个SQL语句写法，大家来看看。。。
在数据库中有个字段为ReturnDate，为datetime类型，该列有个系统给的默认值“1900-1-1”，我想在查出来的时候，不显示这个默认值，如果ReturnDate为默认值的时候，查询结果中此列的值为空字符串，请问，我要如何在语句中实现呢？附上查询语句：


select ID, OrderID, LastName, FirstName, CardholderSinceDate, AddressStreet1, Mobilephone, City, StateOrProvince, PostalCode, CountryCode, CustomerID, LoyaltyCardNumber, GoldCardnumber, GoldCardPIN, Email, StoreNumber, EMSFirstName, EMSLastName, DataType, IsError, CreateDate, ImportUser, Batch, ShipDate, ExpressTrackingCode, UpdateDate, UpdateUser, 
ReturnReason, ReturnDate, TID, IsHandle, DataSource, IsUpdateBOH
from GoldCardReplacementData
where LoyaltyCardNumber='6010581952359804'

[最优解释]
select ID, OrderID, LastName, FirstName, CardholderSinceDate, AddressStreet1, Mobilephone, City, StateOrProvince, PostalCode, CountryCode, CustomerID, LoyaltyCardNumber, GoldCardnumber, GoldCardPIN, Email, StoreNumber, EMSFirstName, EMSLastName, DataType, IsError, CreateDate, ImportUser, Batch, ShipDate, ExpressTrackingCode, UpdateDate, UpdateUser, ReturnReason, case when convert(varchar(10),ReturnDate,23)='1900-1-1' then '' else convert(varchar(10),ReturnDate,23) end ReturnDate, TID, IsHandle, DataSource, IsUpdateBOHfrom GoldCardReplacementDatawhere LoyaltyCardNumber='6010581952359804'
[其他解释]

select   ReturnDate=CASE WHEN CONVERT(DATETIME, ReturnDate) = '1900-1-1' THEN ''     ELSE ReturnDate  END from GoldCardReplacementData WHERE   LoyaltyCardNumber = '6010581952359804'

[其他解释]

SELECT  ID ,
        OrderID ,
        LastName ,
        FirstName ,
        CardholderSinceDate ,
        AddressStreet1 ,
        Mobilephone ,
        City ,
        StateOrProvince ,
        PostalCode ,
        CountryCode ,
        CustomerID ,
        LoyaltyCardNumber ,
        GoldCardnumber ,
        GoldCardPIN ,
        Email ,
        StoreNumber ,
        EMSFirstName ,
        EMSLastName ,
        DataType ,
        IsError ,
        CreateDate ,
        ImportUser ,
        Batch ,
        ShipDate ,

        ExpressTrackingCode ,
        UpdateDate ,
        UpdateUser ,
        ReturnReason ,
        CASE WHEN CONVERT(DATETIME, ReturnDate) = '1900-1-1' THEN ''
             ELSE ReturnDate
        END ReturnDate ,
        TID ,
        IsHandle ,
        DataSource ,
        IsUpdateBOH
FROM    GoldCardReplacementData
WHERE   LoyaltyCardNumber = '6010581952359804'

[其他解释]

case when ReturnDate='1900-1-1' then '' else convert(varchar(10),ReturnDate,120)) end

热点排行

SQL Server

SQL语句写法，大家来看看。