Todays Date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Todays Date

Author

Topic

gigaghostuk
Starting Member

2 Posts

Posted - 2009-07-24 : 11:25:35

Hi guys

I'm slowly but surely learning about SQL and what it can do for my web projects.

Can you help me with some code I need to produce.

I have a small datetime type number which I have to check if it is today (any time today) and if it is I need to return a value like this below

TODAY at 12:33

If it isn't today then I need to produce the complete date and time like this

12/07/2010 12:33

Can anybody point me in the right direction??

P.S I haven't produced any code as of yet.

gigaghostuk

SQL Script Newbie

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-07-24 : 11:53:52

You can play around with the formating, it should be done in the front end anyway

DECLARE @Date datetime
DECLARE @Today datetime

SET @Today = getdate()
SET @Date = '07/24/2009 10:14:23 AM'

SELECT CASE
WHEN @Date >= DATEADD(day,DATEDIFF(day,0,@Today),0)
and @Date < DATEADD(day,DATEDIFF(day,0,@Today),1)
THEN 'TODAY at ' + CONVERT(varchar(5),@date,108)
ELSE convert(varchar(17),@date,113)
END

Jim

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-07-26 : 12:57:02

Just as a minor tweak to Jim's good code, you don't actually need to convert the date back to a date for the comparison... comparing the number of days will suffice.

DECLARE @Date datetime
DECLARE @Today datetime

 SELECT @Today = GETDATE(),
        @Date = '07/26/2009 10:14:23 AM'

 SELECT CASE 
        WHEN DATEDIFF(dd,0,@Date) = DATEDIFF(dd,0,@Today)
        THEN 'TODAY at ' + CONVERT(VARCHAR(5),@Date,108)
        ELSE CONVERT(VARCHAR(17),@Date,113)
        END

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

gigaghostuk
Starting Member

2 Posts

Posted - 2009-07-27 : 09:01:35

wow thanks guys for your help

SQL Script Newbie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-27 : 09:59:32

And even simpler

SELECT	CASE DATEDIFF(DAY, @Date, @Today)
		WHEN 0 THEN 'TODAY at ' + CONVERT(VARCHAR(5), @Date, 108)
		ELSE CONVERT(VARCHAR(17), @Date, 113)
        END

N 56°04'39.26"
E 12°55'05.63"

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-07-27 : 11:15:43

quote:

Originally posted by Peso

And even simpler

SELECT	CASE DATEDIFF(DAY, @Date, @Today)
		WHEN 0 THEN 'TODAY at ' + CONVERT(VARCHAR(5), @Date, 108)
		ELSE CONVERT(VARCHAR(17), @Date, 113)
        END

Heh... no fair, Peter... you had coffee. ;-)

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-27 : 12:41:49

Coffee!?
Try two kids on speed...

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources