| Author |
Topic |
|
mark1504
Posting Yak Master
103 Posts |
 Posted - 2004-03-05 : 09:42:59
|
| I recently started using an answer I found in the archives, which I understood was a widely accepted solution. Then I realized a problemSELECT convert(datetime,convert(int, GETDATE()))produces 2004-03-05 00:00:00.000 before middayand2004-03-06 00:00:00.000 after midday(Today is the 5th!)How should I ensure that I return the 5th all day long? |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-03-05 : 09:45:05
|
| [code]select cast(cast(getdate() as varchar(12)) as datetime)[/code] |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-03-05 : 09:45:35
|
| convert(datetime,convert(varchar(8),getdate(),112))==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-05 : 09:50:47
|
| I'd like to think that thisSELECT DATEADD(d,DATEDIFF(d,0,getdate()),0) ------------------------------------------------------ 2004-03-05 00:00:00.000(1 row(s) affected)performs better, or?--Frank |
|
|
|
mark1504
Posting Yak Master
103 Posts |
Posted - 2004-03-05 : 09:55:34
|
| Amazing! Three different answers in less than ten minutes. My thanks to all. I'm off to try them and will report back soon.(Why on earth GETDATE() can't simply return a date without time is a mystery to me!) |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-05 : 09:59:26
|
Wait for Yukon  --Frank |
|
|
|
mark1504
Posting Yak Master
103 Posts |
Posted - 2004-03-05 : 10:02:05
|
| What a dilemma! They all work fine. I may go for Frank's suggestion just because there's less characters to type. Unless anyone else has a suggestion.Thanks guys. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-03-05 : 10:05:46
|
Whatever, but that's not what you asked for...you still get time component....Nigel's gives you what you want...And technically it did give you what you asked for, but not what you wanted...Brett8-) |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-05 : 10:12:53
|
| ??? Just kidding, but wasn't the topic called 'set time to 00:00:00 with GETDATE()'--Frank |
|
|
|
mark1504
Posting Yak Master
103 Posts |
Posted - 2004-03-05 : 10:13:01
|
| You've confused me Brett. All three methods produced identical results. |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-05 : 10:19:37
|
| Yes, datetimes always have this time component.--Frank |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-03-05 : 10:39:06
|
| Whoa!My Bad!I thought you where looking to lose the time component...Brett8-) |
|
|
|
ChrisFretwell
Starting Member
43 Posts |
Posted - 2004-03-05 : 10:48:54
|
| While time may be eternal, I seem always seem to lose some.Chris |
|
|
|
mark1504
Posting Yak Master
103 Posts |
Posted - 2004-03-05 : 10:54:42
|
| Actually for storing an 'untimed' date I've found this one which needs even less typingConvert(char(10),GETDATE(),101)which yields03/05/2004SQL Server seems to handle it OK for inserts and such, but I feel funny about it ;) |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-03-05 : 11:33:32
|
| Convert(char(8),GETDATE(),112)Then you won't get into trouble with regional settings - it will always convert implicitely.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
mark1504
Posting Yak Master
103 Posts |
Posted - 2004-03-05 : 11:55:46
|
| Thanks nr!!That's the shortest string yet (by 1 char), and it seems to work fine for the insert statements. |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-05 : 16:18:17
|
| Some days ago I saw somewhere else someone posting this solutionSELECT CAST(SUBSTRING(CAST(GETDATE() AS BINARY(8)),1,4) + 0x00000000 AS DATETIME) ------------------------------------------------------ 2004-03-05 00:00:00.000(1 row(s) affected)What do you think?--Frank |
|
|
|
mark1504
Posting Yak Master
103 Posts |
Posted - 2004-03-05 : 16:24:10
|
| LOL! That's longer than all our solutions put together!!! |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-05 : 16:36:25
|
What are you doing at this time online. You're pretty much in the same time zone than me  Still at work?Note, I didn't say it is a good solution in terms of less writing. Here is another one:SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) ------------------------------------------------------ 2004-03-05 00:00:00.000(1 row(s) affected)Not sure what you want this for, but if you need this frequently I would build up some test scenario with say 100k+ rows and run all statement with SET STATISTICS TIME ON and compare that results. And don't forget to DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHEbetween the single statements to make the result comparable. Pretty likely you'll find the solution I've posted first to be the fastest. And don't do this on a production box --Frank |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-05 : 16:39:27
|
| Hmpfh...hit some button too early. After all it's all a matter of preference. I used the CONVERT solution before, but tend to think DATEADD is a bit more flexible. And maybe easier to follow.--Frank |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-03-05 : 19:12:18
|
| Hint for Frank: On your own post, you should see in the header line a piece of paper with a pencil on it. That's your "Edit my post" button and allows you to change/finish your post without having to create a new one.--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
|
|
|
Next Page
|
set time to 00:00:00 with GETDATE()