Skip to main content

Home  Design Book  DMV Book  Presentations  Code  Calendar  Contact  About   
Snippets > Downloadable Packages >  
These snippets of code are intended to be templates for quick bits of code that are commonly useful in day to day coding. 
 

Snippet

Versions

Code

Extended Properties2005 and later  Snippets - Extended Properties

Strip Time From Date Value

2005 and earlier

DATEADD(DAY, 0, DATEDIFF(DAY, 0, <dateValue>))

2008

CAST(<dateValue> as date)

First Day of the Month relative to a date value

All

DATEADD(day, 0, DATEDIFF(day, 0, <datevalue> )

- DATEPART(DAY,<datevalue>) + 1)

Last Day of the Month relative to a date value

ALL

DATEADD(month, 1, DATEDIFF(day, 0, <datevalue> )

- DATEPART(DAY,<datevalue>) )

First Day of the Year relative to a date value

ALL

DATEADD(day, 0, DATEDIFF(day, 0, <datevalue> )

- DATEPART(dayofyear,<datevalue> ) + 1)

Last Day of the Year relative to a date value

ALL

DATEADD(year, 1, DATEDIFF(day, 0, <datevalue> )

- DATEPART(dayofyear,<datevalue> ) ))

Sequence Number code

2005 and later

;with digits (i) as(
select 1 as i union all select 2 as i union all select 3 union all
select 4 union all select 5 union all select 6 union all select 7 union all
select 8 union all select 9 union all select 0)
,sequence (i) as (
SELECT D1.i
+ (10*D2.i)
+ (100*D3.i)
+ (1000*D4.i)
+ (10000*D5.i)
+ (100000*D6.i)
FROM digits AS D1
,digits AS D2
,digits AS D3
,digits AS D4
,digits as D5
,digits As D6
)
select i from sequence order by i