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

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

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

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

Change -1 to -2 for day before yesterday.

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

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: 247180 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.

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: 247180 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.

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