Derived Date part function

Hi there,

 I have a date column which is [REGIST_DATE].

Currently the output that shown in the SQL Server is as below:

[REGIST_DATE]
-------------
2012-01-07 12:11:25. 1460000

My question  is how do I get rid of the MM:SS from the above output by writing it in the Derive Column expression?
The result I am expecting will be

2012-01-07 12:00:00. 0000000

1. I have try datepart but yet i can't get the result.

Would appreciate your assistant in this.

Hi josephtys86.

Why cant you do it with datepart? you just have to make sure that you get all the expression, even if you must add 2 digits to make sure you get 2 numbers for hour, 2 numbers for minutes ans 2 numbers for seconds.

For example: RIGHT("00"+datepart(HH,date_column),2) +":00:00"

This will make sure you get 2 digits for hour and 0 for minutes and seconds (in this example you gave, you already have 2 digits for hour)

Take a look at tim's blog. Not exacly to solve your problem but I'm sure you will get there.

You can always do something like:

YEAR(date_column) + "-" + MONTH(date_column) + "-" + DAY(date_column) + " " + RIGHT("00"+datepart(HH,date_column),2) +":00:00"

Let me know if you need further assistance.

I think this will get you what you want.

DATEADD(hh,DATEDIFF(hh,0,[REGIST_DATE]),0)

This formula is fast and very useful for stripping off the value of any part of a DATETIME that you don't want.  Just change the datepart to the part you want to be the lowest level of granularity.  In this case you want hours, but not minutes etc. so the datepart is hours.  If you wanted to go to a smaller granularity than minutes you will probably have to change the zeros to a more recent date such as '2000-01-01 00:00:00'