Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Date Calculations - SQL Server

RSS
Modified on Thu, Apr 01, 2010, 12:09 PM by Administrator Categorized as SQL Server

General

{copytext|general}
declare @input datetime
set @input = '01/13/2010 12:31:00 pm'

select
     DateOnly           = convert(datetime, datediff(day, 0, @input))
    ,FirstDayThisMonth  = dateadd(day, 1-day(@input), @input)
    ,LastDayThisMonth   = dateadd(day, 0-day(@input), dateadd(month, 1, @input))
    ,FirstDayNextMonth  = dateadd(day, 1-day(@input), dateadd(month, 1, @input))

The following code will work with SQL Server 2008 and newer, which has the new data type, DATE.

convert(date, getdate())

Special Situations

Combining a DATE and a TIME

{copytext|CombineDateTime}
create function dbo.CombineDateTime
    (
     @d date
    ,@t time
    ) returns datetime as begin

return DATEADD(ms, DATEDIFF(ms, 0, @t), CONVERT(smalldatetime, @d))

end

This Past Monday

{copytext|thisPastMonday}
declare 
     @baseDate datetime
    ,@today    datetime
    ,@n        int
    ,@result   datetime

select
    -- 1/1/1900 is a Monday 
     @baseDate  = '1/1/1900'

    -- Get the date-only (i.e., midnight this morning)
    ,@today     = convert(datetime, datediff(day, 0, getdate()))

    -- Count how many days since a known Monday.  This number MOD 7 will the number of 
    -- days since this past Monday. A result of zero means today is a Monday.
    ,@n         = (datediff(d, @baseDate, @today) % 7) * -1

    ,@result    = dateadd(d, @n, @today)

select result = @result

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.