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 do I get just the date when using MSSQL GetDate()?
Slight bias to SQL Server
- Best approach to remove time part of datetime in SQL Server
- Most efficient way in SQL Server to get date from date+time?
Summary
DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
SQL Server 2008 has date
type though. So just use
CAST(GETDATE() AS DATE)
Edit: To add one day, compare to the day before "zero"
DATEADD(day, DATEDIFF(day, -1, GETDATE()), 0)
From cyberkiwi:
An alternative that does not involve 2 functions is (the +1 can be in or ourside the brackets).
DATEDIFF(DAY, 0, GETDATE() +1)
DateDiff
returns a number but for all purposes this will work as a date wherever you intend to use this expression, except converting it to VARCHAR directly - in which case you would have used the CONVERT approach directly on GETDATE(), e.g.
convert(varchar, GETDATE() +1, 102)
How to get just the date part of getdate()?
If you are using SQL Server 2008 or later
select convert(date, getdate())
Otherwise
select convert(varchar(10), getdate(),120)
How to select date without time in SQL
I guess he wants a string.
select convert(varchar(10), '2011-02-25 21:17:33.933', 120)
120 here tells the convert function that we pass the input date in the following format: yyyy-mm-dd hh:mi:ss
.
How to get only the date(mm/dd/yyyy) by using the GETDATE()?
You can convert it to a DATE
SELECT CONVERT(DATE, GETDATE())
Or convert it to a VARCHAR
SELECT CONVERT(CHAR(10), GETDATE(), 101)
The 101 is the format you've requested
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)
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 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
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
Related Topics
How to Give a Unique Constraint to a Combination of Columns in Oracle
Diff Between Top 1 1 and Select 1 in SQL Select Query
SQL Function Return-Type: Table VS Setof Records
Column Conflicts with the Type of Other Columns in the Unpivot List
Postgresql: Not in Versus Except Performance Difference (Edited #2)
Why Are Joins Bad When Considering Scalability
How to Connect an Existing SQL Server Login to an Existing SQL Server Database User of Same Name
Database/SQL Tx - Detecting Commit or Rollback
Update or Insert (Multiple Rows and Columns) from Subquery in Postgresql
How to Manually Execute SQL Commands in Ruby on Rails Using Nuodb
How to Get Multiple Rows into One Line as a String
Cte to Traverse Back Up a Hierarchy
Postgresql Recursive Self Join
Isdate Function in SQL Evaluates Invalid Dates as Valid
SQL Most Recent Using Row_Number() Over Partition
SQL Select Multi-Columns into Multi-Variable