This post is an extension to my earlier post regarding datetime formatting tips given below
http://visakhm.blogspot.com/2010/01/some-quick-tips-for-date-formating.html
The impetus behind creating this blog is the increasing requests I've had recently from my own colleagues as well as from many others through forums asking me method for generating date values using integer parameters. The purpose of this blog is to clarify how integer parameters can be used to generate date values. This is made possible due to the way SQL server stores dates internally.
To start off, lets consider a very simple table having structure as below.
On analysing we see that though date values are displayed in standard iso date format (YYYY-MM-DD) we were able to generate next replenishment date by simple integer add operation over original order date. This is due to fact that internally date values are represented in SQL server as numeric data. A value of 0 corresponds to base date value of 1900-01-01 00:00:00. This can be confirmed using below code.
select cast(0 as datetime)
This enables us to do integer operations over dates as shown in earlier example. An integer value of 1 represents a day. See how we can apply various date manipulations
As you see from above by using DATEADD function we were able to add to basedate the various date part portions like month,year,quarter etc.
This is the logic we use to generate date values using integer parameters. See few examples below
See below for similar logic to generate week dates for a year given a week and year integer values as parameters
On analysing, you can see logic used here is subtracting 1900 from year values to get number of years elapsed since base date and add it to basedate to get to the beginning of year ie 1st Jan. Then add passed @week value -1 to get to the beginning of the week under consideration. Putting this logic inside a CTE and recursing on adding a week help you to generate the other week dates till end of year.
Extending this logic further we can very easily generate date ranges for any time period from integer parameters indicating year and required period (day,week,month,quarter etc) values.
http://visakhm.blogspot.com/2010/01/some-quick-tips-for-date-formating.html
The impetus behind creating this blog is the increasing requests I've had recently from my own colleagues as well as from many others through forums asking me method for generating date values using integer parameters. The purpose of this blog is to clarify how integer parameters can be used to generate date values. This is made possible due to the way SQL server stores dates internally.
To start off, lets consider a very simple table having structure as below.
On analysing we see that though date values are displayed in standard iso date format (YYYY-MM-DD) we were able to generate next replenishment date by simple integer add operation over original order date. This is due to fact that internally date values are represented in SQL server as numeric data. A value of 0 corresponds to base date value of 1900-01-01 00:00:00. This can be confirmed using below code.
select cast(0 as datetime)
This enables us to do integer operations over dates as shown in earlier example. An integer value of 1 represents a day. See how we can apply various date manipulations
As you see from above by using DATEADD function we were able to add to basedate the various date part portions like month,year,quarter etc.
This is the logic we use to generate date values using integer parameters. See few examples below
See below for similar logic to generate week dates for a year given a week and year integer values as parameters
On analysing, you can see logic used here is subtracting 1900 from year values to get number of years elapsed since base date and add it to basedate to get to the beginning of year ie 1st Jan. Then add passed @week value -1 to get to the beginning of the week under consideration. Putting this logic inside a CTE and recursing on adding a week help you to generate the other week dates till end of year.
Extending this logic further we can very easily generate date ranges for any time period from integer parameters indicating year and required period (day,week,month,quarter etc) values.
No comments:
Post a Comment