How to assign current date with specific time to c

问题:

How do i assign current date with a specific time?

let's say 8:00:00 AM to Column EXIT_DT of datatype datetime??

I have tried GETDATE() AS EXIT_DT but it gives me current datetime. I am using Sql server 2005. Any help?

Lets say Today is 1/3/2013 and i want my result to return as a datetime datatype with value 1/3/2013 8:00:00 AM. If i run the statement ytd, the result will be 1/2/2013 8:00:00 AM

回答1:

This formula will always produce 08:00 for the day it is called, and avoids string manipulation:

select DATEADD(day,DATEDIFF(day,'20010101',GETDATE()),'2001-01-01T08:00:00')

Try to avoid solutions that convert to and from strings - treating datetime values as strings is one of the largest sources of bugs.

It works by computing the number of days (as an integer) that have elapsed since 1st January 2001. It then adds that same number of days to 08:00 on 1st January 2001.

回答2:

You can try this :

DECLARE  @dt datetime;

SET @dt=CONVERT(DateTime, CONVERT(VARCHAR,GETDATE(),101)+' 8:00:00')

SELECT CONVERT(VARCHAR, @dt, 101)+'  '+ LTRIM(RIGHT(CONVERT(VARCHAR(20),@dt, 100), 7))

Visit http://www.sql-server-helper.com/tips/date-formats.aspx for datetime formats.

回答3:

Use Convert along with getdate() to get specific formats.

ex:

SELECT CONVERT(VARCHAR(30),GETDATE(),113) 

回答4:

This is a bit stupid, but it works

select cast(cast(getdate() as date) as datetime) + '08:00:00'

it casts the getdate() to date thus losing the hours, than it casts it to datetime and adds 8 hours.

If you want to avoid implicit conversion of varchar to datetime, you could use this version:

select cast(cast(getdate() as date) as datetime) 
     + convert(datetime,'08:00:00',114)

回答5:

This is also working. (1). convert today's date to ISO format (yyyymmdd) (2). add the time, (3). convert back to datetime

Select convert(datetime, convert(varchar, getdate(),112) + ' ' + '8:00:00AM')
--Results
2013-01-03 08:00:00.000

If you need in specific format you need to convert back to varchar again.

回答6:

-- AM/PM --
SELECT TO_CHAR(sysdate, 'MM/DD/YYYY HH:MI:SS AM') FROM dual
/

-- 24 hrs format --
SELECT TO_CHAR(sysdate, 'MM/DD/YYYY HH24:MI:SS') FROM dual
/