CODEWITHSUNDEEP
sqlsql-serversql-server-2008
punter
punter

Reputation: 460

only date and only time in same column - sql server

I have a web portal that should display only time if the date is today's date otherwise it should display date .

dossier_date_created       |   Expected result
----------------------------------------------------
2013-10-22 16:18:46.610    |     2013-10-22
2013-10-23 11:26:56.390    |     11:26

I tried something like this :

select 
case 
when CONVERT(date,dossier_date_created) = CONVERT(DATE,getdate()) then convert(char(5), dossier_date_created, 108) 
else convert(date, dossier_date_created) 
end as timedate
 from Proj_Manage_Dossier

But the result was :

     timedate
    -----------
    2013-10-22
    1900-01-01

How can I do it only with SQL? And btw Datatype of my column "dossier_date_created" is datetime

Upvotes: 0

Views: 162

Answers (3)

Vineeth Vijayan
Vineeth Vijayan

Reputation: 1329

This will work

select case when cast(dossier_date_created as date) = cast(getdate() as date) 
    then cast(cast(dossier_date_created as time) as varchar)
    else cast(cast(dossier_date_created as date) as varchar)
    end from #Proj_Manage_Dossier

Hope it helps :)

cheers......

Upvotes: 0

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

You can just use CAST or CONVERT to get your values to DATE or TIME datatypes. But, since you can't combine two data types in same column you have to cast them both to something identical - like NVARCHAR afterwards:

SELECT CASE WHEN CAST(dossier_date_created AS DATE) = CAST(GETDATE() AS DATE) 
            THEN CAST(CAST (dossier_date_created AS TIME) AS NVARCHAR(10))
    ELSE CAST(CAST(dossier_date_created AS DATE) AS NVARCHAR(10)) END
FROM dbo.Proj_Manage_Dossier

SQLFiddle DEMO

EDIT - For SQL Server versions older then 2008, where there are no DATE and TIME datatypes - You can also directly CONVERT to VARCHAR using appropriate style codes.

SELECT CASE WHEN CONVERT(VARCHAR(10),dossier_date_created,102) = CONVERT(VARCHAR(12),GETDATE(),102)
            THEN CONVERT(VARCHAR(10),dossier_date_created,108)
    ELSE CONVERT(VARCHAR(10),dossier_date_created,102) END
FROM dbo.Proj_Manage_Dossier

SQLFiddle DEMO

Upvotes: 5

Hitesh
Hitesh

Reputation: 3880

This works fine for me:

    select 
      case 
      when (CONVERT(date,PaymentDate) = CONVERT(DATE,getdate()))
       then convert(VARCHAR(15), PaymentDate, 108) 
      else convert(varchar, PaymentDate, 101) 
      end as timedate
    from Payments

Hope it will help you.. :)

Upvotes: 1

Related Questions