Expression needed for Midnight of previous day

Question

Post reply

Expression needed for Midnight of previous day

polkadot

SSChampion

Points: 13664
More actions
June 19, 2017 at 11:59 am

#343289

How to get expression for yesterday and day before so that the time stamp resets *exactly to midnight*?
I have
Yesterday gives 6/18/2017 10:04:10 AM *need 6/18/2017 12:00:00 AM*
@[User::ToDate] = (DT_DATE) DATEADD("DAY",-1,GETDATE())
Day Before Yesterday gives 6/17/2017 10:04:10 AM *need 6/17/2017 12:00:00 AM*
@[User::FromDate] = (DT_DATE) DATEADD("DAY",-2,GETDATE())
--Quote me

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

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

Phil Parkin SSC Guru Points: 246987 More actions · Answer 1

SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) - 1, 0);

Change -1 to -2 for day before yesterday.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

polkadot SSChampion Points: 13664 More actions · Answer 2

Phil, ....are you providing SQL as template for what to do in SSIS?

= (DT_DATE) DATEADD("DAY", -1,getdate())
way i see it, if i just knew how to get midnight version of getdate() and put that in place of the getdate() in above expression, i would have what I needed

--Quote me

Phil Parkin SSC Guru Points: 246987 More actions · Answer 3

polkadot - Monday, June 19, 2017 12:27 PM
Phil, ....are you providing SQL as template for what to do in SSIS?
= (DT_DATE) DATEADD("DAY", -1,getdate())
way i see it, if i just knew how to get midnight version of getdate() and put that in place of the getdate() in above expression, i would have what I needed

Sorry, I should have paid more attention to the forum you posted this in.
Try this instead:
DATEADD( "d", DATEDIFF( "d", (DT_DATE) 0, getdate())-1, (DT_DATE) 0)

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

polkadot SSChampion Points: 13664 More actions · Answer 4

Sorry Phil, i actually need expression to return this format,'2017-06-01 00:00:00.000' .

I do realize I thought I needed 6/01/2017 12:00:00 AM format, but it's causing ssis pkg to fail when passing within parameter to query.

Can you help me out with the typecast to use in expression?

--Quote me

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

Phil Parkin - Monday, June 19, 2017 12:16 PM
SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) - 1, 0);
Change -1 to -2 for day before yesterday.

Heh - And here I thought you did everything in SSIS. 😉

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

polkadot SSChampion Points: 13664 More actions · Answer 6

Turns out, that since I was assigning DATE datatype to @FromDate parameter, it was not passing the expression to Exec SQL Query as '2017-06-01 00:00:00.000'. After I changed datatype of parameter to FLOAT it did.

--Quote me

polkadot SSChampion Points: 13664 More actions · Answer 7

Ed, confused about your statement 'Heh - And here I thought you did everything in SSIS. Wink

'

--Quote me

Ed Wagner SSC Guru Points: 287026 More actions · Answer 8

polkadot - Thursday, June 22, 2017 3:32 PM
Ed, confused about your statement 'Heh - And here I thought you did everything in SSIS. '

That was a reference to Phil posting a T-SQL solution, given how very, very fluent he is in SSIS.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Phil Parkin SSC Guru Points: 246987 More actions · Answer 9

Ed Wagner - Thursday, June 22, 2017 3:52 PM
That was a reference to Phil posting a T-SQL solution, given how very, very fluent he is in SSIS.

Thank you for the vote of confidence, sir.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

polkadot SSChampion Points: 13664 More actions · Answer 10

while you two were having a little secret code language conversation I was pulling my hair out with parameter passing to exec sql task. Man alive.

--Quote me

Phil Parkin SSC Guru Points: 246987 More actions · Answer 11

polkadot - Thursday, June 22, 2017 5:50 PM
while you two were having a little secret code language conversation I was pulling my hair out with parameter passing to exec sql task. Man alive.

No need to be unpleasant.
If you reread your own post

Turns out, that since I was assigning DATE datatype to @FromDate parameter, it was not passing the expression to Exec SQL Query as '2017-06-01 00:00:00.000'. After I changed datatype of parameter to FLOAT it did.

You will see that sounds like you have resolved your problem. If you have not, try explaining why.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

polkadot SSChampion Points: 13664 More actions · Answer 12

sorry you took it so badly. But seriously, Ed, if I didn't get Phil's answer the first time, why would your reposting it help?

--Quote me

Ed Wagner SSC Guru Points: 287026 More actions · Answer 13

polkadot - Thursday, June 22, 2017 6:26 PM
sorry you took it so badly. But seriously, Ed, if I didn't get Phil's answer the first time, why would your reposting it help?

I was just posting a comment to Phil and didn't mean to distract you from what you were trying to accomplish. I see you marked Phil's post as the answer, so I'm glad you got it working.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

polkadot SSChampion Points: 13664 More actions · Answer 14

isn't that what private messages are for?

I had additional questions which I posted after marking Phil's great answer (by the way) as the answer.

When i saw there was a posted reply to my question i rushed to sqlservercentral to check it out, as i was struggling with my SSIS problem, only to find out you had reposted something Phil had already written.

It *is* distracting and disheartening, because I was really struggling with something. Don't do it anymore please. I marked that post as spam, which if you are honest with yourself, it is.

--Quote me