Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

Essential SQL Server Date, Time and DateTime Functions

Part I: Standard Date and Time Functions

I've posted some variations of these before, but here they all are in 1 easy package:  The essential date and time functions that every SQL Server database should have to ensure that you can easily manipulate dates and times without the need for any formatting considerations at all.

They are simple, easy, and brief and you should use them any time you need to incorporate any date literals or date math in your T-SQL code.  I have always wondered why T-SQL omits these basic functions, but the beauty of user defined functions is that we can create them ourselves.

create  function DateOnly(@DateTime DateTime)
– Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.
returns datetime
as
    begin
    return dateadd(dd,0, datediff(dd,0,@DateTime))
    end
go

create function Date(@Year int, @Month int, @Day int)
– returns a datetime value for the specified year, month and day
– Thank you to Michael Valentine Jones for this formula (see comments).
returns datetime
as
    begin
    return dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)
    end
go

create function Time(@Hour int, @Minute int, @Second int)
– Returns a datetime value for the specified time at the "base" date (1/1/1900)
– Many thanks to MVJ for providing this formula (see comments).
returns datetime
as
    begin
    return dateadd(ss,(@Hour*3600)+(@Minute*60)+@Second,0)
    end
go

create function TimeOnly(@DateTime DateTime)
– returns only the time portion of a DateTime, at the "base" date (1/1/1900)
– Thanks, Peso!
returns datetime
as
    begin
    return dateadd(day, -datediff(day, 0, @datetime), @datetime)
    end
go

create function DateTime(@Year int, @Month int, @Day int, @Hour int, @Minute int, @Second int)
– returns a dateTime value for the date and time specified.
returns datetime
as
    begin
    return dbo.Date(@Year,@Month,@Day) + dbo.Time(@Hour, @Minute,@Second)
    end
go

Remember that you must prefix UDFs with the owner (usually dbo) when calling them.

Usage Examples:

  •  where TransactionDate >= dbo.Date(2005,1,2)  – no formatting or implicit string conversions needed for date literals

  • select dbo.Date(year(getdate()), 1,1) returns the first day of the year for the current year.

  • select dbo.DateOnly(getdate()) returns only the date portion of the current day.

  • if dbo.TimeOnly(SomeDate) = dbo.Time(5,30,0)  – check to see if the time for a given date is at 5:30 AM

  • select dbo.Date(year(getdate()), month(getdate()),1) – returns the first day of the current month.

  • select dbo.Date(year(getdate()), month(getdate())+1,0) – returns the last day of the current month.

  • where SomeDate >= dbo.DateOnly(getdate()) and SomeDate < dbo.DateOnly(getDate())+1 – a simple way to get all transactions that occurred on the current date

  • select dbo.DateOnly(getdate()) + 1 + dbo.Time(14,30,0) – returns tomorrow at 2:30 PM.
and so on ….

Part II:  Introducing TimeSpans to SQL Server

With those functions in place, we can add two more that will give us further flexibility when working with dates and times: The concept of a "TimeSpan", very similar to what is available in the .NET framework.

create function TimeSpan(@Days int, @Hours int, @Minutes int, @Seconds int)
– returns a datetime the specified # of days/hours/minutes/seconds from the "base" date of 1/1/1900 (a "TimeSpan")
returns datetime
as
    begin
    return dbo.Time(@Hours,@Minutes,@Seconds) + @Days
    end

create function TimeSpanUnits(@Unit char(1), @TimeSpan datetime)
– returns the # of units specified in the TimeSpan.
– The Unit parameter can be: "d" = days, "h" = hours, "m" = minutes, "s" = seconds
returns int
as
    begin
    return case @Unit
        when 'd' then datediff(day, 0, @TimeSpan)
        when 'h' then datediff(hour, 0, @TimeSpan)
        when 'm' then datediff(minute, 0, @TimeSpan)
        when 's' then datediff(second, 0, @TimeSpan)
        else Null end
    end

Here, a TimeSpan is just a datetime offset from the "base" date of 1/1/1900.  Creating one is the same as creating a Time using the Time() function, but we have added a parameter for Days to give more flexibility.

The TimeSpanUnits() function works similar to standard T-SQL DatePart() function, but it returns the total # of units in the given time span.  So, if you create a time span of 1 day and 2 hours, then TimeSpanUnits("d") will return 1 and TimeSpanUnits("h") will return 26.  Negative values can be returned as well.  You also may wish to implement the TimeSpanUnits() function as multiple functions, one per unit (e.g., TimeSpanHours(), TimeSpanDays(), etc) depending on your preference.

Of course, a simple way to create a TimeSpan is to simply subtract two standard T-SQL DateTimes.  Also please note that we can add and subtract Dates, Times, and TimeSpans all together using standard + and - operators and everything will work as expected.  We can also add integers to our Dates and Times which will add entire days to the values.

Here's a TimeSpan usage example:

declare @Deadline datetime – remember, we still use standard datetimes for everything, include TimeSpans
set @Deadline = dbo.TimeSpan(2,0,0,0)   – the deadline is two days

declare @CreateDate datetime
declare @ResponseDate datetime

set @CreateDate = dbo.DateTime(2006,1,3,8,30,0)  – Jan 3, 2006, 8:30 AM
set @ResponseDate = getdate() – today

– See if the response date is past the deadline:
select case when @ResponseDate > @CreateDate + @Deadline then 'overdue.' else 'on time.' end as Result

– Find out how many total hours it took to respond:  

declare @TimeToRepond datetime
set @TimeToRespond = @ResponseDate - @CreateDate

select dbo.TimeSpanUnits('h', @TimeToRespond) as ResponseTotalHours

– Return the response time as # of days, # of hours, # of minutes:
select dbo.TimeSpanUnits('d',@TimeToRespond) as Days, DatePart(hour, @TimeToRespond) as Hours, DatePart(minute, @TimeToRespond) as Minutes

– Return two days and two hours from now:
select getdate() + dbo.TimeSpan(2,2,0,0)


Part III: Conclusion

We're not working with magic here, or creating new data types or concepts or incorporating the CLR into our database.  We are simply using the existing DateTime datatype to make our lives very easy and to implement efficient and simple date and time manipulation in T-SQL.

The fact that DateTimes are stored as standard numeric values and the fact that they can be added, subtracted and compared and that there is a standard "base" value (that evaluates to a numerical value of 0) means that we have all the power we need to do all kinds of things in T-SQL; we just need a few functions to help us out.

Final Note:  if you need the accuracy of milliseconds in your application, it is very easy to alter the above functions (or add new ones) that allow you to specify milliseconds in your times. 

(1/29/07 Update: Thank you to Fredrik for pointing out an issue with the Date() function when returning 3/1/2008 [see the comments] )

(2/13/07 Update: Once again, some great feedback has given us an even better Date() function – and a Time() function as well.  Thank you Michael !)

(11/15/08 Update: Thanks, Peso, for a nice and simple "TimeOnly" formula! [see the comments])

see also:

Legacy Comments


Jeff
2007-01-03
re: Essential SQL Server Date and Time Functions
Hi James -- I used to do it the same way, but then someone pointed this out to me, and since then I've religiously used the DateAdd(DateDiff()) method:

declare @datetime datetime
set @datetime = '1/1/2006 11:59:59 PM'
select cast(cast(@DateTime as integer) as DateTime)

returns 1/2/2006 ...

Jeff
2007-01-03
re: Essential SQL Server Date and Time Functions
Same with the Time2 function, FYI:

declare @datetime datetime
set @datetime = '1/1/2006 11:59:29 PM'

select cast(@DateTime - cast(@DateTime as Integer) as DateTime)

returns the time on 12/31/1899.


Jeff
2007-01-03
re: Essential SQL Server Date and Time Functions
Yeah, I had the same reaction when someone pointed that out to me, too. You also could subtract .5 from the DateTime before casting it, instead of using Floor(), but either way I prefer the method posted.

Chris Benard
2007-01-03
re: Essential SQL Server Date and Time Functions
Jeff, I've marked Bloglines to save this article. I HATE dealing with dates in SQL server. This will make life so much easier.

Of possible interest to you might be my latest blog post (just click my name, I linked right to the post). I wrote about how the aspnet_regsql (which I know isn't your team) creates stored procedures that don't work with SQL Server 2005 schemas and how to fix them. If you could pass that on to the correct team, I'd love it and I'm sure many would appreciate it.

And yes, I know there's a new way to do SQL cache dependencies in 2005, but the .Net 2.0 tool should at least support doing schemas on 2005. I've updated the Microsoft SP to support that. Please let me know what you think and if you'll be able to coherently explain that problem to the appropriate team.

Keep up the great work blogging. I wish you wrote more often.

Marc Brooks
2007-01-04
re: Essential SQL Server Date and Time Functions
More interesting SQL DATE stuff (in just T-SQL) here:

http://musingmarc.blogspot.com/2006/07/more-on-dates-and-sql.html

Fredrik Stax"ang
2007-01-09
re: Essential SQL Server Date and Time Functions
The function Date(@Year int, @Month int, @Day int) has a bug.

select dbo.Date(2008,3,1) -> 2008-02-29 00:00:00.000

dateadd(day, @Day-1, dateadd(month, @Month-1, dateadd(year, (@Year-1900),0)))

works better


Michael Valentine Jones
2007-02-04
re: Essential SQL Server Date, Time and DateTime Functions
In function Date you can shorten the formula from this:
dateadd(day, @Day-1, dateadd(month, @Month-1, dateadd(year, (@Year-1900),0)))
to this:
dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)

Test results that I posted on the link below indicate that it is also faster, probably because there is only one call to to the dateadd function.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66471



In function Time you can shorten the formula from this:
dateadd(hour, @Hour, 0) + dateadd(minute, @Minute,0) + dateAdd(second, @Second,0)
to this:
dateadd(ss,(@Hour*3600)+(@Minute*60)+@Second,0)



Jeff
2007-02-05
re: Essential SQL Server Date, Time and DateTime Functions
Michael -- I like it! Anything short and simple and fast is good for me. If you don't mind, I will update the functions with your suggested versions.

Michael Valentine Jones
2007-02-05
re: Essential SQL Server Date, Time and DateTime Functions
Fine with me.

Nate Suda
2007-02-14
re: Essential SQL Server Date, Time and DateTime Functions
Fantasitc.

Michael Valentine Jones
2007-02-15
re: Essential SQL Server Date, Time and DateTime Functions
I hate to be a pest after you modified the Date function code already, but Peter Larsson discovered that changing the formula from this:
dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)
to this:
dateadd(month,(12*@Year)-22801+@Month,@Day-1)
resulted in about a 3% to 5% reduction in runtime with inline code, and the code is slightly shorter.

You can see the discussion on this thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339



Rob
2007-02-21
re: Essential SQL Server Date, Time and DateTime Functions
When trying to create the functions under User Defined Functions I get an error stating that they already exist.

What im I doing wrong?

Thanks for the great functions. I wish i could make them work.

steveg
2007-03-08
re: Essential SQL Server Date, Time and DateTime Functions
Rob: Functions exist in your database with the same name -- either you'vfe run the script twice or someone else has created existing functions with the same name. If this is a production system Don't Touch It until you know what's going on (ask your local DBA).

Otherwise change "Create Function" to "Alter function".

Johnh
2007-03-21
re: Essential SQL Server Date, Time and DateTime Functions
Can anyone give me any suggestions with regards to calculating minutes between two events (@OriginalSentEmailTime and @FirstResponseEmailTime) where any time after 5pm or before 9am do not get counted in the total number of minutes? As well Saturdays and Sundays should not count towards the total number of minutes. Everything I have tried gets pretty ugly.

suresh
2007-03-28
re: Essential SQL Server Date, Time and DateTime Functions
Jeff,

Your datetime functions have helped me a lot while writing triggers in SQL SERVER. Thanx for your article.

Arjun
2007-03-29
re: Essential SQL Server Date, Time and DateTime Functions
thanks for the handy functions jeff! i've been convert(smalldatetime, convert(Varchar(10), @mydatetime, 101)) way too long and should have remembered to use a func!

Joris
2007-04-20
re: Essential SQL Server Date, Time and DateTime Functions
Yep, this page goes right to the Favorites menu... Thanks!

prasad
2007-04-25
re: Essential SQL Server Date, Time and DateTime Functions
Hi
I want only times to be added
wat i mean is i have two fields ontime(varchar) and duration(smallint )
where ontime holds 9:50 as data and duration holds 45 as data
now i need to add these two .

Can any one tell how it can be possible

Jeff
2007-04-25
re: Essential SQL Server Date, Time and DateTime Functions
prasad -- did you even read the article at all?

First, Do not use SmallInt and/or VARCHAR to store dates or times!!!!! Use proper datatypes. That would be DateTime.

Once you have done that, and both of those columns are DateTimes, then you simply ... add them together!!!

select onTime, Duration, onTime + Duration
from YourTable

That's it! See how using correct datatypes makes things easy ???

hey
2007-05-09
re: Essential SQL Server Date, Time and DateTime Functions
its really good stuff. thanks

katerina
2007-06-05
re: Essential SQL Server Date, Time and DateTime Functions
Hi,

These date information is very useful. Thank you. I'm however struggling to subtract one date from another to get number of days as result. For example
select.........,
case when calc_del_date > act_del_date then (calc_del_date - act_del_date)
when calc_del_date < act_del_date then (act_del_date - calc_del_date)
else ''
end as days_early_late

Can you help???

Jeff
2007-06-05
re: Essential SQL Server Date, Time and DateTime Functions
katerina -- I show exactly how to do this in the article -- did you read the part about TimeSpans and see the examples?

sandeep
2007-06-06
re: Essential SQL Server Date, Time and DateTime Functions
Hi,
I have to use SQL server 2005 as the backend db for reporting using cognos. One of my reports needs the minutes between 2 dates. Can you tell me the best way of doing it? do i create a function in the db or create a dynamic variable in reportnet. If i have to create a function , pls let me know how to create it?

Perry
2007-06-11
re: Essential SQL Server Date, Time and DateTime Functions
sandeep:

> If i have to create a function , pls let me know how to create it?

There is a great article for this -- see this article: http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

:)

Neerav
2007-06-19
re: Essential SQL Server Date, Time and DateTime Functions
Can someone suggest a way to get the midnight of the first day of current month ?

Jeff
2007-06-19
re: Essential SQL Server Date, Time and DateTime Functions
>> Can someone suggest a way to get the midnight of the first day of current month ?

The article explains with an example exactly how to do that.

Mark M
2007-06-19
re: Essential SQL Server Date, Time and DateTime Functions
What if you need to store dates older than 1753. I am working on a project dealing with historical data and this is a requirement.

Jarrod N
2007-08-03
re: Essential SQL Server Date, Time and DateTime Functions
Hi, Thanks very much for this, I was having a lot of trouble with a GROUP BY DateTime statement, where the records had been inserted milliseconds apart ... the DateOnly is just what I was after for GROUP BY'ing dates with differences of seconds between records.

ww
2007-08-15
re: Essential SQL Server Date, Time and DateTime Functions
thanks

Sanjay Kumar Dinda
2007-08-16
Is there any function like LastDate and FirstDate of Oracle
I am havig a problem at the time of working with date. I want function Last Date and First Date.

Jim
2007-08-17
re: Essential SQL Server Date, Time and DateTime Functions
These Date & Time functions helped me out in delivering my code on Date & Time

Many Thanks!!

dotnetguts
2007-08-19
re: Essential SQL Server Date, Time and DateTime Functions
Good Information

DotNetGuts
http://dotnetguts.blogspot.com

santosh
2007-09-13
re: Essential SQL Server Date, Time and DateTime Functions
declare @datetime datetime
set @datetime = '1/1/2006 12:59:00 '
select cast(convert(varchar(10),@DateTime,101 ) as datetime)


swati
2007-09-22
re: Essential SQL Server Date, Time and DateTime Functions
i would like to know how to get the data in employee table who are hired in the month of janauray irrespective of the year in which they are hired

arun
2007-09-27
re: Essential SQL Server Date, Time and DateTime Functions
I am also screwed up with the date time format .. first time using this on MSSQL for writting TSQL stored procedures....

Its a pure rubbish...

Arun

Shishir Gupta
2007-09-27
re: Essential SQL Server Date, Time and DateTime Functions
The date and time format is an headache. It has become pain in my neck.

Shishir Gupta

IT Head
http://www.naukrisalah.com

Vincent
2007-10-01
re: Essential SQL Server Date, Time and DateTime Functions
Hi, i want to know how do i fire a trigger at 2359 daily and i can transfer my current data into archive.

dev lead
2007-10-01
re: Essential SQL Server Date, Time and DateTime Functions
Thank you sooooo much. Your efforts here have saved me a lot of time. Thanks for everyone's input and keep up the good work!!

saurabh
2007-10-23
re: Essential SQL Server Date, Time and DateTime Functions
Hi Sir,

I have a specified date like(22/01/2010) and i want a date of the day(like: sunday or monday) just before 22/01/22. so is there any function like this please help.

thanks :
Saurabh

Solomon Rutzky
2007-10-24
re: Essential SQL Server Date, Time and DateTime Functions
Hello. If it is not inappropriate for me to state here, I have a small project that addresses some of these DateTime issues, including TimeSpan output. The project is called SQL# (http://www.SQLsharp.com/) and is a .Net CLR based library of Stored Procedures and User-Defined Functions to address functionality not inherently in T-SQL. Currently, the DateTime related functions are:

BusinessDays
DaysInMonth
DaysLeftInYear
FormatTimeSpan
FirstDayOfMonth
FullDateString
FullTimeString
IsBusinessDay
IsLeapYear
LastDayOfMonth

The "FormatTimeSpan" function allows you to pass in two DATETIME fields and a text format string that will then output the timespan in the format you specified as an NVARCHAR.

The manual has full examples of all of these.

Subbu
2007-10-29
re: Essential SQL Server Date, Time and DateTime Functions
Hello Jeff/other techies,
I have a nice problem before you, I need to find out the peak users logged in to the system

suppose 9:23 to 10:23 there are 800 users logged in
9:40 to 10:40 there are 900 users logged in,
11:33 to 12:33 there are 978 users logged in

In Short I want to group by <date conditionhere> having count(*) >900 (peak users)

select convert(varchar,min(logged_in),105),COUNT(*)
from tbl_UM_login r
where
convert(varchar,logged_in,105) < convert(varchar, getDate(), 105)
AND convert(varchar,logged_in,105) > convert(varchar, dateadd(dd,-7,getDate()), 105)
GROUP BY DATEDIFF(dd,getDate(),logged_in)
HAVING COUNT(*) > @peakUsers

Shabeer M
2007-11-14
I need All months name in SQL with songle query
Please I need result

jan
feb
mar
apr
may like this


Thank You

Fred
2007-11-15
re: Essential SQL Server Date, Time and DateTime Functions
Hello all -
I am pretty new to this and similarly new to MS Sql so please bear with me. I have been following this thresd and others and am trying to find a solution to a similar problem.

I have two dates in a table, a received date and a resolved date. I am tasked with reporting on the time interval between the received date and the resolved date. the results should be in minutes. Any help will be appreciated.

Here is what I have but non of these appear to be acurate. What am I doing wrong or what is the best approach

select
im_issue_no,
im_received_date,
avg(cast(im_resolved_date as integer) - cast(im_Received_date as integer)) as AVG,
avg((CONVERT (FLOAT, DATEPART(hh, im_resolved_date) * 60 + DATEPART(n, im_resolved_date)) / 60) - (CONVERT (FLOAT, DATEPART(hh, im_Received_date) * 60 + DATEPART(n, im_Received_date)) / 60)) as AVG_2,
datediff(minute, im_received_date, im_resolved_date) as Minutes,
datediff(Hour, im_received_date, im_resolved_date)as Hours,
im_resolved_date
from
issues
where
im_issue_no = 24482
group by
im_issue_no,im_received_date,im_resolved_date

Jeff
2007-11-15
re: Essential SQL Server Date, Time and DateTime Functions
Fred -- Without sample data, expected results, or more specifics, I have no idea what specifically you are looking for. The worst thing to do when asking for help is to present code that doesn't work and then ask for someone to fix it without providing clear, precise specifications and sample input and expected output. I recommend visiting the SQLTeam forums for more help, and be a lot more detailed there and you'll get the help you need.

Also, this article may be helpful to you:

http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server

Prasanna
2007-11-16
re: Essential SQL Server Date, Time and DateTime Functions
i have 2 datetime columns startdatetime and enddatetime and some event varchar column.
now my question is how to insert into these columns datetime in format or style i want to use while convert and insert..

something of this type.. but this is not working

insert into mydatetimeEvents
select convert(commencedatetime, 'Jan 01 2007 09:00AM',100),
convert(enddatetime, 'Jan 01 2007 09:00PM',100),'Anniversaries')

mydatetimeEvents-tablename
commencedatetime,enddatetime-column name-datatype-datetime
style-100
aniversaries-some other column of dattype varchar

Jeff
2007-11-16
re: Essential SQL Server Date, Time and DateTime Functions
Prasanna -- you should really consider checking Books Online or reading any other SQL Server resource (books, google, MSDN) to find out how Convert() works and what it expects for arguments. And you should ask questions like this in programming forums, such as SQLTeam, not in blog comments.

sally
2007-12-04
re: Essential SQL Server Date, Time and DateTime Functions
How to get only date with format dd/MM/yyyy.......?

My client want all date with format like that. I already user DateOnly like below:

BEGIN function [dbo].[DateOnly](@DateTime DateTime)
-- Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.
returns datetime
as
begin
return dateadd(dd,0, datediff(dd,0,@DateTime))
end

but it return with datetime like this '2006-04-17 00:00:00.000'

Jeff Smith
2007-12-04
re: Essential SQL Server Date, Time and DateTime Functions
Sally that is a formatting question, it has nothing to do with T-SQL. Format your data at your presentation layer, never at your database layer.

see: http://weblogs.sqlteam.com/jeffs/archive/2007/08/29/SQL-Dates-and-Times.aspx

Dean
2007-12-26
re: Essential SQL Server Date, Time and DateTime Functions
Sally, For display purposed only, you could do this...

function [dbo].[DisplayDate](@DateTime DateTime)
returns varchar(10)
as
begin
return cast(month(@DateTime) as varchar) + '/' +
cast(day(@DateTime) as varchar) + '/' +
cast(year(@DateTime) as varchar)
end

Jeff
2007-12-26
re: Essential SQL Server Date, Time and DateTime Functions
Dean -- great example of why you should not try to format in T-SQL. It no longer returns a date that can be compared, sorted, or used in any date functions; it doesn't actually follow the format specification she asked for, and it is longer, more complicated and less efficient than simply returning the date value and letting the client format it.

Why is this so hard to people to grasp? Why do people insist on making simple things more complicated?

Qingsong Yao
2008-08-01
re: Essential SQL Server Date, Time and DateTime Functions
Hello, Dean
Great Example. Given that SQL Server 2008 already support date and time types, we might just them directly. Also, please check my blog, I have a BIGINT implemenation of timespan.

Brian Fuller
2008-08-14
re: Essential SQL Server Date, Time and DateTime Functions
I am struggling with date calculations. For example
SET @D1 = '20081031'
SET @D1 = Dateadd (mm, 1, @D1)
SET @D1 = Dateadd (mm, -1, @D1)
PRINT @D1
gives an answer 0f '20081030'
I can see why it happens, but the inconsistency is giving me hassles !!



Eralper
2008-09-05
re: Essential SQL Server Date, Time and DateTime Functions
Hello Jeff,

That is a very nice work to bring up together a set of useful functions for the developer community.
MS SQL2008 will introduce a new set of functions especially for the date and time issues.
Great.

javierrockstar
2008-09-09
re: Essential SQL Server Date, Time and DateTime Functions
what a cool job you did here Jeff, it overpassed the expectatives i had when i used google to search something like this.

I'm using it in a personal, private code i'm working on if you don't mind, the date-time functions you have i mean.

Later!!

Hi Jeff
2008-09-15
re: Essential SQL Server Date, Time and DateTime Functions
Great work, I used u'r function Date to get end of previous month and start of previous month.

Thanks !!

Kavitha
2008-09-17
re: Essential SQL Server Date, Time and DateTime Functions
:-)

John
2008-10-06
re: Essential SQL Server Date, Time and DateTime Functions
Some useful functions here Jeff, thanks!

I had a request when running reports to be able to compare a date with the same date last year.
Using DateDiff(year, -1, DateInQuestion) wasn't what the client wanted, they wanted the comparison to be against the same week day of the previous year i.e. Friday 4th July 2008 needed to compare sales for Friday 6th July 2007.

I wrote the following function which *seems* to result in what I wanted and thought this may be a useful addition to your own.

Create Function dbo.GetLastYearsDate
(
@Date DateTime
)
Returns DateTime
As
Begin
Declare @Dates Table
(
Date DateTime
)

Declare @GetLastYearsDate DateTime,
@StartDate DateTime,
@EndDate DateTime

-- Get a selection of dates from last year
Select @StartDate = DateAdd(year, -1, DateAdd(dd, DateDiff(dd, 0, @Date), -7)),
@EndDate = DateAdd(year, -1, DateAdd(dd, DateDiff(dd, 0, @Date), +7))

-- Insert the range
While (@StartDate < @EndDate)
Begin
Insert Into @Dates Values (@StartDate)
Select @StartDate = DateAdd(day, +1, @StartDate)
End

-- Match last years on date parts from this year
Select @GetLastYearsDate = Date
From @Dates
Where (
DatePart(wk, @Date) = DatePart(wk, Date)
Or
Case
When DatePart(wk, @Date) = 53 Then 1
Else DatePart(wk, @Date) + 1
End = DatePart(wk, Date)
)
And DatePart(dw, @Date) = DatePart(dw, Date)

Return @GetLastYearsDate
End
Go

Select dbo.GetLastYearsDate('2008-07-04') -- Results in 2007-07-06 00:00:00.000

brendans
2008-10-20
re: Essential SQL Server Date, Time and DateTime Functions
http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

sushmita patil
2008-10-22
re: Essential SQL Server Date, Time and DateTime Functions
I want combine all integer in date formate in one column

i.e Cobine date,month and year in following format
'7/6/2005'.

How to do this?
Please give me answer?
Help

Peso
2008-11-11
re: Essential SQL Server Date, Time and DateTime Functions
TimeOnly can also be written like this

dateadd(day, -datediff(day, 0, @datetime), @datetime)

jeff
2008-11-11
re: Essential SQL Server Date, Time and DateTime Functions
I like that, Peso. Probably more efficient since it doesn't need to call another UDF. I'll test a little just to be sure later on and I'll probably update the function with that change. (Giving you credit, of course!)

thanks!

Peso
2008-11-11
re: Essential SQL Server Date, Time and DateTime Functions
A little more simplified (no minus) but not so intuitive

dateadd(day, datediff(day, @datetime, 0), @datetime)

barani
2008-11-25
re: Essential SQL Server Date, Time and DateTime Functions
hi
i need to get the day name like monday, tuesday of the current day. any query on this? please help me.

mehdi seydali
2008-12-12
re: Essential SQL Server Date, Time and DateTime Functions
hi

Phil
2008-12-16
re: Essential SQL Server Date, Time and DateTime Functions
Date passed to stored procedure is GMT so am using select @InputDtTm = DATEADD(hour,-6,@InputDtTm) to reset to CST. This will work until daylight savings time when need to subtract 5 hours. Is there anything that can be used to determine whether CST or CDT so do not have to change code twice a year?

Amit
2009-01-14
re: Essential SQL Server Date, Time and DateTime Functions
thanks for this set of functions ... it helped me a lot

Michael
2009-01-14
re: Essential SQL Server Date, Time and DateTime Functions
Hello i need to extract date from datetime column
how can i do that?
thanks.

Héctor
2009-01-15
re: Essential SQL Server Date, Time and DateTime Functions
This is another way of finding the same weekday on the same week of previous year to a given date without the need for UDF nor variables:

select dateadd(day,
(datepart(week, getdate()) * 7 + datepart(weekday, getdate()))
- (datepart(week, dateadd(year, -1, getdate())) * 7 + datepart(weekday, dateadd(year, -1, getdate()))),
dateadd(year, -1, getdate()))

Basically, you add the number of days required to move the previous year's date to the same week and weekday as the original date.

I used getdate() to represent the original date. You must change it as needed.

Regina Jensen
2009-01-19
re: Essential SQL Server Date, Time and DateTime Functions
Thanks, this is helpful!

Waseem
2009-02-06
re: Essential SQL Server Date, Time and DateTime Functions
Is it possible that I can store date/time in "datetime" column like this...

"06/Feb/2009 11:39:25"

Do I have to use something in "formula" or "Default Value" field?

Please do help me!

Cyril
2009-02-07
re: Essential SQL Server Date, Time and DateTime Functions
Great write-up! I have used this in one of my projects. Thank you so much!

salbal
2009-02-09
re: Essential SQL Server Date, Time and DateTime Functions
It is very useful

kamal verma
2009-03-27
re: Essential SQL Server Date, Time and DateTime Functions
swati you can use this statement

select to_char(dob,'month,dd'),count(plno) from employees
where emp_status like 'W%' and
to_char(dob,'month') like 'july%' and
to_char(dob, 'dd' ) like '01'
group by dob;

matro
2009-05-10
re: Essential SQL Server Date, Time and DateTime Functions
very useful for me. thank you and respect from Rome, Italy.

Adam9000
2009-05-21
re: Essential SQL Server Date, Time and DateTime Functions
Veeeeeeeeeeeeeeeeeeery usefull.

THX SO MUCH!

FreeZeBox
2009-06-29
re: Essential SQL Server Date, Time and DateTime Functions
yeah.. very useful indeed..

Sneha
2009-08-06
re: Essential SQL Server Date, Time and DateTime Functions
Great Job! It was very very helpful !!!

ME
2009-08-18
re: Essential SQL Server Date, Time and DateTime Functions
This is how to get the date with out having to worry about the time for comparison as a datetime.
Convert(datetime, Convert(VARCHAR, getdate(), 101) , 101)
Quick easy, no craziness....


Anders And
2009-08-25
re: Essential SQL Server Date, Time and DateTime Functions
The first function fails if the time is 23:59:59.999.

DateOnly('2009-08-25 23:59:59.999') = 2009-08-26

How do I fix it?

Tony
2009-09-15
re: Essential SQL Server Date, Time and DateTime Functions
I have seen plenty of documentation on how to convert the date format to & from the Hundred Years Format. But nothing regarding the time. For example, 40065: 9/10/2009 -/+ 1 in Sql Server & Excel. However, 2402 doesn't indicate military time. Does anyone have a suggestion?

Zachary Jensen
2009-11-11
Determinism and Essential SQL Server Date, Time and DateTime Functions
First and foremost, thank you Jeff, for all of these functions. They've been a great help!

I have an issue with them and determinism which I don't quite grasp. I implemented your date creation function in SQL Server 2005 as follows:

ALTER FUNCTION [BusinessCalcs].[fnDate]
(
@Year INT
,@Month INT
,@Day INT
)
RETURNS DATETIME
AS
BEGIN
-- From http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx
RETURN DATEADD(MONTH, ((@Year - 1900) * 12) + @Month - 1, @Day - 1)
END

Some time later, I added this function to a calculated field on a table, with the following expression:

BusinessCalcs.fnDate(ReportYear, ReportMonth, 1)

Where ReportYear and ReportMonth are both columns from the same table of type 'int'. When I try to add an index over the new date column, sql server complains that the computed column isn't deterministic.

It seems to me that the function is deterministic--DATEADD is a deterministic function* and everything else is addition and subtraction. What am I missing here?

- Zak

* http://msdn.microsoft.com/en-us/library/ms178091(SQL.90).aspx

sumit
2009-12-17
re: Essential SQL Server Date, Time and DateTime Functions
IF U WANT TIME IN AM PM FORMAT USE :-

select substring(CONVERT(varchar, GetDate()),12,8)

Imran habiba
2009-12-23
re: Essential SQL Server Date, Time and DateTime Functions
Hi

can I give > or < criteria in datediff syntex like if I want result as date diffrance in day bu i need only daye wich are >365.

Dave Conner
2010-01-06
Another Way Essential SQL Server Date, Time and DateTime Functions
Nice article.

When I ran the first Dateonly function it returned unaltered datetime value. For yet another variation simply return a value cast as DATE , the dateADD, DateDIFF functions are not really necessary.

create function DateOnly(@DateTime DateTime)
-- Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.
returns date
as
begin
return @DateTime
end
go

Testing this over 10000 iterations just saves 100 milliseconds or so (dateadd and datediff are highly optimized) but this version has the advantage of clarity for any developer looking at the code.

Jeff
2010-01-06
re: Essential SQL Server Date, Time and DateTime Functions
Thanks, Dave. These functions written long before SQL 2008 introduced the "Date" data type, and were necessary at the time and are still necessary for any SQL Server version before 2008.

Neelesh
2010-01-18
SQL Datetime format
Hi,

I have a problem in setting MS sql sever datetime format from mm/dd/yy to dd/mm/yy so that what ever the data comes inside and go out side is of the format and also displayed on web forms in this format only. By default the sql server is taking US format mm/dd/yy can any one helps me out to configure the settings.

I can set it temporary using 'SET DATEFORMAT' in particular session but I want to change it permanently. Where I can set permanently SQL date format settings.

I am using SQL 2005.

Thanks,
Neelesh

Lakshmanan from INDIA
2010-02-04
re: Essential SQL Server Date, Time and DateTime Functions
Date format in SQL Server 2005
Select getdate() -- 2010-02-05 10:03:44.527

-- To get all date format
select CONVERT(VARCHAR(12),getdate(),100) +' '+ 'Date -100- MMM DD YYYY' -- Feb 5 2010
union
select CONVERT(VARCHAR(10),getdate(),101) +' '+ 'Date -101- MM/DDYYYY'
Union
select CONVERT(VARCHAR(10),getdate(),102) +' '+ 'Date -102- YYYY.MM.DD'
Union
select CONVERT(VARCHAR(10),getdate(),103) +' '+ 'Date -103- DD/MM/YYYY'
Union
select CONVERT(VARCHAR(10),getdate(),104) +' '+ 'Date -104- DD.MM.YYYY'
Union
select CONVERT(VARCHAR(10),getdate(),105) +' '+ 'Date -105- DD-MM-YYYY'
Union
select CONVERT(VARCHAR(11),getdate(),106) +' '+ 'Date -106- DD MMM YYYY' --ex: 03 Jan 2007
Union
select CONVERT(VARCHAR(12),getdate(),107) +' '+ 'Date -107- MMM DD,YYYY' --ex: Jan 03, 2007
union
select CONVERT(VARCHAR(12),getdate(),109) +' '+ 'Date -108- MMM DD YYYY' -- Feb 5 2010
union
select CONVERT(VARCHAR(12),getdate(),110) +' '+ 'Date -110- MM-DD-YYYY' --02-05-2010
union
select CONVERT(VARCHAR(10),getdate(),111) +' '+ 'Date -111- YYYY/MM/DD'
union
select CONVERT(VARCHAR(12),getdate(),112) +' '+ 'Date -112- YYYYMMDD' -- 20100205
union
select CONVERT(VARCHAR(12),getdate(),113) +' '+ 'Date -113- DD MMM YYYY' -- 05 Feb 2010


SELECT convert(varchar, getdate(), 20) -- 2010-02-05 10:25:14
SELECT convert(varchar, getdate(), 23) -- 2010-02-05
SELECT convert(varchar, getdate(), 24) -- 10:24:20
SELECT convert(varchar, getdate(), 25) -- 2010-02-05 10:24:34.913
SELECT convert(varchar, getdate(), 21) -- 2010-02-05 10:25:02.990


---==================================
-- To get the time
select CONVERT(VARCHAR(12),getdate(),108) +' '+ 'Date -108- HH:MM:SS' -- 10:05:53

select CONVERT(VARCHAR(12),getdate(),114) +' '+ 'Date -114- HH:MM:SS:MS' -- 10:09:46:223
SELECT convert(varchar, getdate(), 22) -- 02/05/10 10:23:11 AM
----=============================================
SELECT getdate()+1
SELECT month(getdate())+1
SELECT year(getdate())+1

Shiva
2010-03-02
re: Essential SQL Server Date, Time and DateTime Functions
I want the time in this format " hh:mm:sec" from sql server date commands or you want any command from sqlserver.
how it is printed only the time. please send reply as early as possible

Aiza
2010-06-30
re: Essential SQL Server Date, Time and DateTime Functions
have a task to determine the age of a file based from the date uploaded field subtracted to the current date. I wonder how can I do this in a stored procedure and how. Your immediate reply means a lot. Thanks in advance.

Thomas
2010-08-05
re: Essential SQL Server Date, Time and DateTime Functions
Vielen Dank. Die Funktion DateOnly habe ich lange gesucht.

hilongos
2010-08-19
re: Essential SQL Server Date, Time and DateTime Functions
is this possible to posgre?

College Essays
2010-08-19
re: Essential SQL Server Date, Time and DateTime Functions
@Hilongos I think yes, for the query syntax has no difference.