How to Return Only the Date from a SQL Server Datetime Datatype

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 return only the Date with formate from a SQL Server DateTime datatype

If your SQL-server version higher than 2012 you can try to use FORMAT function to make it.

SELECT FORMAT(getdate(),'dd,MMMM,yyy')

sqlifddle

---

Or you can use DATENAME function get the name to be your expect result.

SELECT DATENAME(day,Getdate())+','+DATENAME(month,Getdate())+','+DATENAME(year,Getdate())

sqlfiddle

Result

10,September,2018

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 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 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'

or

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

Other options are:

1. If you have control over the database schema, and you don't need the
   time data, take it out.

2. 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'

How to return only the year and month from a SQL Server DateTime datatype

SELECT FORMAT ( GETDATE() , 'yyyy-MM' )

Ok so if you have a Table named MyTable with a Column named DateCol of Type DateTime you can use the query below:

SELECT FORMAT ( DateCol , 'yyyy-MM' ) FROM MyTable 

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.

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

---

Related Topics