[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

In T-SQL, is there a quick way to truncate the time part for a datetime value?

Posted on 2011-10-24
8
Medium Priority
?
257 Views
Last Modified: 2012-05-12
In T-SQL, is there a quick way to truncate the time part for a datetime value?

E.g. convert 2011-1-1 12:23:34:567 to 2011-1-1 00:00:00:000

Thanks.
0
Comment
Question by:thomaszhwang
8 Comments
 
LVL 15

Accepted Solution

by:
tim_cs earned 668 total points
ID: 37020750
CAST(Yourdate AS DATE)
0
 

Author Comment

by:thomaszhwang
ID: 37020759
But this will make the value become 2011-1-1 instead of 2011-1-1 00:00:00:000, right?
0
 
LVL 26

Assisted Solution

by:tigin44
tigin44 earned 336 total points
ID: 37020776
select convert(datetime,convert(date, getdate()))
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 15

Assisted Solution

by:tim_cs
tim_cs earned 668 total points
ID: 37020779
Missed that part.  You could cast back to a datetime.

CAST(CAST(getdate() AS DATE) AS DATETIME)
0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 332 total points
ID: 37020802
For SQL 2005 I dont think you have date datat type so you can use:

SELECT CONVERT(DATETIME,(CAST(MONTH(getdate()) AS VARCHAR(2))+'/'+CAST(DAY(getdate()) AS VARCHAR(2))+'/'+CAST(YEAR(getdate()) AS VARCHAR(4))),101)
0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 332 total points
ID: 37021224
The DATE data type does not exist in SQL 2005 or earlier; it was added for 2008.

Another alternative that works in all versions:

SELECT MyDate, DATEADD(day, DATEDIFF(day, 0, MyDate), 0) AS DateWithMidnightOnly
FROM SomeTable

Open in new window

0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 332 total points
ID: 37025754
Or:

SELECT CAST(CAST( dateColumn AS int) AS datetime)
0
 

Author Closing Comment

by:thomaszhwang
ID: 37102358
Thanks guys.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question