To Get Date from Datetime in SQL

How to return only the Date from a SQL Server DateTime datatype

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me

2008-09-22 00:00:00.000

Pros:

No varchar<->datetime conversions required
No need to think about locale

How to get DATE from DATETIME column?

you can use CAST(column_name AS DATE) for Sql-server 2008 and above version

SELECT [name], [book_id], [book_name], 
cast([taken_date] as date) as [taken_date], 
cast([last_date] as date) as [last_date], 
cast([renewed_date] as date) as [renewed_date], 
[status], [comment], [fine_amount] 
FROM [library] WHERE ([admn_no] = @admn_no)

EDIT 1

for earlier version before sqlserver 2008 you can do it like

SELECT CONVERT(VARCHAR(10),GETDATE(),111)

so the whole query will go like this

SELECT [name], [book_id], [book_name], 
CONVERT(VARCHAR(10),[taken_date],111) as [taken_date], 
CONVERT(VARCHAR(10),[last_date],111) as [last_date], 
CONVERT(VARCHAR(10),[renewed_date],111) as [renewed_date], 
[status], [comment], [fine_amount] 
FROM [library] WHERE ([admn_no] = @admn_no)

EDIT 2

for formatting your date from yyyy/dd/mm to dd/mm/yyyy you can change to 101 rather than 111

SELECT CONVERT(VARCHAR(10),GETDATE(),101)

so query will be

SELECT [name], [book_id], [book_name], 
CONVERT(VARCHAR(10),[taken_date],101) as [taken_date], 
CONVERT(VARCHAR(10),[last_date],101) as [last_date], 
CONVERT(VARCHAR(10),[renewed_date],101) as [renewed_date], 
[status], [comment], [fine_amount] 
FROM [library] WHERE ([admn_no] = @admn_no)

How to get Time from DateTime format in SQL?

SQL Server 2008:

SELECT cast(AttDate as time) [time]
FROM yourtable

Earlier versions:

SELECT convert(char(5), AttDate, 108) [time]
FROM yourtable

Extract date from datetime column - SQL Server Compact

SQL Server Compact has no date type.

If you don't want to see the time, convert the datetime value to a string:

SELECT CONVERT(nvarchar(10), GETDATE(), 120)

(This has been tested and actually works against SQL Server Compact)

How to query DATETIME field using only date in Microsoft SQL Server?

use range, or DateDiff function

 select * from test 
 where date between '03/19/2014' and '03/19/2014 23:59:59'

 select * from test 
 where datediff(day, date, '03/19/2014') = 0

Other options are:

If you have control over the database schema, and you don't need the
time data, take it out.
or, if you must keep it, add a computed column attribute that has the time portion of the date value stripped off...

Alter table Test Add DateOnly As DateAdd(day, datediff(day, 0, date), 0)

or, in more recent versions of SQL Server...

Alter table Test Add DateOnly As Cast(DateAdd(day, datediff(day, 0, date), 0) as Date)

then, you can write your query as simply:

select * from test 
where DateOnly = '03/19/2014'

Sql Server Select Only Date Part From DateTime

I think the following is a better way to do what you want:

where date >= dateadd(day, 0, datediff(day, 0, getdate()) - 1)

This truncates the current date to midnight yesterday, which I am guessing is what you really want.

For your method, try using format 120:

SELECT TOP 100 *
FROM [n].[a2].[DOTABLE]
WHERE CONVERT(VARCHAR(10), data, 120) > DATEADD(DAY, - 1, getdate())
ORDER BY data DESC;

You can do this on both sides:

SELECT TOP 100 *
FROM [n].[a2].[DOTABLE]
WHERE CONVERT(VARCHAR(10), data, 120) > CONVERT(varchar(10), DATEADD(DAY, - 1, getdate()), 120)
ORDER BY data DESC;

This format is YYYY-MM-DD which is useful for comparisons.

Then, upgrade SQL Server, and use the date data type instead.

Most efficient way in SQL Server to get date from date+time?

I must admit I hadn't seen the floor-float conversion shown by Matt before. I had to test this out.

I tested a pure select (which will return Date and Time, and is not what we want), the reigning solution here (floor-float), a common 'naive' one mentioned here (stringconvert) and the one mentioned here that I was using (as I thought it was the fastest).

I tested the queries on a test-server MS SQL Server 2005 running on a Win 2003 SP2 Server with a Xeon 3GHz CPU running on max memory (32 bit, so that's about 3.5 Gb). It's night where I am so the machine is idling along at almost no load. I've got it all to myself.

Here's the log from my test-run selecting from a large table containing timestamps varying down to the millisecond level. This particular dataset includes dates ranging over 2.5 years. The table itself has over 130 million rows, so that's why I restrict to the top million.

SELECT TOP 1000000 CRETS FROM tblMeasureLogv2 
SELECT TOP 1000000 CAST(FLOOR(CAST(CRETS AS FLOAT)) AS DATETIME) FROM tblMeasureLogv2
SELECT TOP 1000000 CONVERT(DATETIME, CONVERT(VARCHAR(10), CRETS, 120) , 120) FROM tblMeasureLogv2 
SELECT TOP 1000000 DATEADD(DAY, DATEDIFF(DAY, 0, CRETS), 0) FROM tblMeasureLogv2

SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.

(1000000 row(s) affected) Table 'tblMeasureLogv2'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 422 ms, elapsed time = 33803 ms.

(1000000 row(s) affected) Table 'tblMeasureLogv2'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 625 ms, elapsed time = 33545 ms.

(1000000 row(s) affected) Table 'tblMeasureLogv2'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 1953 ms, elapsed time = 33843 ms.

(1000000 row(s) affected) Table 'tblMeasureLogv2'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 531 ms, elapsed time = 33440 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.

What are we seeing here?

Let's focus on the CPU time (we're looking at conversion), and we can see that we have the following numbers:

Pure-Select:  422
Floor-cast:   625
String-conv: 1953
DateAdd:      531

From this it looks to me like the DateAdd (at least in this particular case) is slightly faster than the floor-cast method.

Before you go there, I ran this test several times, with the order of the queries changed, same-ish results.

Is this something strange on my server, or what?

how to get current datetime in SQL?

Complete answer:

1. Is there a function available in SQL?

Yes, the SQL 92 spec, Oct 97, pg. 171, section 6.16 specifies this functions:

CURRENT_TIME       Time of day at moment of evaluation
CURRENT_DATE       Date at moment of evaluation
CURRENT_TIMESTAMP  Date & Time at moment of evaluation

2. It is implementation depended so each database has its own function for this?

Each database has its own implementations, but they have to implement the three function above if they comply with the SQL 92 specification (but depends on the version of the spec)

3. What is the function available in MySQL?

NOW() returns 2009-08-05 15:13:00  
CURDATE() returns 2009-08-05  
CURTIME() returns 15:13:00

Oracle SQL SELECT DATE from DATETIME field

TO_DATE (REPORTDATE, 'DD.MON.YYYY')

This makes no sense. You are converting a date into a date again. You use TO_DATE to convert a string literal into DATE.

I want result to return only 29.10.2013

You could use TRUNC to truncate the time element. If you want to use this value for DATE calculations, you could use it directly.

For example,

SQL> select TRUNC(SYSDATE) dt FROM DUAL;

DT
---------
12-MAR-15

To display in a particular format, you could use TO_CHAR and proper FORMAT MASK.

SQL> SELECT to_char(SYSDATE, 'DD.MM.YYYY') dt from dual;

DT
----------
12.03.2015

SQL>