Just a week ago, my Database Team member asked me what is the best way to only select date part from datetime. When ran following command it also provide the time along with the date.
SELECT GETDATE()
ResultSet: 2007-06-10 7:00:56.107
The required outcome was only 2007/06/10.
I asked him to come up with solution by using date functions. The method he suggested was to use
SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
I approved his method though, I finally suggested my method using function CONVERT.
SELECT CONVERT(VARCHAR(10),GETDATE(),111)
The reason I use this because it is very convenient as well as provides quick support to convert the date in any format. The table which suggests many formats are displayed on MSDN.
Some claims that using CONVERT is slower than using DATE functions, but it is extremely negligible. I prefer to use CONVERT.
Here is the video discussing the same concepts.
Let me know what is your favorite method to select only date part from datetime. I would like to know what are the different methods and if any other method is interesting I will be happy to blog about that in the future with due credit.
Here is Part 2 of the blog post: SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice – Part 2.
In SQL Server 2008 we also have a new datatype of Date and Time, which can be very handy if you are using SQL Server 2008 or later versions. You can read about that in this blog post SQL SERVER – DATE and TIME in SQL Server 2008.
Reference: Pinal Dave (https://blog.sqlauthority.com)
405 Comments. Leave new
Thanks for the site, I get many benefits.
I create a view that pulls down safety data from corporate site based on local id. I then need to derive the number of day’s since last incident based on certain codes and do other calculations. This will end up on ASP web page. Should I create another view or table or maybe even do sql code in ASP? Here is my select:
SELECT *
FROM [SAFETY_WEB].[dbo].[vw_HSEL]
WHERE (inrCode = ‘HS3’) OR (inrCode = ‘HS4’) OR (inrCode = ‘HS5’) OR (inrCode = ‘HS6’) OR (inrCode = ‘HS7’) OR (inrCode = ‘HS9’)
ORDER BY EventDate DESC, inrCode
Not sure how to get number of day’s since last incident and put it in a field
SELECT CONVERT(VARCHAR(10),GETDATE(),111) – EventDate of Top 1
Because of the ORDER BY above, the Top EventDate is the latest incident.
There is a table “Leave”
in which three columns are there namely
1st – EmpNo
2nd — From Date
3rd — End Date
Employee No. 1 has taken leave from 5th march to 10 march
so record will be
1 5-MAR-2012 10-Mar-2012
i want result in vertical
like
EmpNo Date
1 5-MAR
1 6-MAR
1 7-MAR
1 8-MAR
1 9-MAR
1 10-MAR
Can I comapre date fileld (with Getdate)without using Convert or cast method which will work faster than Convert/Cast ?
very waste blog ;; noting gets in to learner memory
select convert(varchar(4),datediff(dd,getdate(),’01/01/2009′)) giving result as * instead of truncating the value
please can you clear me on this?
Use this
select cast(Getdate() as date)
selection criteria in both date format yyyy-MM-dd or yyyy-dd-MM in sql server
SELECT CONVERT(VARCHAR(10),GETDATE(),111)
I try this my some SQL Query it worked thank you very much.
If this is for formatting purpose you should use format function in front end application
why not u all simply use !!!!!!!!!!!!!!!! what the rocket science in this ..?????
select CONVERT(date, getdate())
Its for SQL 2008
hmm..v quick reply..thats good :) i forget to add it that its for 2008..actually saw the last comment which was of 28th may 2012 n using your 2007 technique :P
Yes, exactly, and for the before, I just deducted from official doc CONVERT(date,DATEADD(d,-1,GETDATE()))
https://docs.microsoft.com/fr-fr/sql/t-sql/functions/getdate-transact-sql?view=sql-server-2017
Thx.. I need to update the part of the date my date is 02/05/1987 i am having 40000 records.. kindly suggest some solution… my all dates are random and i want make them look like as 01/month/year… means just need to set the day as 01 (update table set day(date.)=01 gives syntax. error…
This is what you need
update table
set col=dateadd(day,datediff(day,0,datecol),0)
where
thank u….
thnx madhivanan but in my case this will nt wrk.. i am having some dates like
2012-01-10 00:00:00.000
2013-01-18 00:00:00.000
2014-01-22 00:00:00.000
2015-01-17 00:00:00.000
2016-01-18 00:00:00.000
2017-01-29 00:00:00.000
2018-01-01 00:00:00.000
2018-05-15 00:00:00.000
2020-01-20 00:00:00.000
2021-01-01 00:00:00.000
2022-05-12 00:00:00.000
2023-03-25 00:00:00.000
2024-06-06 00:00:00.000
if i run this query on these will nt b changed.. as i need to convert these dates as year+01+01 for alll
So you want to reset to Jan 1?
sir could you help me in converting time which is stored as BIGINT, to date format?
Yes it is possible. Can you give me sample data with expected result?
I have date 2012-07-09 08:07:02.360 in database i want only ‘2012-07-09’ this part will return in select statement. Can you help me ?
select convert(date,’2012-07-09 08:07:02.360′)
This result fulfill my requirement for difference between start time and end time
SELECT DATEDIFF(minute,(SELECT (CONVERT(VARCHAR(10),GETDATE(),111)+’ 08:00:00.000′)),(SELECT (CONVERT(VARCHAR(10),GETDATE(),111)+’ 15:30:00.000′)))
Diff between current date time stamp and today other time:
SELECT DATEDIFF(minute,current_timestamp,(SELECT (CONVERT(VARCHAR(10),GETDATE(),111)+’ 15:30:00.000′))) AS DiffDate
When i am not passing date value . The database is taking default value 1900-01-01 00:00:00.000 .How to restrict this. i wnt to pass NULL value when i am not assigning any date.
Any Suggestions ?
You should not pass empty string. Pass NULL value
Do not pass empty string. That gives you the problem. Pass only NULL value or omit from passing any values
Hello Sir
here my problem is i have to get values from a txt file using bulk insert
BULK INSERT tbl_Trial
FROM ‘C:Outputinout.txt’
with
(
FIELDTERMINATOR=’ ‘,
ROWTERMINATOR=’0x0A’
)
this is the sp am using for that.
1 2012-07-05 17:09:41 1 0 1 0
1 2012-07-05 17:09:49 1 0 1 0
1 2012-07-05 17:09:51 1 0 1 0
2 2012-07-05 17:11:23 1 0 1 0
2 2012-07-05 17:11:35 1 0 1 0
3 2012-07-05 17:15:41 1 0 1 0
3 2012-07-05 17:15:43 1 0 1 0
1 2012-07-05 17:19:51 1 0 1 0
1 2012-07-05 17:19:55 1 0 1 0
this is the txt format
Question is
How to insert the txt file values to sql server table with time and date separately using stored procedure.
Import data into staging table;split date and time seperately;insert them into target table
Hi sir,
I have used this query ORDER BY cast([Regdate] as datetime) DESC but error occur was The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range
please help me.
Hi sir,
when i use this query for sorting date wise order by CONVERT(datetime,[date],103) DESC
Error occur was:
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
Hi sir,
when i use this query for sorting date wise order by CONVERT(datetime,[date],103) DESC
Error occur was:
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
Please help me urgently
Thanks in adv….
What is the datatype of [date]?
That date datatype was nvarchar sir,
Thanks
What is the format? Depends on it, you need to use different number instead of 103