SQL Server: Use different date format in Select when selected date equals current date

Question

I have a table with a column "modTime" formatted as datetime. To fetch dates from this column I use the following line in my Select which returns a date in the format DD MMM YYYY:

CONVERT(VARCHAR(11), G.modTime, 106) AS modTime

Is there a way that I can return the date in a different format (like the following) when it matches the current date and only otherwise use the above format ? This returns the date as Today at hh:mm.

('Today at ' + CONVERT(VARCHAR(5), G.modTime, 108)) AS modTime

Both ways work when I use them separately but I couldn't find a way to combine them using CASE etc.

This strikes me as something that should probably be done UI-side by your code, rather than loaded onto SQL. It's not a bad question, but you might want to reconsider your architecture if you need to do this in T-SQL. — Matthew Haugen, Sep 13 '14 at 07:33
Thanks for this. Yes, I can do it in PHP as well but I wasn't sure what is the better approach here. — user2571510, Sep 13 '14 at 07:38
@MatthewHaugen you are right, but I think doing this at database side might give us some performance benefits at UI-side. — Amit Mittal, Sep 13 '14 at 07:39
Yeah, that's what I thought as well, esp. as I have to do this for multiple dates in one select. — user2571510, Sep 13 '14 at 07:39

score 2 · Answer 1 · edited May 23 '17 at 12:29

2

You can try this:

select iif(G.modTime=getdate(),('Today at ' + CONVERT(VARCHAR(5), G.modTime, 108)),CONVERT(VARCHAR(11),G.modTime, 106) ) from <table name>

Please note that IIF works only with SQL Server 2012 or later.

http://msdn.microsoft.com/en-IN/library/hh213574.aspx

For older versions, this post might help you:

SQL Server 2008 IIF statement does not seem enabled

edited May 23 '17 at 12:29

Community

1
1

answered Sep 13 '14 at 07:26

Amit Mittal

1,129
11
30

Thanks for this ! I use SQL Server 2008 so the above wouldn't work but I'll check the link you provided. – user2571510 Sep 13 '14 at 07:30

score 1 · Accepted Answer · answered Sep 13 '14 at 07:40

you will not match to getdate() using equals, and you need to set getdate()'s time to midnight

select
      case when G.modTime >= dateadd(day, datediff(day,0, getdate() ), 0)
                then ('Today at ' + CONVERT(VARCHAR(5), G.modTime, 108)) 
           else
                CONVERT(VARCHAR(11), G.modTime, 106)
      end  AS modTime
from G

Above I have used: dateadd(day, datediff(day,0, getdate() )

Instead you could use: cast(getdate() as date)

both, have the effect of giving you "today" at 00:00:00

SQL Server: Use different date format in Select when selected date equals current date

2 Answers2