what Workday number is today in the month today is may 9th but the workday number is 6

Question

Post reply

what Workday number is today in the month today is may 9th but the workday number is 6

kat35601

SSC Eights!

Points: 816
More actions
May 9, 2016 at 8:11 am

#326914

what Workday number is today in the month today is may 9th but the workday number is 6

I need away to get the workday number using getdate()

Viewing 7 posts - 1 through 6 (of 6 total)

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

kat35601 (5/9/2016)

what Workday number is today in the month today is may 9th but the workday number is 6

I need away to get the workday number using getdate()

what code are you using to get "workday" ?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

kat35601 SSC Eights! Points: 816 More actions · Answer 2

kat35601

SSC Eights!

Points: 816

May 9, 2016 at 8:30 am

#1877495

That is what I am looking for I need to be able to get what workday number the current day is saturday and sunday would be 0

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

one way is...Gooogle for others....this doesnt auto magically know about your own national holidays / work holidays ....yadda yadda



DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate = '20160501'

SET @EndDate = GETDATE()

SELECT

   (DATEDIFF(dd, @StartDate, @EndDate) + 1)

  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)

  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)

  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

kat35601 SSC Eights! Points: 816 More actions · Answer 4

Thank you I change it a little but get the same results



SELECT

   (DATEDIFF(dd,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0), getdate()) + 1)

  -(DATEDIFF(wk, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0), getdate()) * 2)

  -(CASE WHEN DATENAME(dw, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) = 'Sunday' THEN 1 ELSE 0 END)

  -(CASE WHEN DATENAME(dw, GETDATE()) = 'Saturday' THEN 1 ELSE 0 END)

does that look ok it seems to work fine.

Thanks again

Luis Cazares SSC Guru Points: 183686 More actions · Answer 5

You'll be missing any holidays. To prevent that, you'll need to create a calendar table or at least a holidays table.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 6

Luis Cazares (5/9/2016)

You'll be missing any holidays. To prevent that, you'll need to create a calendar table or at least a holidays table.

I normally use the Dim_Date table from the Data Warehouse for things like this. Check, you might already have a good starting point.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]