SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Date/Time Info and Script Links
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/15/2006 :  23:10:17  Show Profile  Reply with Quote
This post is to provide information and Script Library links related to datetime. There are also links to other resources.


List of Subjects
Typical Date Query
Uses of the DATETIME data type
Finding the Start of Time Periods
Finding the End of Time Periods
Generating Date Tables
Getting Time Only from DateTime
Finding Age
Finding ISO Weeks
Converting Year, Month, and Day to DateTime
Converting to/from UNIX Time
Finding the midpoint between two datetimes
Generating Random Datetimes
Creating a Formatted Calendar
Find Datetime Range Overlaps
Calculate overlap seconds of datetime ranges
Convert seconds to HHH:MM:SS
Function to emulate the Microsoft Excel DAYS360 function
A More Precise DateDiff Function
Links to other Date, Time, and Calendar Resources



Typical Date Query
How to query a table with a selection on a datetime column, for example, find all items for the date 2006-01-14. This isn’t really a script, but it is one of the most common questions about datetime.
Select
	*
from
	NyTable
Where
	MyDateColumn >=  '20060114' and
	MyDateColumn <   '20060115'

Notice that you are asking for greater than or equal to the beginning of the date, and less than the following date. You can apply the same general query for any range of days. This is almost always the best way to write a query of this type, because it allows SQL Server to use any index that exists on the datetime column, and it uses less resources than a query that applies a function to the datetime column.

Notice that the query dates are in format YYYYMMDD; you should always use this format for date strings. This is SQL Servers "universal" date format that works the same with all settings of DATEFIRST. Any other format may produce an error if the setting of DATEFIRST is not exactly what you expect.

For datetime strings use universal format
YYYYMMDD HH:MM:SS.MIL (20061231 23:59:59.997).




Uses of the DATETIME data type
The DATETIME data type can be used to hold four different types of date/time values:
1. Date and time – a date and time together
Example: 2006-07-15 12:06:15.333
2. Date – a date only stored as the time at midnight:
Example: 2006-07-15 00:00:00.000
3. Time – a time only stored as time on the DATETIME zero date, 1900-01-01.
Example: 1900-01-01 12:06:15.333
4. Elapsed time – a difference between two DATETIME values, stored as the time since the DATETIME zero point, 1900-01-01 00:00:00.000.
Example: 1900-01-03 14:12:34.443

The actual usage of the value is only defined in the context of the application. There is no way to specify that a DATETIME is to be used for a date and time, date only, time only, or elapsed time. It is possible to insure that a column in a table contains date only or time only by adding a constraint to a column, but is is necessary that the application format the DATETIME value properly.

The following script briefly demonstrates the four different ways to use DATETIME, and several conversions from one type to another: date and time to date only, date and time to time only, date only plus time only to date and time, two date and time values to elapsed time, and elapsed time to individual days, hours, minutes, seconds, and milliseconds.

-- Demo four uses of DATETIME datatype
declare @datetime1 datetime
declare @datetime2 datetime
declare @date_only datetime
declare @time_only datetime
declare @date_plus_time datetime
declare @elapsed_time datetime
declare @elapsed_days int
declare @elapsed_hours int
declare @elapsed_minutes int
declare @elapsed_seconds int
declare @elapsed_milliseconds int

-- Load 2 datetime values
select @datetime1 = '20060715 12:06:15.333'
select @datetime2 = '20060718 02:18:49.777'

-- Get date only from datetime using DATEAADD/DATEDIFF functions
select @date_only = dateadd(day,datediff(day,0,@datetime1),0)

-- Get time only from datetime by subtracting date only
select @time_only = @datetime2-dateadd(day,datediff(day,0,@datetime2),0)

-- Add date only and time only together
select @date_plus_time = @date_only+@time_only

-- Get elapsed time as the difference between 2 datetimes
select @elapsed_time = @datetime2-@datetime1

-- Get elapsed time parts as time since 1900-01-01 00:00:00.000
select @elapsed_days = datediff(day,0,@elapsed_time)
select @elapsed_hours = datepart(hour,@elapsed_time)
select @elapsed_minutes = datepart(minute,@elapsed_time)
select @elapsed_seconds = datepart(second,@elapsed_time)
select @elapsed_milliseconds = datepart(millisecond,@elapsed_time)

declare @cr varchar(4), @cr2 varchar(4)
select @cr = char(13)+Char(10)
select @cr2 = @cr+@cr


print	'Results:'+@cr2
print	'Datetime1 = '+convert(varchar(30),@datetime1,121)+@cr+
	'Datetime2 = '+convert(varchar(30),@datetime2,121)+@cr2

print	'Date Only = '+convert(varchar(30),@date_only,121)+
	', from Datetime1 = '+convert(varchar(30),@datetime1,121)+@cr2

print	'Time Only = '+convert(varchar(30),@time_only,121)+
	', from Datetime2 = '+convert(varchar(30),@datetime2,121)+@cr2

print	'Add date and time: '+convert(varchar(30),@date_plus_time,121)+' ='+@cr+
	'	 '+convert(varchar(30),@date_only,121)+
	' + '+convert(varchar(30),@time_only,121)+@cr2

print	'Elapsed Time: '+convert(varchar(30),@elapsed_time,121)+' ='+@cr+
	'	 '+convert(varchar(30),@datetime2,121)+
	' - '+convert(varchar(30),@datetime1,121)+@cr2

print	'Elapsed Time Parts:'+@cr+
	' Days         = '+convert(varchar(20),@elapsed_days)+@cr+
	' Hours        = '+convert(varchar(20),@elapsed_hours)+@cr+
	' Minutess     = '+convert(varchar(20),@elapsed_minutes)+@cr+
	' Secondss     = '+convert(varchar(20),@elapsed_seconds)+@cr+
	' Milliseconds = '+convert(varchar(20), @elapsed_milliseconds)+@cr2+@cr2
Results:

Datetime1 = 2006-07-15 12:06:15.333
Datetime2 = 2006-07-18 02:18:49.777

Date Only = 2006-07-15 00:00:00.000, from Datetime1 = 2006-07-15 12:06:15.333

Time Only = 1900-01-01 02:18:49.777, from Datetime2 = 2006-07-18 02:18:49.777

Add date and time: 2006-07-15 02:18:49.777 =
	 2006-07-15 00:00:00.000 + 1900-01-01 02:18:49.777

Elapsed Time: 1900-01-03 14:12:34.443 =
	 2006-07-18 02:18:49.777 - 2006-07-15 12:06:15.333

Elapsed Time Parts:
 Days         = 2
 Hours        = 14
 Minutess     = 12
 Secondss     = 34
 Milliseconds = 443

This is a query to sum elapsed datetime values and return the sum as a datetime value that is an offset from 1900-01-01 00:00:00.000.
select
	TotalElapsedTime =
	dateadd(dd,sum(datediff(dd,0,a.ET)),
	dateadd(hh,sum(datepart(hh,a.ET)),
	dateadd(mi,sum(datepart(mi,a.ET)),
	dateadd(ss,sum(datepart(ss,a.ET)),
	dateadd(ms,sum(datepart(ms,a.ET)),0)))))
from
	( -- Test Data
	select ET = convert(datetime,'1900-01-01 07:49:03.000')	union all
	select ET = convert(datetime,'1900-01-01 02:28:06.000')	union all
	select ET = convert(datetime,'1900-01-01 03:09:01.003')	union all
	select ET = convert(datetime,'1900-01-01 06:19:02.040')	union all
	select ET = convert(datetime,'1900-01-07 06:19:02.000')
	) a


Results:

TotalElapsedTime                                       
------------------------------------------------------ 
1900-01-08 02:04:14.043

(1 row(s) affected)








Finding the Start of Time Periods
One of the most common questions is how to remove the time from a datetime so that you end up with just a date. In other words, change 2006/12/13 02:33:48.347 to 2006/12/13 00:00:00.000. The following links have functions that will find the start of Century, Decade, Year, Quarter, Month, Week, Day, Hour, 30 Minutes, 20 Minutes, 15 Minutes, 10 Minutes , 5 Minutes , x number of Minutes ,Minute , or Second.
Start of Time Period Functions:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755
Start of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
Start of Week Function, Part Deux:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59927
Convert DateTime to Date using Rounding UDF:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62354



Finding the End of Time Periods
Sometimes there is a need to find the last day of a time period. The following links have functions that will find the last day of Century, Decade, Year, Quarter, Month, or Week.
End Date of Time Period Functions:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64759
End of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64760



Generating Date Tables
It can be very useful to have a table with a list of dates, and various attributes of those dates, especially for complex reporting. The functions on these links can be used to load a date table with many different columns of date attributes.
Date Table Function F_TABLE_DATE:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
Here is another approach that also includes a function for calculating Easter. I haven’t tried it myself.
Create Date Table with UK & Easter bank holidays:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49711



Getting Time Only from DateTime
By convention, a time only column is stored in SQL Server as an offset from 1900-01-01 00:00:00.000. The function on this link will get the time from a datetime value.
Time Only Function: F_TIME_FROM_DATETIME
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65358



Finding Age
Computing the age of someone is more difficult than it might seem when you take into account different month lengths, leap year, and other things.
This function returns age in format YYYY MM DD.
Age Function F_AGE_YYYY_MM_DD:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729
This function returns age in years.
Age Function F_AGE_IN_YEARS:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462
This link is more of a discussion of the problem of calculating age than a script you can use, but it does show the difficulties. I haven’t tried it myself.
Calculating age in years:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11578



Finding ISO Weeks
The ISO 8601 standard for dates defines a standard way of assigning a unique number to each week starting on Monday. The following functions can be used to return ISO weeks. The date table functions mentioned in the "Generating Date Tables" subject above also have columns for ISO weeks.
ISO Year Week Day of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60515
ISO Week of Year Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60510



Converting Year, Month, and Day to DateTime
The functions on this link will take input parameters of Year, Month, and Day and return a datetime. There are several version posted.
Make Date function (like in VB):
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339



Converting to/from UNIX Time
The functions in this script can be used to convert to/from SQL Server date time to UNIX Time.
UNIX Time Conversion Functions:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66858



Finding the midpoint between two datetimes
The function in this script finds the midpoint in time between two datetimes.
Datetime Range Midpoint Function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=68806



Generating Random Datetimes
The functions on this link can be used to generate random datetimes, random integers, and random samples.
Random Integer, Sample, and Datetime Functions
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69499



Creating a Formatted Calendar
There are several methods is this link that will return a result set with a formatted calendar.
Calender In Sql Server:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44865



Find Datetime Range Overlaps
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=108848



Calculate overlap seconds of datetime ranges
Time overlap calculations
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77202



Converts seconds to HHH:MM:SS
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107275



Function emulates the Microsoft Excel DAYS360 function
DATEDIFF360
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105375



A More Precise DateDiff Function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78730



Links to other Date, Time, and Calendar Resources
This post has links to other resources for date and time information, as well as many other commonly asked questions about SQL Server.
FAQ - Frequently Given Answers:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210

This blog entry has links to various date time subjects.
Fun with Dates (Date Conversion examples):
http://weblogs.sqlteam.com/brettk/archive/2005/06/02/5528.aspx

This external link has a lot of information on the SQL Server datetime datatype, writing queries with datetime, and various datetime operations.
Demystifying the SQL Server DATETIME Datatype:
http://www.sql-server-performance.com/fk_datetime.asp

These external links are a series of articles about working about working with SQL Server Date/Time.
Working with SQL Server Date/Time Variables:
http://www.databasejournal.com/features/mssql/article.php/10894_2191631_1
Part Two - Displaying Dates and Times in Different Formats:
http://www.databasejournal.com/features/mssql/article.php/10894_2197931_1
Part Three - Searching for Particular Date Values and Ranges:
http://www.databasejournal.com/features/mssql/article.php/10894_2209321_1
Part Four - Date Math and Universal Time:
http://www.databasejournal.com/features/mssql/article.php/10894_2216011_1

This external link explains how the datetime datatypes work in SQL Server, including common pitfalls and general recommendations.
Guide to the datetime datatypes:
http://www.karaszi.com/SQLServer/info_datetime.asp

These external links discuss the ISO 8601 standards of dates and times.
Numeric representation of Dates and Time:
http://www.iso.org/iso/en/prods-services/popstds/datesandtime.html
ISO 8601:
http://en.wikipedia.org/wiki/ISO_8601
A summary of the international standard date and time notation:
http://www.cl.cam.ac.uk/~mgk25/iso-time.html

This external link explains how time is calculated on UNIX systems.
Unix Time:
http://en.wikipedia.org/wiki/Unix_time

These are external links to the U.S. Naval Observatory, an authority in the area of Precise Time.
The U.S. Naval Observatory Home:
http://www.usno.navy.mil/
The Official Standard of Time for the United States:
http://tycho.usno.navy.mil/

This external link has Clock, Calendar, Time Zone, and Holiday information for most of the world:
http://www.timeanddate.com/

This external link has a lot of information on the subject of Calendars.
Frequently Asked Questions about Calendars:
http://www.tondering.dk/claus/calendar.html

If you don't have any idea what all this is about,
You may need to Learn SQL:
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

And finally, the primary Microsoft SQL Server References:
SQL Server 2000 Books Online
http://msdn2.microsoft.com/en-us/library/aa257103(SQL.80).aspx
SQL Server 2005 Books Online
http://msdn2.microsoft.com/en-us/library/ms130214.aspx









CODO ERGO SUM

Edited by - Michael Valentine Jones on 10/31/2008 12:22:55

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 05/02/2006 :  02:26:22  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Well MVJ.
I also want to add some more links related to DateTime

Time behaviour in SQL Server
http://www.windowsitpro.com/Articles/Print.cfm?ArticleID=15574

Search for date and time
http://vyaskn.tripod.com/searching_date_time_values.htm

How to query on dates
http://www.sql-server-performance.com/fk_datetime.asp

Different date formats in sql server
http://weblogs.sqlteam.com/derrickl/archive/2005/01/08/3959.aspx

Julian date converter
http://aa.usno.navy.mil/data/docs/JulianDate.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 05/02/2006 :  05:00:46  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

Well MVJ.
I also want to add some more links related to DateTime

Time behaviour in SQL Server
http://www.windowsitpro.com/Articles/Print.cfm?ArticleID=15574

Search for date and time
http://vyaskn.tripod.com/searching_date_time_values.htm

How to query on dates
http://www.sql-server-performance.com/fk_datetime.asp

Different date formats in sql server
http://weblogs.sqlteam.com/derrickl/archive/2005/01/08/3959.aspx

Julian date converter
http://aa.usno.navy.mil/data/docs/JulianDate.html

Madhivanan

Failing to plan is Planning to fail



I already had this link in my post:
http://www.sql-server-performance.com/fk_datetime.asp

I couldn't see this one; it one seems to require registration:
http://www.windowsitpro.com/Articles/Print.cfm?ArticleID=15574

If you are interested in the Julian date, my date table function returns both the Julian Date and the Modified Julian date. The Julian Date returned is as of Noon on the date in the table. The Modified Julian date is as of midnight.
Date Table Function F_TABLE_DATE:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519



CODO ERGO SUM
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 05/02/2006 :  05:48:00  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Sometimes I am confused with different methods that are used to calculate Julian date

For the date 2006-05-02,

MVJ
2453858 and 53857

http://www.minisolve.com/TipsJulianSQL.htm
106122

http://weblogs.sqlteam.com/brettk/archive/2005/06/02/5528.aspx
2006122


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 05/02/2006 :  08:07:04  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

Sometimes I am confused with different methods that are used to calculate Julian date

For the date 2006-05-02,

MVJ
2453858 and 53857

http://www.minisolve.com/TipsJulianSQL.htm
106122

http://weblogs.sqlteam.com/brettk/archive/2005/06/02/5528.aspx
2006122


Madhivanan

Failing to plan is Planning to fail


The problem is that the term Julian Date is used two different ways. The Julian Date and Modified Julian Date from my function are the astronomical dates described in the link below, and called JULIAN_DATE and MODIFIED_JULIAN_DATE in my function. This is also the Julian Date returned from the link you posted from the US Naval Observatory.

The date from those links is the ordinal date described below. This is also available in my function as column YEAR_DAY_OF_YEAR.


http://en.wikipedia.org/wiki/Julian_Day
"The Julian day or Julian day number (JDN) is the number of days that have elapsed since 12 noon Greenwich Mean Time (UT or TT) on Monday, January 1, 4713 BC in the proleptic Julian calendar 1. That day is counted as Julian day zero. The Julian day system was intended to provide astronomers with a single system of dates that could be used when working with different calendars and to unify different historical chronologies.
...
...
The term Julian date is also used to refer to:
Julian calendar dates
ordinal dates (day-of-year)

The use of Julian date to refer to the day-of-year (ordinal date) is usually considered to be incorrect."





CODO ERGO SUM

Edited by - Michael Valentine Jones on 05/02/2006 17:41:41
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 05/02/2006 :  09:26:27  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>The use of Julian date to refer to the day-of-year (ordinal date) is usually considered to be incorrect."

Thanks. Thats what I was also thinking of

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 05/03/2006 :  02:48:26  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Can you highlight the change you made?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 05/28/2006 :  18:27:25  Show Profile  Reply with Quote
Added link to subject Converting to/from UNIX Time.


CODO ERGO SUM

Edited by - Michael Valentine Jones on 05/28/2006 18:34:04
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/01/2006 :  21:18:46  Show Profile  Reply with Quote
Added links for SQL Server Books Online



CODO ERGO SUM
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 06/02/2006 :  02:54:34  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
I think it is better to highlight the change you made as Kriten does in his sticky topic

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/18/2006 :  16:03:55  Show Profile  Reply with Quote
Added section for "Uses of the DATETIME data type"


CODO ERGO SUM
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 06/18/2006 :  16:07:01  Show Profile  Reply with Quote
Pedantic point, but some servers I look after will cough on

	MyDateColumn >=  '2006-01-14' and
	MyDateColumn <   '2006-01-15'

and will only be happy with 'yyyymmdd' - i.e. no hyphens. Goodness knows what their locale etc. is!

Kristen
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/18/2006 :  16:14:45  Show Profile  Reply with Quote
quote:
Originally posted by Kristen

Pedantic point, but some servers I look after will cough on

	MyDateColumn >=  '2006-01-14' and
	MyDateColumn <   '2006-01-15'

and will only be happy with 'yyyymmdd' - i.e. no hyphens. Goodness knows what their locale etc. is!

Kristen


I'm just trying to drive traffic to SQLTeam so you can keep your post count up.


Looks like date format ydm will do it.
Is there some national setting where ydm is the default?

set dateformat ydm
go
select D1 = convert(datetime,'20010215')
go
select D2 = convert(datetime,'2001-15-02')
go
select D3 = convert(datetime,'2001-02-15')

Results:

D1                                                     
------------------------------------------------------ 
2001-02-15 00:00:00.000

(1 row(s) affected)

D2                                                     
------------------------------------------------------ 
2001-02-15 00:00:00.000

(1 row(s) affected)

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.


CODO ERGO SUM

Edited by - Michael Valentine Jones on 06/18/2006 16:45:35
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 06/19/2006 :  03:56:30  Show Profile  Reply with Quote
"is there some national setting where ydm is the default?"

The Japanese do it that way, but not sure if its the default for a Japanese Locale in SQL

Without your SET DATEFORMAT I get:

select K1 = convert(datetime,'20010102')
go
select K2 = convert(datetime,'2001-01-02')
go

K1                                                     
------------------------------------------------------ 
2001-01-02 00:00:00.000

K2                                                     
------------------------------------------------------ 
2001-02-01 00:00:00.000

Gawd knows what locale that client's server is set to ...

Kristen
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 07/09/2006 :  17:25:38  Show Profile  Reply with Quote
Added link to a new function I posted under subject "Finding the midpoint between two datetimes"

CODO ERGO SUM

Edited by - Michael Valentine Jones on 07/23/2006 16:33:43
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 07/23/2006 :  16:34:37  Show Profile  Reply with Quote
Added link to new functions I posted under subject "Random Integer, Sample, and Datetime Functions"



CODO ERGO SUM
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/01/2006 :  16:31:37  Show Profile  Reply with Quote
Updated "Finding the Start of Time Periods" to mention function for start of X numver of minutes.

CODO ERGO SUM
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/04/2006 :  11:35:50  Show Profile  Reply with Quote
Updated "Typical Date Query" to add information about using the universal format for date strings.


CODO ERGO SUM
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/04/2006 :  16:04:49  Show Profile  Reply with Quote
Updated "Finding Age" to add a link to function F_AGE_IN_YEARS.




CODO ERGO SUM
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 12/27/2006 :  12:13:06  Show Profile  Reply with Quote
Updated link for SQL Server 2000 Books Online



CODO ERGO SUM
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/14/2008 :  17:48:07  Show Profile  Reply with Quote
Updated original post to add links to following topics:
Find Datetime Range Overlaps
Calculate overlap seconds of datetime ranges
Convert seconds to HHH:MM:SS
Function to emulate the Microsoft Excel DAYS360 function
A More Precise DateDiff Function


CODO ERGO SUM

Edited by - Michael Valentine Jones on 08/14/2008 17:51:49
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000