March 18, 2010 at 9:58 pm
I have a stored proc that has an input variable like:
@SubsEnd DATETIME='12/31/1900'
My standard format from my client is MM/dd/yyyy
So like 03/21/2010 is inputted from a field at the client.(vb.net/asp.net)
But when this date is inserted into the TBL Datetime field from the variable @SubsEnd I get
2010/03/21 01:25:01 or whatever
I just want to tweak the @SubsEnd so that the data in the TBL field has time at 00:00:00.000 every time.
How can I reset the time part to ZERO or MIDNIGHT? I dont want to do this at the client, prefer at the server.
Any ideas???:-)
March 18, 2010 at 10:15 pm
This is my attempt to assist you
DECLARE
@int date,
@idate datetime
SET @int = (SELECT CONVERT(date,getdate()))
SET @iDate = (SELECT CONVERT(datetime, @int))
SELECT @int AS Initial_Conversion, @idate AS Result
--or try CAST & convert
SELECT IntegerToDatetime = CAST(convert(varchar,@iDate) AS datetime)
GO
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 18, 2010 at 11:10 pm
You sent me on correct highway thanks...:-)
This is the code the will work for me..
DECLARE @iDate DATETIME,
@val INT
SET @iDate = '12/31/2010 01:25:35'
SET @val = (SELECT CAST(@iDate AS INT))
SET @iDate = (SELECT CONVERT(datetime, @val))
SELECT @iDate AS 'A', CONVERT(CHAR(10), @iDate, 101) AS 'B'
March 18, 2010 at 11:15 pm
glad to be of some help.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 19, 2010 at 7:40 am
Thats a nice one by Henrico too, here is a classic one..
DECLARE @iDate DATETIME,
@val INT
SET @iDate = '12/31/2010 01:25:35'
SELECT DATEADD( day, 0, DATEDIFF( day, 0, @iDate))
---------------------------------------------------------------------------------
March 19, 2010 at 10:38 am
Thanks
March 19, 2010 at 11:18 am
SELECT CAST(FLOOR(CAST(GETDATE() as FLOAT)) AS DateTime)
March 21, 2010 at 5:50 am
rich-521822 (3/19/2010)
SELECT CAST(FLOOR(CAST(GETDATE() as FLOAT)) AS DateTime)
Casting a date to a float relies on undocumented internal behaviour, and is therefore a bad idea. In 2008, none of the new date/time types can be manipulated in this way, so it is a good example of a bad habit that needs kicking
Converting to an INT is similar: 2008 and onward do not allow explicit casts to numeric types.
In addition, only one of the methods presented so far is SARGable - meaning that an index seek might be used. The other methods all require an index scan at least. The SARGable method is the one based on the built-in date functions DATEADD and DATEDIFF:
SELECT DATEADD(DAY, DATEDIFF(DAY, '2000-01-01', GETDATE()), '2000-01-01');
That is the code Nabha posted, but I prefer to use a real date rather than a numeric zero.
March 21, 2010 at 11:29 am
Whats with the ';' in the SQL statement
'2000-01-01');
???
I dont use ';' in SQL 2005, should I ??
March 21, 2010 at 3:38 pm
Digs (3/21/2010)
Whats with the ';' in the SQL statement'2000-01-01');
???
I dont use ';' in SQL 2005, should I ??
You should start getting in the habit of terminating your SQL statements with a semi-colon. In 2005 there are a couple of statements that require it (e.g. the statement preceding a common table expression must be terminated).
In 2008 - there are more areas that require the statement to be terminated.
The semi-colon is a statement terminator.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 21, 2010 at 10:20 pm
Agreed on the FLOAT conversion. Bad thing to do for the future.
Same goes with the CONVERT not because it's a bad thing for the future but because it's comparatively slow in the face of scalability.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 22, 2010 at 6:26 am
Digs (3/18/2010)
You sent me on correct highway thanks...:-)This is the code the will work for me..
DECLARE @iDate DATETIME,
@val INT
SET @iDate = '12/31/2010 01:25:35'
SET @val = (SELECT CAST(@iDate AS INT))
SET @iDate = (SELECT CONVERT(datetime, @val))
SELECT @iDate AS 'A', CONVERT(CHAR(10), @iDate, 101) AS 'B'
Casting to an int like this is a really bad idea. The cast from datetime to int uses rounding so if the datetime is in the afternoon it will get rounded to the following day.
Try using SET @iDate = '12/31/2010 14:25:35' in the above script and see what happens...
You should really use the code posted by Nabha.
/SG
March 22, 2010 at 6:32 am
Paul White NZ (3/21/2010)
In addition, only one of the methods presented so far is SARGable - meaning that an index seek might be used. The other methods all require an index scan at least. The SARGable method is the one based on the built-in date functions DATEADD and DATEDIFF:
SELECT DATEADD(DAY, DATEDIFF(DAY, '2000-01-01', GETDATE()), '2000-01-01');
What exactly do you mean with SARGable in this context ?
I guess that you mean that if you have an indexed datetime column in a table you could find all entries in a particular day using a query like this:
SELECT MyDate
FROM table
WHERE DATEADD(DAY, DATEDIFF(DAY, '2000-01-01', MyDate), '2000-01-01') = '20100301'
This does not seem to work for me - I always get an index scan. (SQL Server 2008)
Am I missing something ?
March 22, 2010 at 6:47 am
select cast(cast(getdate() as varchar(12)) as datetime)
gives
2010-03-22 00:00:00.000
John.
March 22, 2010 at 7:23 am
john.campbell-1020429 (3/22/2010)
select cast(cast(getdate() as varchar(12)) as datetime)
gives
2010-03-22 00:00:00.000
John.
Like I said in my previous post above... that's a bad idea for performance.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy