Date Tricks

22/04/2014

תגיות:
תגובה אחת
First Day Of Current Week. select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),0),106)
Last Day Of Current Week. select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),6),106)
First Day Of Last week. select CONVERT(varchar,DATEADD(week,datediff(week,7,getdate()),0),106)
Last Day Of Last Week. select CONVERT(varchar,dateadd(week,datediff(week,7,getdate()),6),106)
First Day Of Next Week. select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),7),106)
Last Day Of Next Week. select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),13),106)
First Day Of Current Month. select CONVERT(varchar,dateadd(d,-(day(getdate()-1)),getdate()),106)
Last Day Of Current Month. select CONVERT(varchar,dateadd(d,-(day(dateadd(m,1,getdate()))),dateadd(m,1,getdate())),106)
First Day Of Last Month. select CONVERT(varchar,dateadd(d,-(day(dateadd(m,-1,getdate()-2))),dateadd(m,-1,getdate()-1)),106)
Last Day Of Last Month. select CONVERT(varchar,dateadd(d,-(day(getdate())),getdate()),106)
First Day Of Next Month. select CONVERT(varchar,dateadd(d,-(day(dateadd(m,1,getdate()-1))),dateadd(m,1,getdate())),106)
Last Day Of Next Month. select CONVERT(varchar,dateadd(d,-(day(dateadd(m,2,getdate()))),DATEADD(m,2,getdate())),106)
First Day Of Current Year. select CONVERT(varchar,dateadd(year,datediff(year,0,getdate()),0),106)
Last Day Of Current Year. select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+1,0))),106)
First Day of Last Year. select CONVERT(varchar,dateadd(year,datediff(year,0,getdate())-1,0),106)
Last Day Of Last Year. select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate()),0))),106)
First Day Of Next Year. select CONVERT(varchar,dateadd(YEAR,DATEDIFF(year,0,getdate())+1,0),106)
Last Day Of Next Year. select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+2,0))),106)
הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *

תגובה אחת

  1. Ronen12/05/2014 ב 16:30

    Good day 🙂
    First Day Of Current Week, is not a fixed and given value, but depends on your stting. There are countries where the first day of the week is sunday a (Like in Israel, where the above query will not return the correct value) and in other places it can be Monday (where the query will work OK), or any other day in the week. The query in this blog do not take this into consideration, and there for will not return the correct value for everyone. I suspect it is the same with the other queries here (I read only the first one).
    By the way a simple solution for geting the first day of the week can be using:
    DATEADD(DAY,1-DATEPART(DW, GETDATE()),GETDATE())
    We can use the value of @@DATEFIRST in order to get the correct first day of the week acording our seeting.
    ** Compere those results:
    SET DATEFIRST 7 — Sunday (default, U.S. English, Isreale Hebrew)
    select
    GETDATE() [Current_Date],
    CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),0),106) [Blog_Solution],
    CONVERT(VARCHAR,DATEADD(DAY,1-DATEPART(DW, GETDATE()),GETDATE()),106) [First_Day_Of_Current_Week]
    GO

    הגב