[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
Medium Priority
257 Views
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
Question by:thomaszhwang

LVL 15

Accepted Solution

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

Author Comment

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

tigin44 earned 336 total points
ID: 37020776
select convert(datetime,convert(date, getdate()))
0

LVL 15

Assisted Solution

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

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

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
``````
0

LVL 70

Assisted Solution

Scott Pletcher earned 332 total points
ID: 37025754
Or:

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

Author Closing Comment

ID: 37102358
Thanks guys.
0

## Featured Post

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…
###### Suggested Courses
Course of the Month20 days, 10 hours left to enroll