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
Thnx.
It helped me lots.
Good job! MVP….
proud of you..
dear friend,
i need to display column names only from table
give me you thoughts
@Senthil
Select Column_Name From Information_Schema.Columns
Where Table_Name = ‘mytable’
Replace mytable with he name of actual table in above script.
This will give you list of column in vertical structure.
If you just want to see column names from a table without actually seeing data in the table in horital structure, then use below script
Select * from mytable
Where 1=2
Replace mytable with he name of actual table in above script.
Explanation: Because 1 is never equal to 2, you will always get the column names and will not see data returned by the query.
~ Peace.
Try sp_columns tablename
Type your name in query window select the text and press ALT+F1
EXEC SP_COLUMNS Table_name
how to get the server’s current date, which is connected through linked server in sql server 2005
SELECT GETDATE()
tks!
cheers mate!
Thanx dude!!! Good work:)
What a wonderful explanation…..
You must be a top noch programmer or a system solution developer……
Lots of love and thanks
How should I change the query to update the “LastModifiedDate” column in my table to a specific value?
update your_table
set LastModifiedDate=’date_value’
HI.
I want to display the date in U.S. format.
Instead of Getdate() iam specifying directly the date.
I have tried using these syntaxes.. It is not changing to that format.
Kindly help
Select convert(varchar,’10/09/2007′,1) ,
Select convert(varchar,’10/09/2007′,101),
Select convert(varchar,’10/09/2007′,110)
SELECT CONVERT(VARCHAR(10),GETDATE(),104)
SELECT CONVERT(VARCHAR(10),GETDATE(),105)
SELECT CONVERT(VARCHAR(10),GETDATE(),106)
SELECT CONVERT(VARCHAR(10),GETDATE(),107)
DocDate & DocDueDate is getting from temp table
SELECT GRVDATE= CONVERT (VARCHAR(15), DocDate,106) from #Head
SELECT REVDATE= CONVERT (VARCHAR(15), DocDueDate,106) from #Head
I need to store in a table having the DocDate & DocDueDate datatype as datetime
while doing this I got the error “Input string was not in a correct format.”
plz help
Display is the job of front end application. If you dont use any, as said, use CONVERT function
SELECT [NAME], CONVERT(VARCHAR(10),[OUTDATE],101) AS DATECONVERT, [OUTUSER] FROM
CASEIO ORDER BY
[OUTDATE] DESC;
does the job for me, you may want to refer to this MSDN article :
https://www.microsoft.com/en-us/download/details.aspx?id=51958
thanx ,
it helped me alot
thankyou for this function
The convert and string method has more overhead than you think if you need to use it in large rowsets.
Also, you still need another CAST to turn it back into datetime.
An alternate method one is to cast to float, FLOOR, and cast back to datetime.
Finally: the function usually is non deterministic if you use string to datetime (or vice versa) because the date format is culture and locale sensitive. CAST and CONVERT also mess up determistism.
Using the DATEADD(..DATEDIFF..) allows determinism, keeps all calculations within the datetime datatype, and guarantees complete locale insensitivity.
Determinism is important for indexing computed columns, or if the function is used in WHERE clause
Nice Solution
It is Vey helpful for My duplicate Transcation entry where date is set as Index.
Thanks
Hello,
I am extracting year from date in oracle i. e. to_char(sysdate,’yyyy’) Now i want to change my database from oracle to SQL Server 2005. So the point is how to convert
to_char(sysdate,’yyyy’) from oracle to SQL Server 2005.
Plz help me out.
Thanking you,
Tushar
select datepart(year,getdate())——
you’ll be getting only year part as ‘2010’
or you can use
select year(getdate())
Hai Tushar,
You try this select datepart(yyyy,getdate())
rgds
or
select year(getdate())
Thnax Buddy Nice Examples its working a lot….
i personally feel u should add some more exapmles
for sql queries which screw the programming day to day
any ways thanx
Sarthak…
If you need more example, keep following the blog serious about datetime column
Gr8 work dude.. ppl face number of problems working with datetime…
Regards
Bharath T
10x…..
I want to convert varchar type date into datetime so i am getting the following error.
Solve this Problem….
“Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.”
It means that some of the values can’t be converted to datetime values. Can you post some of the sample data? Also what is the format that date values are stored?
Dear Sir/Madam,
Can we use CONVERT while index a date field?
Usage of Convert function will not make use of index
If you have index and want to remove time part, use method 1
But what if I need the date to be 2007-12-14, with hyphens! SQL Server only offers me 2007-12-14 00:00:00, I don’t need the time at all.
As I said in my previous comment, formation is the job of front end application. If you still need it, use convert function with style 120. Also note that hyphenated values is ambigous
Refer this post for more informations about datetime formats
It helped me a lot.
Thanks
nice soln