CODEWITHSUNDEEP
sqlsql-serverdatedatetime
jackhammer013
jackhammer013

Reputation: 2297

Remove date in Datetime from SQL Server GETDATE() Function

I'm making a query that fetches the records for the current date regardless of the time, how can I make it on SQL Server? My version of SQL Server doesn't support DATE for conversion. I want a format like:

2013-06-20

So for example my query is:

SELECT * FROM sometable where date = GETDATE();

Where GETDATE() is in a format 2015-06-19 19:12:10.953. I want it to be 2015-06-19 in the query.

Thanks.

Upvotes: 0

Views: 2990

Answers (5)

i486
i486

Reputation: 6563

CONVERT(VARCHAR, GETDATE(), 120) will make the date to ISO format, then SUBSTRING will get only date part:

SELECT * 
FROM sometable 
WHERE date = SUBSTRING(CONVERT(VARCHAR, GETDATE(), 120), 1, 10)

Upvotes: 2

t-clausen.dk
t-clausen.dk

Reputation: 44326

Since your version of sqlserver does not support DATE conversion (you must be using sqlserver 2005 or older), you need to recalculate getdate().

SELECT *
FROM
  sometable
WHERE
  date >= dateadd(d, datediff(d,0, GETDATE()), 0)
  and date < dateadd(d, datediff(d,0, GETDATE()), 1)

If your date is declared as a char(10) or similar, you can simply use this where clause:

WHERE date = convert(char(10), GETDATE(), 126)

This is the interval that will be included. Excluding todate:

SELECT 
  dateadd(d, datediff(d,0, GETDATE()), 0) fromdate,
  dateadd(d, datediff(d,0, GETDATE()), 1) todate

Result:

fromdate                  todate
2015-06-19 00:00:00.000   2015-06-20 00:00:00.000

Upvotes: 0

User Learning
User Learning

Reputation: 3473

You may try CAST : like this : 

SELECT CAST(GETDATE() AS DATE) 

SELECT CAST(date AS DATE) FROM UserMaster where date = GETDATE();

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You could express the query as:

SELECT *
FROM sometable 
WHERE date = cast(GETDATE() as date);

However, an alternative way to phrase this is:

SELECT *
FROM sometable 
WHERE date >= cast(GETDATE() as date) and
      date < cast(dateadd(day, 1, GETDATE()) as date)

This version is more guaranteed to use an appropriate index, if available.

Upvotes: 4

JJRS
JJRS

Reputation: 37

Please try following

SELECT * FROM sometable where DATEDIFF(DAY, date,GETDATE())=0;

Thanks

Upvotes: 0

Related Questions