Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aspirasam
Starting Member
1 Post |
 Posted - 2010-11-26 : 03:59:45
|
| Hi,I have a variable that I set to GETDATE().This happens at about 2.35am but is random down to the millisecond ie. never exactly 2.00am or 2.35am.What I need to be able to do is get todays date and set the time part of it to be 2.30am.Can anyone tell me how to do this please? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-11-26 : 04:11:04
|
declare @datevar datetimeset @datevar = dateadd(minute,datediff(minute,0,getdate()),0)select @datevar
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-11-27 : 02:43:21
|
| you mean always hardcode timepart as 2:30?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
MohanKandasamy
Starting Member
9 Posts |
Posted - 2010-11-27 : 07:49:02
|
| Hi,if you want to hardcode the time then you can use the below querySELECT CONVERT(VARCHAR(10), GETDATE(),121) +SPACE(1) + '02:35'Mohan Kandasamy |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-27 : 08:47:55
|
| "SELECT CONVERT(VARCHAR(10), GETDATE(),121) +SPACE(1) + '02:35'"best to avoid converting DATETIME datatype to STRING if you then convert it back to DATETIME again - much slower than using just arithmetic operations |
|
|
|
MohanKandasamy
Starting Member
9 Posts |
Posted - 2010-11-27 : 09:03:23
|
| Yes, i agreed , But cant avoid sometime if iam not wrong :)Mohan Kandasamy |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-27 : 10:06:48
|
| For example:DATEADD(Minute, DATEDIFF(Minute, 0, @MyDate), 0)is more efficient to "round" a date to the minute, because it only uses Integer Arithmetic, rather than convert to String and back to Datetime |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-11-29 : 05:28:38
|
quote:
Originally posted by MohanKandasamy
Hi,if you want to hardcode the time then you can use the below querySELECT CONVERT(VARCHAR(10), GETDATE(),121) +SPACE(1) + '02:35'Mohan Kandasamy
Work on DATEs not on CHARs  MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Set datetime