Return date only from getdate()

Question

Post reply

Return date only from getdate()

Mary Elizabeth Lake

SSC Veteran

Points: 286
More actions
June 3, 2004 at 10:52 am

#111548

Can someone help me with the syntax for returning the date only from getdate()? In other words, the results I want is
2004-06-03 as opposed to 2004-06-03 09:47:12.097
I've been trying to figure out cast or convert, but I'm not having much success - or perhaps I'm simply going about this the wrong way? Ultimately I really want yesterdays date (getdate()-1) but first I need to get rid of the time...
Thanks from a newbie...
Mary Elizabeth

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Michel Tremblay Newbie Points: 6 More actions · Answer 1

Michel Tremblay

Newbie

Points: 6

June 3, 2004 at 11:15 am

#508872

select convert(char(10), getdate()-1,121)

Mary Elizabeth Lake SSC Veteran Points: 286 More actions · Answer 2

Thank you Michel - the select statement you suggested gives me exactly what I asked for - the date. But apparently what I asked for and what I need are not the same thing...

The column in question is a datetime column, but I only care about the date. I am trying to get a count of records that were created on a given date.

If I query like this:

WHERE [datetime] > '2004-06-02' AND [datetime] < '2004-06-03'

I get all the records created on June 2nd (45,037)

But if I query like this:

WHERE [datetime] LIKE convert(char(10), getdate()-1,121)

I get zero records returned. Am I missing something?

Thanks!

Mary Elizabeth Lake SSC Veteran Points: 286 More actions · Answer 3

Hmm. Well the following query works, and returns all the records created yesterday... but I don't know if it's the most efficient way to do it...?

select count(*) from

WHERE [datetime] > convert(char(10), getdate()-1,121)

AND [datetime] < convert(char(10), getdate(),121)

tran008 Ten Centuries Points: 1002 More actions · Answer 4

what is the [datetime] look like? You may want to convert it.

select count(*) from table where convert(char(10),datetime,121)=convert(char(10),getdtate()-1,121)

Chiara SSC Veteran Points: 212 More actions · Answer 5

and what about using....

...WHERE convert(char(10), [datetime],121) LIKE convert(char(10), getdate()-1,121)

instead of...

...WHERE [datetime] LIKE convert(char(10), getdate()-1,121)

where, obviously, [datetime] is your fieldname

Ciao, Chiara

Mary Elizabeth Lake SSC Veteran Points: 286 More actions · Answer 6

For what it's worth, this is what I ended up with:

print 'Total number of incoming SMTP messages for ' + convert(char(10), getdate()-1,121)
select count(*) from
WHERE convert(char(10), [datetime],121) LIKE convert(char(10), getdate()-1,121)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Alternatively if you want to avoid the conversion to char

select count(*) from

WHERE

cast(floor(cast([datetime] as float)) as datetime) = cast(floor(cast(getdate() as float)) as datetime)

The above is faster than a char comparison, especially if you have large volumes of data

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

David Gilkinson Ten Centuries Points: 1334 More actions · Answer 8

Try where datediff(dd, [datetime], '2004-06-02' ) = 0

you can also add

[datetime] >= '2004-06-02'

if you need to pick up an index on datetime

Jeet SSCommitted Points: 1536 More actions · Answer 9

We have found need to do this quite often so we capsulated the logic in a function. This was your code stays nice and concise and the logic can be as complicated as neccessary

Thanks Jeet

KW Valued Member Points: 56 More actions · Answer 10

Assuming you are using a view or procedure, you could also use something like the following:

declare @StartDate datetime

declare @StopDate datetime

set @StartDate = convert(char(10),Getdate()-1, 121)

--returns yesterday's date with time stamp = 00:00:00

set @StopDate = @StartDate + '23:59:59.999'

--returns yesterday's date with time stamp = 23:59:59.999, you can also use convert/getdate as above

--set @StopDate = convert(char(10), Getdate(), 121) OR

--set @StopDate = @StartDate + 1

select Count(*) from Table where DateTimeColumn between @StartDate and @StopDate

ZenDada SSCarpal Tunnel Points: 4637 More actions · Answer 11

I don't like to waste an unnecessary function call when I use this so often I don't forget anyway:

SELECT CAST(CONVERT(VARCHAR(15),GETDATE(),101) AS DATETIME)AS "TruncAndCastDateTime"

GO

SELECT CAST(CONVERT(VARCHAR(15),GETDATE(),101) AS SMALLDATETIME)AS "TruncAndCastSmallDateTime"

GO

By the way, smalldatetime is half the size of datetime. Saves a lot of space. If you are truncing anyway, you don't need datetime.

[font="Courier New"]ZenDada[/font]

Brian Cidern SSC Veteran Points: 263 More actions · Answer 12

FYI - when I need to counts by date on a DateTime field where the time value is populated, I just zero out the time value with

SELECT CAST(Convert(VarChar(8), GetDate(), 112) AS DateTime) ...

Brian Cidern SSC Veteran Points: 263 More actions · Answer 13

Whoops -- I added my post without going to page two, and now I see that TnJMoseley posted the exact same thing...