Solved

Changing the time value of a sql datetime

Posted on 2007-11-16
5
4,069 Views
Last Modified: 2008-02-01
Hi How can I set just the time value of a datetime:

for example

DECLARE @ENDDATE SMALLDATETIME
SET @ENDDATE = GETDATE()

If i wanted to then amend the time value of @ENDDATE, how would I do this?
0
Comment
Question by:scm0sml
  • 3
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20297597
select CONVERT(varchar,getdate(), 108)
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20297602
make sure that you store that value in a Varchar column
declare @Time = varchar(10)
select @Time = CONVERT(varchar,getdate(), 108)
0
 

Author Comment

by:scm0sml
ID: 20297618
say i have

SET @TESTDATE = GETDATE()

That will give me the date and time for now.

But say I wanted to set the time value of @TESTTIME to 09:30 for example.

How can i do this?
0
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 100 total points
ID: 20297621

DECLARE @ENDDATE SMALLDATETIME
SET @ENDDATE = GETDATE()
 
set @change smalldatetime set @change = '12:30:12'
set @endDate = convert(datetime,
 convert(varchar, @endDate, 102) + ' ' + convert(varchar, @change, 8))

Open in new window

0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 400 total points
ID: 20297633
scm0sml,



Aneesh R

select CAST(CONVERT(varchar,getdate(), 112)   +' ' +'09:30'AS DATETIME)

Open in new window

0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

816 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now