DateTime input with time always at 00:00:00.0000

Question

DateTime input with time always at 00:00:00.0000

Viewing 9 posts - 16 through 24 (of 24 total)

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

Paul White SSC Guru Points: 150468 More actions · Answer 1

Stefan_G
What exactly do you mean with SARGable in this context?

No idea! Misremembering something, answers on a postcard as to what. :laugh:

Of course the only way such an expression is seekable is if the exact expression exists as a computed column that is indexed.

Same goes for the CHAR conversion (if done with a deterministic style) and FLOAT (2008 only).

My apologies for the misinformation. Really baffled why I wrote that.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Digs SSCertifiable Points: 7333 More actions · Answer 2

So is this the best method...after all the chatter..:cool:

DECLARE @iDate DATETIME,

@val INT

SET @iDate = '12/31/2010 01:25:35'

SELECT DATEADD( day, 0, DATEDIFF( day, 0, @iDate))

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

Heh... Paul won't like the zero notation but, yes, that general form is the most effective because it's the second fastest method and, unlike the fastest method, is upwardly mobile for future versions of SQL Server.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Stefan_G (3/22/2010)
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 ?

In this case, try this:

SELECT

MyDate

FROM

dbo.table

WHERE

MyDate >= dateadd(dd, datediff(dd, '2000-01-01', getdate()), '2000-01-01') and

MyDate < dateadd(dd, datediff(dd, '2000-01-01', getdate()) + 1, '2000-01-01')

Paul White SSC Guru Points: 150468 More actions · Answer 5

Some code to illustrate my point about indexed computed columns. Note that the computation is performed on the column reference.

-- Persisting whole_date2 only works in 2008

CREATE TABLE #Test

(

row_id INT IDENTITY PRIMARY KEY,

the_date DATETIME NOT NULL,

whole_date1 AS DATEADD(DAY, DATEDIFF(DAY, 0, the_date), 0),

whole_date2 AS CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, the_date))) PERSISTED,

whole_date3 AS CONVERT(DATETIME, CONVERT(CHAR(10), the_date, 120), 120) PERSISTED

);

-- Test data

INSERT #Test (the_date)

SELECT TOP (10000)

DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT 0)), '1900-01-01 08:49:51.368')

FROM master.sys.all_columns AC1,

master.sys.all_columns AC2,

master.sys.all_columns AC3;

-- Indexes to support seeks

-- Index uq2 only works on 2008

CREATE UNIQUE INDEX uq0 ON #Test (the_date);

CREATE UNIQUE INDEX uq1 ON #Test (whole_date1);

CREATE UNIQUE INDEX uq2 ON #Test (whole_date2);

CREATE UNIQUE INDEX uq3 ON #Test (whole_date3);

-- Find a date using a seek, despite the expression on the column

DECLARE @SearchFor DATETIME;

SET @SearchFor = '1905-01-01';

SELECT the_date

FROM #Test

WHERE @SearchFor = DATEADD(DAY, DATEDIFF(DAY, 0, the_date), 0);

SELECT the_date

FROM #Test

WHERE @SearchFor = CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, the_date)));

SELECT the_date

FROM #Test

WHERE @SearchFor = CONVERT(DATETIME, CONVERT(CHAR(10), the_date, 120), 120);

-- Tidy up

DROP TABLE #Test;

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 6

Not sure if you're using 2k8 or not but in 2k5, whole_date2 is not deterministic and cannot be persisted.

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

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

Paul White NZ (3/22/2010)
Same goes for the CHAR conversion (if done with a deterministic style) and FLOAT (2008 only).

^^^ Hey Jeff 😉 ^^^

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 8

Paul White NZ (4/3/2010)
Paul White NZ (3/22/2010)
Same goes for the CHAR conversion (if done with a deterministic style) and FLOAT (2008 only).
^^^ Hey Jeff 😉 ^^^

Hey Paul...

-- Persisting whole_date2 only works in 2008

Heh... you know I'm blind and one eye and can't see out the other before coffee. 😛

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

Paul White SSC Guru Points: 150468 More actions · Answer 9

Jeff Moden (4/3/2010)
Heh... you know I'm blind and one eye and can't see out the other before coffee. 😛

:laugh:

Can't believe I missed my own comment :blush:

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi