working on it ...

Filters

Explore Public Snippets

Sort by

Found 12 snippets matching: dateadd

    public by maholtz modified Jul 10, 2014  3267  0  6  3

    Alle Datensätze der letzten Monate

    Das Skript liest aus einer Tabelle alle Datensätze der letzten 18 Monate, so dass pro Benutzer der letzte Login zu sehen ist. Als letzte Spalte wird der Timestamp noch einmal ausgegeben.
    SELECT count(*)
         ,max([oid])
        ,min([timestamp])
        ,[benutzer]
        ,MAX(DATEADD(month, -18, SYSDATETIME()))
    FROM [datenbank].[dbo].[tabelle]
    WHERE
      timestamp > DATEADD(month, -18, SYSDATETIME())
      AND benutzer IS NOT NULL
    GROUP BY benutzer
    
    

    public by maholtz modified Jul 10, 2014  2098  0  6  2

    Datensätze finden, die nicht in den letzten 6 Monaten erzeugt wurden.

    Alle Datensätze werden ermittelt, die älter als 6 Monate sind - ausgehend vom 1. des aktuellen Monats. Es werden also alle Datensätze ermittelt, die nicht in den letzten 6 Monaten erzeugt wurden.
    WHERE (
      date < CONVERT(date, DATEADD(month, -6, DATEADD(day, (-1* DATEPART(day, GETDATE())) +1, CONVERT (date, GETDATE(),  104))), 104)
    )

    public by msdn modified Jan 12, 2015  781  0  6  0

    CreatePhotoInfo: Create a new PhotoInfo object.

    Create a new PhotoInfo object. Initial value of the PhotoId property. Initial value of the FileName property. Initial value of the DateAdded property. Initial value of the Exposure property. Initial value of the Dimensions property. Initial value of the DateModified property.
    using System;
    using System.Data.Objects;
    using System.Data.Objects.DataClasses;
    using System.Data.EntityClient;
    using System.ComponentModel;
    using System.Xml.Serialization;
    using System.Runtime.Serialization;
    
    #region Factory Method
        
    /// <summary>
    /// Create a new PhotoInfo object.
    /// </summary>
    /// <param name="photoId">Initial value of the PhotoId property.</param>
    /// <param name="fileName">Initial value of the FileName property.</param>
    /// <param name="dateAdded">Initial value of the DateAdded property.</param>
    /// <param name="exposure">Initial value of the Exposure property.</param>
    /// <param name="dimensions">Initial value of the Dimensions property.</param>
    /// <param name="dateModified">Initial value of the DateModified property.</param>
    public static PhotoInfo CreatePhotoInfo(global::System.Int32 photoId, global::System.String fileName, global::System.DateTime dateAdded, Exposure exposure, Dimensions dimensions, global::System.DateTime dateModified)
    {
        PhotoInfo photoInfo = new PhotoInfo();
        photoInfo.PhotoId = photoId;
        photoInfo.FileName = fileName;
        photoInfo.DateAdded = dateAdded;
        photoInfo.Exposure = StructuralObject.VerifyComplexObjectIsNotNull(exposure, "Exposure");
        photoInfo.Dimensions = StructuralObject.VerifyComplexObjectIsNotNull(dimensions, "Dimensions");
        photoInfo.DateModified = dateModified;
        return photoInfo;
    }

    external by daryl-scott modified Feb 25, 2016  137  0  2  0

    Provides datetime functions commonly found in other languages such as dateadd, datediff, and truncate.

    Provides datetime functions commonly found in other languages such as dateadd, datediff, and truncate.: datecalc.py
    import datetime
    
    __doc__ = """Provides datetime functions commonly found in other languages such as dateadd, datediff, and truncate."""
    
    YEAR = "YEAR"
    FISCALYEAR = "FISCALYEAR"
    QUARTER = "QUARTER"
    MONTH = "MONTH"
    WEEK = "WEEK"
    DAY = "DAY"
    HOUR = "HOUR"
    MINUTE = "MINUTE"
    SECOND = "SECOND"
    MILLISECOND = "MILLISECOND"
    MICROSECOND = "MICROSECOND"
    
    # Note: Only the value of month is used by functions
    FISCALYEARBEGIN = datetime.datetime(1970, 10, 1)
    
    def lastdayofmonth(year, month):
        """Returns the last day of the supplied month and year."""
        # Get the first of the following month and then subtract one day
        if month == 12:
            tmp_date = datetime.datetime(year = year + 1, month = 1, day = 1)
        else:
            tmp_date = datetime.datetime(year = year, month = month + 1, day = 1)
            
        tmp_date = tmp_date - datetime.timedelta(days = 1)
        out_days = tmp_date.day
        return out_days
    
    def dateadd(datepart, increment, value):
        """Returns a date to which a specified time interval has been added."""
        # Note: The year, quarter, and month functions return the last day of month
        # when the value has more days in the month than in the returned month.
        if datepart in [YEAR, FISCALYEAR]:
            out_year = value.year + increment
            out_month = value.month
            lastday = lastdayofmonth(out_year, out_month)
            out_day = min(value.day, lastday)
            result = value.replace(year = out_year, month = out_month, day = out_day)
        elif datepart == QUARTER:
            months = (value.year * 12) + value.month + (increment * 3)
            out_year, out_month = divmod(months, 12)
            lastday = lastdayofmonth(out_year, out_month)
            out_day = min(value.day, lastday)
            result = value.replace(year = out_year, month = out_month, day = out_day)
        elif datepart == MONTH:
            months = (value.year * 12) + value.month + increment
            out_year, out_month = divmod(months, 12)
            lastday = lastdayofmonth(out_year, out_month)
            out_day = min(value.day, lastday)
            result = value.replace(year = out_year, month = out_month, day = out_day)
        elif datepart == WEEK:
            result = value + datetime.timedelta(days = increment * 7)
        elif datepart == DAY:
            result = value + datetime.timedelta(days = increment)
        elif datepart == HOUR:
            result = value + datetime.timedelta(hours = increment)
        elif datepart == MINUTE:
            result = value + datetime.timedelta(minutes = increment)
        elif datepart == SECOND:
            result = value + datetime.timedelta(seconds = increment)
        elif datepart == MILLISECOND:
            result = value + datetime.timedelta(microseconds = increment * 1000)
        elif datepart == MICROSECOND:
            result = value + datetime.timedelta(microseconds = increment)
        else:
            raise ValueError("Invalid datepart value: '{0}'".format(datepart))
    
        return result
    
    def datediff(datepart, startdate, enddate):
        """Returns the number of intervals between two dates."""
        delta = enddate - startdate
    
        if datepart in [YEAR, FISCALYEAR]:
            result = enddate.year - startdate.year
        elif datepart == QUARTER:
            result = ((enddate.year - startdate.year) * 12 + enddate.month - startdate.month) // 3
        elif datepart == MONTH:
            result = (enddate.year - startdate.year) * 12 + enddate.month - startdate.month
        elif datepart == WEEK:
            result = delta.days // 7
        elif datepart == DAY:
            result = delta.days
        elif datepart == HOUR:
            result = (delta.days * 24) + (delta.seconds // 3600)
        elif datepart == MINUTE:
            # 24 hours/day * 60 minutes/hour = 1440 minutes/day; 60 seconds / minute
            result = (delta.days * 1440) + (delta.seconds // 60)
        elif datepart == SECOND:
            # 24 hours/day * 60 minutes/hour * 60 seconds/minutes = 86400 seconds / day
            result = (delta.days * 86400) + delta.seconds
        elif datepart == MILLISECOND:
            # 1 microsecond = 1000 milliseconds
            result = (((delta.days * 86400) + delta.seconds) * 1000000 + delta.microseconds) // 1000
        elif datepart == MICROSECOND:
            # 1 second = 1000000 microseconds
            result = ((delta.days * 86400) + delta.seconds) * 1000000 + delta.microseconds
        else:
            raise ValueError("Invalid datepart value: '{0}'".format(datepart))
    
        return result
    
    def datepart(datepart, value):
        """Returns the specified part of a given date."""
        if datepart == YEAR:
            result = value.year
        elif datepart == FISCALYEAR:
            fiscalmonth = FISCALYEARBEGIN.month
            result = value.year + (value.month - fiscalmonth) // 12
        elif datepart == QUARTER:
            result = (value.month - 1) // 3 + 1
        elif datepart == MONTH:
            result = value.month
        elif datepart == WEEK:
            # Week number of the year (Sunday as the first day of the week) as a decimal number [00,53].
            # All days in a new year preceding the first Sunday are considered to be in week 0.
            result = int(value.strftime("%U"))
        elif datepart == DAY:
            result = value.day
        elif datepart == HOUR:
            result = value.hour
        elif datepart == MINUTE:
            result = value.minute
        elif datepart == SECOND:
            result = value.second
        elif datepart == MILLISECOND:
            result = value.microsecond // 1000
        elif datepart == MICROSECOND:
            result = value.microsecond
        else:
            raise ValueError("Invalid datepart value: '{0}'".format(datepart))
    
        return result
    
    def split(value, epoch = None):
        """Split the input date value into a date, time pair.  The returned values are datetime objects
        with the time component of 'date' set to 0, and the date component of 'time' set to epoch."""
        epoch = epoch or datetime.datetime(1900, 1, 1)
        result_date = datetime.datetime(year = value.year, month = value.month, day = value.day)
        result_time = value.replace(year = epoch.year, month = epoch.month, day = epoch.day)
        return result_date, result_time
    
    def timestamp():
        """Returns current date and time truncated to second"""
        now = datetime.datetime.now()
        result = now.replace(microsecond = 0)
        return result
    
    def today():
        """Returns current date as a datetime object."""
        now = datetime.datetime.now()
        result = datetime.datetime(now.year, now.month, now.day)
        return result
    
    def truncate(datepart, value):
        """Returns a date representing the smallest possible value of an interval."""
        if datepart == YEAR:
            result = datetime.datetime(year = value.year, month = 1, day = 1)
        elif datepart == FISCALYEAR:
            fiscalmonth = FISCALYEARBEGIN.month
            fiscalyear = value.year + (value.month - fiscalmonth) // 12
            result = datetime.datetime(year = fiscalyear, month = fiscalmonth, day = 1)
        elif datepart == MONTH:
            result = datetime.datetime(year = value.year, month = value.month, day = 1)
        elif datepart == QUARTER:
            quarterMonth = (value.month - 1) // 3 * 3 + 1
            result = datetime.datetime(year = value.year, month = quarterMonth, day = 1)
        elif datepart == WEEK:
            # Return first day of week (Sunday)
            weekday = value.isoweekday() % 7
            out_day = datetime.datetime(year = value.year, month = value.month, day = value.day)
            result = out_day - datetime.timedelta(days = weekday)
        elif datepart == DAY:
            result = datetime.datetime(year = value.year, month = value.month, day = value.day)
        elif datepart == HOUR:
            result = value.replace(minute = 0, second = 0, microsecond = 0)
        elif datepart == MINUTE:
            result = value.replace(second = 0, microsecond = 0)
        elif datepart == SECOND:
            result = value.replace(microsecond = 0)
        elif datepart == MILLISECOND:
            microsecond = value.microsecond // 1000 * 1000
            result = value.replace(microsecond = microsecond)
        elif datepart == MICROSECOND:
            result = value
        else:
            raise ValueError("Invalid datepart value: '{0}'".format(datepart))
    
        return result
    
    
    

    external by jame2408 modified Jan 4, 2017  13  0  1  0

    DATEADD 與 DATEDIFF 日期應用

    DATEADD 與 DATEDIFF 日期應用: DATEADD與DATEDIFF的應用.sql
    /*
    DATEADD與DATEDIFF的應用:
    
    1. DATEDIFF:起始日與迄日之間差異了幾天(Day)/月(MONTH)/季(QUARTER)/年(YEAR)
        - 常見用法:DATEDIFF(Day/Week/MONTH/QUARTER/YEAR, 起日, 迄日)
        - 起日/迄日:
    	- 日期部份若放為『0』或『''』,代表資料庫最小時間1900-01-01 00:00:00.000。
    	- 同理,若為『-1』,代表1899-12-31 00:00:00.000。
    2. DATEADD:增加/減少天數(Day)/月(MONTH)/季(QUARTER)/年(YEAR),範例如下:
        - 加一個月 DATEADD(MONTH,  1 ,GETDATE())
        - 減一天 DATEADD(DAY,  -1 ,GETDATE())
    
    資料來源:
        - 我的Coding之路(https://dotblogs.com.tw/lastsecret/2010/10/04/18097)
        - ~楓花雪岳~(http://jengting.blogspot.tw/2011/09/sql-dateadddatediff.html)
    */
    
    --當月第一天
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) ,0)
    
    --當月最後一天
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1)
    
    --當季第一天(1月1號、4月1號、7月1號、10月1號)
    SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0)
    
    --當季最後一天(3月30號、6月30號、9月30號、12月31號)
    SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, -1, GETDATE()), -1)
    
    --當天 00:00:00.000(午夜12點,一天的開始)
    SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
    
    --將西元年(datetime格式)轉換成民國年(CHAR格式)
    select CAST(DATEDIFF(YEAR, '1911-01-01', getdate()) AS VARCHAR(3)) +
    	   REPLACE(CONVERT(CHAR(5), GETDATE(), 1),'/','')
    
    /* 
    
    取得特定日期是否為週末假日:
    	1900-01-01為星期一,用 DATEDIFF 函數算出任意日期與這一天的差異天數,再取得除以 7 的餘數,可以算出以下數據:
    	|  星期  |  餘數  |  備註  |  整除於5的值  |
    	|   一   |   0   | 工作天  |      0      | 
    	|   二   |   1   | 工作天  |      0      | 
    	|   三   |   2   | 工作天  |      0      | 
    	|   四   |   3   | 工作天  |      0      | 
    	|   五   |   4   | 工作天  |      0      | 
    	|   六   |   5   |  週末   |      1      | 
    	|   日   |   6   |  週末   |      1      | 
    	從上表可知我們若要透過 T-SQL 語法計算該日期是否為週末假日,就可以用以下語法取得 True/False 型態的結果
    
    資料來源:The Will Will Web(http://blog.miniasp.com/post/2009/01/20/How-to-use-simple-T-SQL-to-get-weekend-status-in-date.aspx)
    
    */
    select DATEDIFF(DAY, 0, '2016-08-28') % 7 / 5   -- 1 (星期日)
    select DATEDIFF(DAY, 0, '2016-08-29') % 7 / 5   -- 0 (星期一)
    select DATEDIFF(DAY, 0, '2016-08-30') % 7 / 5   -- 0 (星期二)
    select DATEDIFF(DAY, 0, '2016-08-31') % 7 / 5   -- 0 (星期三)
    select DATEDIFF(DAY, 0, '2016-09-01') % 7 / 5   -- 0 (星期四)
    select DATEDIFF(DAY, 0, '2016-09-02') % 7 / 5   -- 0 (星期五)
    select DATEDIFF(DAY, 0, '2016-09-03') % 7 / 5   -- 1 (星期六)
    
    select DATEDIFF(DAY, 0, getdate()) % 7 / 5 -- 看執行結果為0或1就知道是否為週末
    
    

    external by Goos modified Jun 6, 2014  663  3  3  0

    DateAdditions.swift

    DateAdditions.swift: gistfile1.swift
    //
    //  DateAdditions.swift
    //  OMGSWIFT
    //
    //  Created by Robin Goos on 03/06/14.
    //  Copyright (c) 2014 OMG. All rights reserved.
    //
    
    import Foundation
    
    enum TimeUnit: Int {
        case Second = 1
        case Minute = 60
        case Hour = 3600
    }
    
    struct Time {
        var value: Double
        init (value: Double, type: TimeUnit = .Second) {
            self.value = value
        }
        
        // Computed properties
        var ago : NSDate {
            let now = NSDate()
            return now.dateByAddingTimeInterval(-self.toTimeInterval())
        }
        
        var fromNow : NSDate {
            let now = NSDate()
            return now.dateByAddingTimeInterval(self.toTimeInterval())
        }
        
        // Conversions
        func toSeconds() -> Double {
            return self.value
        }
        
        func toMinutes() -> Double {
            return self.value / Double(TimeUnit.Minute.toRaw())
        }
        
        func toHours() -> Double {
            return self.value / Double(TimeUnit.Hour.toRaw())
        }
        
        func toTimeInterval() -> NSTimeInterval {
            return NSTimeInterval(self.value)
        }
        
        // Methods
        func since(date: NSDate) -> NSDate {
            return date.dateByAddingTimeInterval(self.toTimeInterval())
        }
    }
    
    @infix func + (left: NSDate, right: Time) -> NSDate {
        return left.dateByAddingTimeInterval(right.toTimeInterval())
    }
    
    @infix func - (left: NSDate, right: Time) -> NSDate {
        return left.dateByAddingTimeInterval(-right.toTimeInterval())
    }
    
    @infix func + (left: Time, right: Time) -> Time {
        return Time(value: left.value + right.value, type: .Second)
    }
    
    @infix func - (left: Time, right: Time) -> Time {
        return Time(value: left.value - right.value, type: .Second)
    }
    
    extension Int {
        var seconds : Time {
            return Time(value: Double(self), type: .Second)
        }
        var minutes : Time {
            return Time(value: Double(self), type: .Minute)
        }
        var hours : Time {
            return Time(value: Double(self), type: .Hour)
        }
    }
    
    //let aDate = 24.hours.ago
    //let recent = 10.minutes.ago
    //let recenter = NSDate() - 5.minutes
    //let now = 10.minutes.since(recent)
    //let soon = now + 10.minutes
    //let soonerStill = 30.minutes.fromNow
    
    

    external by gelizondo modified Dec 5, 2016  78  0  1  0

    dateAdd() - eBavel v6

    dateAdd() - eBavel v6: dateAdd().txt
    // dateAdd() agrega un valor de día, mes o año a el valor de una fecha y su valor regresa como el de la fecha actualizada.
    
    // Sintaxis - Los formatos permitidos son (En comillas): 'Day', 'Month', 'Year'
        dateAdd('Formato',Valor númerico a incrementar,[Fecha@Forma])     // El valor numerico va sin comillas
    
    Ejemplos:      
        // Se puede utilizar un campo para asignar el valor numerico
            dateAdd('Formato',[Valor@Forma],[Fecha@Forma])
    
        // Se puede utilizar la funcion currentDate()
            dateAdd('Formato',[Valor@Forma],currentDate())
            
        // Se puede agregar el valor a una fecha que venga de otra función
           
           // Diferencia en Fechas
                dateAdd('Formato',[Valor@Forma],dateDif([Fecha Inicial@Forma],[Fecha Final@Forma],'Formato'))
                            ó
           // Diferencia en Fechas + Hora
                dateAdd('Formato',[Valor@Forma],dateDif(Concat([Fecha Inicial@Forma],' ',[Hora Inicial@Forma]),Concat([Fecha Final@Forma],'             ',[Hora Final@Forma]),'Formato'))
                
    Nota.- El valor a devolverce debe quedar de ser posible en un campo Date
    
    

    external by Derek Marley modified Sep 20, 2014  22  0  1  0

    I'm trying to figure this out.

    I'm trying to figure this out.: DATEADD-DATEDIFF.SQL
    M
    WHERE DATE_EVENT 
    	BETWEEN 
    	DATEADD(MONTH, DATEDIFF(MONTH, '19000201', 
    		--'2/25/2012'),'19000101') 
    		GETDATE()), '19000101') 	
    	AND
    	DATEADD(MONTH, DATEDIFF(MONTH, '19000101', 
    		--'2/25/2012'),'19000101') 
    		GETDATE()), '18991231')
    		
    
    

    external by Chui Hin Wah (Dickson) modified Feb 3, 2015  22  0  1  0

    js Date functions

    js Date functions: dateAdd
    /*
     * copied from http://stackoverflow.com/a/1214753
     */
    function dateAdd(date, interval, units) {
      var ret = new Date(date); //don't change original date
      switch(interval.toLowerCase()) {
        case 'year'   :  ret.setFullYear(ret.getFullYear() + units);  break;
        case 'quarter':  ret.setMonth(ret.getMonth() + 3*units);  break;
        case 'month'  :  ret.setMonth(ret.getMonth() + units);  break;
        case 'week'   :  ret.setDate(ret.getDate() + 7*units);  break;
        case 'day'    :  ret.setDate(ret.getDate() + units);  break;
        case 'hour'   :  ret.setTime(ret.getTime() + units*3600000);  break;
        case 'minute' :  ret.setTime(ret.getTime() + units*60000);  break;
        case 'second' :  ret.setTime(ret.getTime() + units*1000);  break;
        default       :  ret = undefined;  break;
      }
      return ret;
    }
    
    

    external by jz3lada modified May 27, 2014  296  0  3  0

    Quitar/Agregar a un fecha una cantidad de días/meses/años especifica

    Quitar/Agregar a un fecha una cantidad de días/meses/años especifica: DATEADD
    /*
      for more info:
              http://msdn.microsoft.com/es-es/library/ms186819.aspx
    */
    
    DECLARE @Date DATETIME
    SET @Date=Getdate()
    
      DATEADD(DAY,-1,@Date)
      DATEADD(YEAR,2,@Date)
    
    
    • Public Snippets
    • Channels Snippets