Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

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:

Print | posted on Tuesday, January 02, 2007 11:38 AM | Filed Under [ T-SQL Code Library - SQL DateTime Data ]

Feedback

Gravatar

# re: Essential SQL Server Date and Time Functions

The speed differences seem too small to measure, but here are my versions of some of those:
Create function [dbo].[DateOnly2](@DateTime DateTime)
-- Strips out the time portion of any dateTime value.
returns datetime
as
begin
return cast(cast(@DateTime as integer) as DateTime)
end

CREATE function [dbo].[TimeOnly2](@DateTime DateTime)
-- returns the only the time portion of a date, at the "base" date (1/1/1900)
returns datetime
as
begin
return cast(@DateTime - cast(@DateTime as Integer) as DateTime)
end

Plus one I didn't see you cover:

CREATE FUNCTION [dbo].[Epoch] ()
-- Returns beginning of time as DateTime
RETURNS DateTime
AS
BEGIN
RETURN cast(0 as DateTime)
END
1/3/2007 11:22 AM | James Curran
Gravatar

# 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 ...
1/3/2007 11:30 AM | Jeff
Gravatar

# 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.

1/3/2007 11:32 AM | Jeff
Gravatar

# re: Essential SQL Server Date and Time Functions

Yikkes! You're right! In fact, it fails for any PM time. It seems that CAST rounds to the nearest integer rather than truncating to the lower (as cast does most other places).

select cast(FLOOR(cast(@DateTime as float)) as datetime)

does work, but that's getting a bit too convoluted.
1/3/2007 1:08 PM | James Curran
Gravatar

# 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.
1/3/2007 3:27 PM | Jeff
Gravatar

# Essential SQL Server Date and Time Functions

1/3/2007 5:34 PM | Dontchaknow
Gravatar

# 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.
1/3/2007 8:36 PM | Chris Benard
Gravatar

# 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
1/4/2007 2:34 AM | Marc Brooks
Gravatar

# 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

1/9/2007 9:44 AM | Fredrik Stax"ang
Gravatar

# 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)


2/4/2007 3:15 AM | Michael Valentine Jones
Gravatar

# 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.
2/5/2007 10:29 AM | Jeff
Gravatar

# re: Essential SQL Server Date, Time and DateTime Functions

Fine with me.
2/5/2007 4:16 PM | Michael Valentine Jones
Gravatar

# re: Essential SQL Server Date, Time and DateTime Functions

Fantasitc.
2/14/2007 9:44 AM | Nate Suda
Gravatar

# 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


2/15/2007 8:18 PM | Michael Valentine Jones
Gravatar

# 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.
2/21/2007 2:09 PM | Rob
Gravatar

# 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".
3/8/2007 6:42 PM | steveg
Gravatar

# 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.
3/21/2007 6:09 PM | Johnh
Gravatar

# 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.
3/28/2007 5:07 AM | suresh
Gravatar

# 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!
3/29/2007 12:10 AM | Arjun
Gravatar

# re: Essential SQL Server Date, Time and DateTime Functions

Yep, this page goes right to the Favorites menu... Thanks!
4/20/2007 5:11 AM | Joris
Gravatar

# 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
4/25/2007 2:55 AM | prasad
Gravatar

# 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 ???
4/25/2007 8:50 AM | Jeff
Gravatar

# re: Essential SQL Server Date, Time and DateTime Functions

its really good stuff. thanks
5/9/2007 6:17 PM | hey
Gravatar

# 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???
6/5/2007 11:14 AM | katerina
Gravatar

# 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?
6/5/2007 11:18 AM | Jeff
Gravatar

# 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?
6/6/2007 1:42 AM | sandeep
Gravatar

# 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

:)
6/11/2007 3:10 PM | Perry
Gravatar

# 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 ?
6/19/2007 3:36 PM | Neerav
Gravatar

# 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.
6/19/2007 3:55 PM | Jeff
Gravatar

# 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.
6/19/2007 11:15 PM | Mark M
Gravatar

# 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.
8/3/2007 12:54 AM | Jarrod N
Gravatar

# re: Essential SQL Server Date, Time and DateTime Functions

thanks
8/15/2007 4:23 AM | ww
Gravatar

# 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.
8/16/2007 2:30 AM | Sanjay Kumar Dinda
Gravatar

# 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!!
8/17/2007 3:47 AM | Jim
Gravatar

# re: Essential SQL Server Date, Time and DateTime Functions

Good Information

DotNetGuts
http://dotnetguts.blogspot.com
8/19/2007 1:43 AM | dotnetguts
Gravatar

# 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)

9/13/2007 1:46 AM | santosh
Gravatar

# 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
9/22/2007 1:12 AM | swati
Gravatar

# 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
9/27/2007 5:17 AM | arun
Gravatar

# 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
9/27/2007 6:31 AM | Shishir Gupta
Gravatar

# 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.
10/1/2007 11:46 PM | Vincent
Gravatar

# 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!!
10/1/2007 11:56 PM | dev lead
Gravatar

# 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
10/23/2007 3:05 AM | saurabh
Gravatar

# 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.
10/24/2007 11:16 AM | Solomon Rutzky
Gravatar

# 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
10/29/2007 1:01 PM | Subbu
Gravatar

# Mr.

Question: I have a dates in this format: 10/11/2007 6:33:45 PM

However I need to run a sql query to group by date (without the time) eg

SELECT TOP (100) PERCENT OrderDate, SUM(OrderTotal) AS DateTotal
FROM dbo.tbl_Order
GROUP BY OrderDate
ORDER BY OrderDate

How do I do this?
Right now it groups the dates and time. thanks for any tips.
11/10/2007 4:27 AM | Aron
Gravatar

# I need All months name in SQL with songle query

Please I need result

jan
feb
mar
apr
may like this


Thank You
11/14/2007 1:01 PM | Shabeer M
Gravatar

# 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
11/15/2007 4:35 PM | Fred
Gravatar

# 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
11/15/2007 4:39 PM | Jeff
Gravatar

# 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
11/16/2007 5:43 AM | Prasanna
Gravatar

# 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.
11/16/2007 10:46 AM | Jeff
Gravatar

# 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'
12/4/2007 4:29 AM | sally
Gravatar

# 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
12/4/2007 4:42 PM | Jeff Smith
Gravatar

# 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
12/26/2007 3:44 PM | Dean
Gravatar

# 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?
12/26/2007 5:01 PM | Jeff
Gravatar

# 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.
8/1/2008 7:27 PM | Qingsong Yao
Gravatar

# 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 !!


8/14/2008 10:58 AM | Brian Fuller
Gravatar

# 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.
9/5/2008 2:16 AM | Eralper
Gravatar

# 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!!
9/9/2008 8:31 PM | javierrockstar
Gravatar

# 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 !!
9/15/2008 4:31 PM | Hi Jeff
Gravatar

# re: Essential SQL Server Date, Time and DateTime Functions

:-)
9/17/2008 3:03 AM | Kavitha
Gravatar

# 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
10/6/2008 9:50 AM | John
Gravatar

# re: Essential SQL Server Date, Time and DateTime Functions

http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx
10/20/2008 2:45 PM | brendans
Gravatar

# 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
10/22/2008 6:08 AM | sushmita patil
Gravatar

# re: Essential SQL Server Date, Time and DateTime Functions

Does this help?

http://www.izenda.com/Site/KB/CodeSamples/Combining-Multiple-Event-Based-Fields-with-UNION
10/22/2008 12:58 PM | anon
Gravatar

# re: Essential SQL Server Date, Time and DateTime Functions

TimeOnly can also be written like this

dateadd(day, -datediff(day, 0, @datetime), @datetime)
11/11/2008 2:10 PM | Peso
Gravatar

# 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!
11/11/2008 2:37 PM | jeff
Gravatar

# 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)
11/11/2008 4:09 PM | Peso
Gravatar

# 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.
11/25/2008 1:35 AM | barani
Gravatar

# re: Essential SQL Server Date, Time and DateTime Functions

hi
12/12/2008 11:52 PM | mehdi seydali
Gravatar

# 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?
12/16/2008 2:03 PM | Phil
Gravatar

# re: Essential SQL Server Date, Time and DateTime Functions

thanks for this set of functions ... it helped me a lot
1/14/2009 10:37 AM | Amit
Gravatar

# re: Essential SQL Server Date, Time and DateTime Functions

Hello i need to extract date from datetime column
how can i do that?
thanks.
1/14/2009 11:18 AM | Michael
Gravatar

# 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.
1/15/2009 10:27 AM | Héctor
Gravatar

# re: Essential SQL Server Date, Time and DateTime Functions

Thanks, this is helpful!
1/19/2009 1:25 PM | Regina Jensen
Gravatar

# 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!
2/6/2009 1:51 AM | Waseem
Gravatar

# 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!
2/7/2009 8:19 AM | Cyril
Gravatar

# re: Essential SQL Server Date, Time and DateTime Functions

It is very useful
2/9/2009 1:54 AM | salbal
Gravatar

# 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;
3/27/2009 1:13 AM | kamal verma
Gravatar

# re: Essential SQL Server Date, Time and DateTime Functions

very useful for me. thank you and respect from Rome, Italy.
5/10/2009 5:01 AM | matro
Gravatar

# re: Essential SQL Server Date, Time and DateTime Functions

Veeeeeeeeeeeeeeeeeeery usefull.

THX SO MUCH!
5/21/2009 11:00 AM | Adam9000
Gravatar

# re: Essential SQL Server Date, Time and DateTime Functions

yeah.. very useful indeed..
6/29/2009 9:32 AM | FreeZeBox
Gravatar

# Determine the record "covering" a specified date

Thanks a lot!

One complementary question: Does anyone have available a function to to determine the record in a table with a date field, that "covers" a specified date? An example: I have a table as indicated below and want to select the record which covers the timespan beginning with the date value of the sought records and limited by the date value of the following date. Note: This following date (in timely order) must not necessarily be placed in the logical order of the table's ID values.

Sample Table records:
ID date Text
-----------------------------
1 2009-05-01 MyDate1
2 2001-04-13 MyDate3
3 1998-12-10 MyDate4
4 2008-07-05 MyDate2

The searched record for the date e.g. 1998-12-13 is obviously record no. 3. But how to find it the shortest possible way?

Many thanks in advance for any suggestion.
Wolfgang, Berlin/Germany
7/16/2009 4:51 AM | Wolfgang Sohst
Gravatar

# re: Essential SQL Server Date, Time and DateTime Functions

Great Job! It was very very helpful !!!
8/6/2009 2:39 PM | Sneha
Gravatar

# 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....

8/18/2009 6:48 PM | ME
Gravatar

# 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?
8/25/2009 5:03 AM | Anders And
Gravatar

# 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?
9/15/2009 9:05 PM | Tony
Gravatar

# 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
11/11/2009 2:44 PM | Zachary Jensen
Gravatar

# 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)
12/17/2009 6:15 AM | sumit
Gravatar

# 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.
12/23/2009 5:01 AM | Imran habiba
Gravatar

# 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.
1/6/2010 10:01 AM | Dave Conner
Gravatar

# 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.
1/6/2010 10:22 AM | Jeff
Gravatar

# 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
1/18/2010 7:08 AM | Neelesh
Gravatar

# 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
2/4/2010 9:46 PM | Lakshmanan from INDIA
Gravatar

# 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
3/2/2010 2:20 AM | Shiva
Gravatar

# 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.
6/30/2010 9:43 AM | Aiza
Gravatar

# re: Essential SQL Server Date, Time and DateTime Functions

Vielen Dank. Die Funktion DateOnly habe ich lange gesucht.
8/5/2010 6:32 AM | Thomas
Gravatar

# re: Essential SQL Server Date, Time and DateTime Functions

is this possible to posgre?
8/19/2010 1:06 AM | hilongos
Gravatar

# re: Essential SQL Server Date, Time and DateTime Functions

@Hilongos I think yes, for the query syntax has no difference.
8/19/2010 1:11 AM | College Essays
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET