DateTime input with time always at 00:00:00.0000

Question

Post reply

DateTime input with time always at 00:00:00.0000

Digs

SSCertifiable

Points: 7333
More actions
March 18, 2010 at 9:58 pm

#146094

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???:-)

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

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

Digs SSCertifiable Points: 7333 More actions · Answer 2

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'

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 3

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

Nabha SSCrazy Eights Points: 8534 More actions · Answer 4

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))

---------------------------------------------------------------------------------

Digs SSCertifiable Points: 7333 More actions · Answer 5

Digs

SSCertifiable

Points: 7333

March 19, 2010 at 10:38 am

#1136888

Thanks

rich-521822 SSC Veteran Points: 236 More actions · Answer 6

SELECT CAST(FLOOR(CAST(GETDATE() as FLOAT)) AS DateTime)

Does this define something about me or is it just vanity?

Paul White SSC Guru Points: 150467 More actions · Answer 7

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.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Digs SSCertifiable Points: 7333 More actions · Answer 8

Whats with the ';' in the SQL statement

'2000-01-01');

???

I dont use ';' in SQL 2005, should I ??

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 9

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

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 10

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Stefan_G SSCertifiable Points: 6609 More actions · Answer 11

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

Stefan_G SSCertifiable Points: 6609 More actions · Answer 12

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 ?

john.campbell-1020429 Ten Centuries Points: 1194 More actions · Answer 13

select cast(cast(getdate() as varchar(12)) as datetime)

gives

2010-03-22 00:00:00.000

John.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 14

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)