Solved

Changing the time value of a sql datetime

Posted on 2007-11-16
5
4,062 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
Comment Utility
select CONVERT(varchar,getdate(), 108)
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
scm0sml,



Aneesh R

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

Open in new window

0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

743 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

18 Experts available now in Live!

Get 1:1 Help Now