CODEWITHSUNDEEP
sql-serverdatetimegetdate
Jin Yong
Jin Yong

Reputation: 43778

How to default the time for the date with SQL Server

Does anyone know how can I default the time for the date with SQL Server?

Example:

When I use getdate() and it will return me the current date and time. How can I get the current date but default the time portion to '10:00:00.000' ?

Upvotes: 2

Views: 3354

Answers (6)

mvp
mvp

Reputation: 116377

This works for SQL Server (SQLFiddle):

SELECT DATEADD(hour, 10, CAST(CAST(GETDATE() AS DATE) AS DATETIME))

Explanation: GETDATE() gives current date and time. Casting to DATE makes it date only (midnight time). Casting it again to DATETIME makes it compatible with DATEADD, which finally adds 10 hours to it.

Upvotes: 2

Charles Bretana
Charles Bretana

Reputation: 146557

Again, in Ms SQL Server, You can also use

Select DateAdd(day, datediff(day, getdate()), 0) + 10/24.0

Upvotes: 0

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11609

select convert(datetime,convert(varchar,convert(date,getdate())) + ' 10:00:00.000')

SQL FIDDLE

Upvotes: 0

AnandPhadke
AnandPhadke

Reputation: 13506

in sql server 2008 and above:

select convert(datetime,convert(varchar(10),convert(date,(GETDATE())))+' 00:00:00')

Upvotes: 0

Santhosh
Santhosh

Reputation: 1791

This is from DB2. But same concept should work very DB. Just convert that date in to Timestamp

SELECT TIMESTAMP(CURRENT_DATE, TIME('10:00:00')) AS MY_DATE FROM SYSIBM.SYSDUMMY1

Upvotes: 0

sohail.hussain.dyn
sohail.hussain.dyn

Reputation: 1541

Use the below if you are using sql server.

select cast(cast(getdate()AS INT)+0.41667 as datetime)

Note time is scaled on a 0.0 to 0.9999, and the no. of hours are equally distributed. e.g. 0.5 will give 12a.m.

Upvotes: 1

Related Questions