Assigning current date without time value to column

Question

Post reply

Assigning current date without time value to column

Viewing 6 posts - 16 through 20 (of 20 total)

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

10e5x Default port Points: 1445 More actions · Answer 1

10e5x

Default port

Points: 1445

January 4, 2013 at 9:03 pm

#1574015

Oh i get it already. So which one is recommended? Better performance maybe? THanks to the two pros!

10e5x Default port Points: 1445 More actions · Answer 2

[/b]

So if i have an sql statement in my execute sql task that looks something like this:

Delete from TABLE where (CREATION_D = ?)

CREATION_D is the column i am discussing about. So if i use it as datetime it will give me values such as 1/4/2013 12:00:00 AM while ? is a parameter from my package variable (ExecDate) of a datatype varchar that will look like 1/4/2013 11:38:00 AM. I want that delete statement to be triggered when both are of same date. How shall i edit to achieve this? Thanks for all the inputs, i decided to heed all advice and remain my CREATION_D as datetime datatype

Hi pros,

i would like to ask if i decide not to use the varchar variable... but given the same scenario how can i perform DELETE FROM TABELE WHERE CREATION_D = todayDateWithoutTime?

Which means i compare if the two dates are the same, regardless of the time, i shall delete..

Am i doing it right?

DELETE FROM RPT_TAPS_DTL



WHERE     (DATEADD(DAY, DATEDIFF(DAY, CAST('19000101' AS DATETIME), CREATION_D), CAST('19000101' AS DATETIME)) = DATEADD(DAY, DATEDIFF(DAY,

CAST('19000101' AS DATETIME), GETDATE()), CAST('19000101' AS DATETIME)))

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 3

10e5x (1/9/2013)

[/b]

So if i have an sql statement in my execute sql task that looks something like this:

Delete from TABLE where (CREATION_D = ?)

CREATION_D is the column i am discussing about. So if i use it as datetime it will give me values such as 1/4/2013 12:00:00 AM while ? is a parameter from my package variable (ExecDate) of a datatype varchar that will look like 1/4/2013 11:38:00 AM. I want that delete statement to be triggered when both are of same date. How shall i edit to achieve this? Thanks for all the inputs, i decided to heed all advice and remain my CREATION_D as datetime datatype

Hi pros,

i would like to ask if i decide not to use the varchar variable... but given the same scenario how can i perform DELETE FROM TABELE WHERE CREATION_D = todayDateWithoutTime?

Which means i compare if the two dates are the same, regardless of the time, i shall delete..

Am i doing it right?

DELETE FROM RPT_TAPS_DTL
WHERE (DATEADD(DAY, DATEDIFF(DAY, CAST('19000101' AS DATETIME), CREATION_D), CAST('19000101' AS DATETIME)) = DATEADD(DAY, DATEDIFF(DAY,
CAST('19000101' AS DATETIME), GETDATE()), CAST('19000101' AS DATETIME)))

It will work but this is how I would do it:



delete from RPT_TAPS_DTL

where

    CREATE_D >= dateadd(dd,datediff(dd,cast('19000101' as datetime),getdate()),cast('19000101' as datetime)) and

    CREATE_D < dateadd(dd,datediff(dd,cast('19000101' as datetime),getdate()) + 1,cast('19000101' as datetime));

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Cadavre (1/4/2013)

10e5x (1/4/2013)

omg i am losing the discussion over here... so the recommended solution to me is which? And is it a typo or? why there are two '??' sry for the noobness and confusion

Either would work, I think Lynn was just showing you a different way to achieve the result. The two "?" is for your parameter.

DELETE FROM TABLE WHERE (CREATION_D = DATEADD(DAY, 0, DATEDIFF(DAY, 0, @yourDateParamater )));
DELETE FROM TABLE WHERE (CREATION_D = DATEADD(DAY, DATEDIFF(DAY, 0, @yourDateParamater ), 0));

Another way would be to do this: -

DELETE FROM TABLE WHERE (CREATION_D = CAST(DATEDIFF(DAY, 0, @yourDateParamater ) AS DATETIME));

I think they're all roughly equivalent in terms of performance.

Careful now. I realize that Lynn has already pointed it out but I want to emphasize it... The example that Lynn gave is the correct one (the second one above). "0" is a date here and the correct syntax of DATEADD is DATEADD(<datepart>, <numberofparts>, <date/time>). It can really make a difference depending on what you're doing.

--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)
Intro to Tally Tables and Functions

10e5x Default port Points: 1445 More actions · Answer 5

Careful now. I realize that Lynn has already pointed it out but I want to emphasize it... The example that Lynn gave is the correct one (the second one above). "0" is a date here and the correct syntax of DATEADD is DATEADD(<datepart>, <numberofparts>, <date/time>). It can really make a difference depending on what you're doing.

Thanks Jeff, noted, I got it.

Cadavre SSC-Forever Points: 41690 More actions · Answer 6

Jeff Moden (1/9/2013)

Careful now. I realize that Lynn has already pointed it out but I want to emphasize it... The example that Lynn gave is the correct one (the second one above). "0" is a date here and the correct syntax of DATEADD is DATEADD(<datepart>, <numberofparts>, <date/time>). It can really make a difference depending on what you're doing.

I may be being dense, but can you give me an example?

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/