Jump to content
Sign in to follow this  
jafm

Todos os Dias de um mês

Recommended Posts

jafm

Boas,

preciso de algo que me devolva todos os dias do mês

1,2,3,...29,30,31

Eu tenho que passar isto como parâmetros.

SELECT DATEADD(s,1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) as DeDia1
,(SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),1))) as ADia1
.
.
.
,(SELECT DATEADD(hh,-24,DATEADD(mm, DATEDIFF(m,1,GETDATE())+1,-1))) as ADia30
,(SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,-1))) as ADia30
,(SELECT DATEADD(hh,-24,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))) as ADiaFim
,(SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))) as ADiaFim

inicialmente a minha estratégia foi a de cima, mas depois vi que não dava pois dependendo dos meses se 30 ou 31 eu não conseguiria assegurar os restantes dias.

Será que existe alguma maneira de eu dar a volta?

Share this post


Link to post
Share on other sites
jafm

Boa ajuda. Obrigado.

Entretanto estou com aqui com um problema, que é para o ano actual funciona direitinho,

WITH sample AS (
SELECT '1' as AnoAtual,DATEADD(s,1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) AS dt
union select '1' as AnoAtual, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),1)) as DT2
UNION ALL
SELECT '1' as AnoAtual, DATEADD(dd, 1, dt)
FROM sample s
WHERE DATEADD(dd, 1, dt) <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))
SELECT *
FROM sample

para o ano anterior não estou a conseguir colocar por exemplo 2012-08-01 23:59:59.000, só me devolve o 2012-08-31 23:59:59.000

o que posso estar a fazer de errado? Deixo aqui o codigo que não estou a conseguir resolver

WITH sample AS (
SELECT '1' as AnoAnterior,DATEADD(s,1,DATEADD(mm, DATEDIFF(m,0,DATEADD(YEAR,-1,GETDATE())),0)) AS dt
union select '1' as AnoAnterior, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(YEAR,-1,GETDATE()))+1,0)) as DT2
UNION ALL
SELECT '1' as AnoAnterior, DATEADD(dd, 1, dt)
FROM sample s
WHERE DATEADD(dd, 1, dt) <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(YEAR,-1,GETDATE()))+1,0)))
SELECT *
FROM sample

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.