Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert – just use DATETIME

Wed Aug 29, 2007 by Jeff Smith in t-sql, efficiency, database-design, datetime-data

The Importance of Data Types

Imagine that SQL Server only provided two data types: the MONEY data type to store numeric values, and VARCHAR to store text.

If you are designing a database in this scenario and you need to store or return integer values, which data type – MONEY or VARCHAR – would you use?

Suppose I were to argue that MONEY is too complicated, and a waste of space, since all MONEY values always have 4 digits after the decimal which we don't need and it might get confusing. Therefore, we should use VARCHAR, which is much simpler. After all, if we use MONEY, every time we execute a SELECT, our integer data will be returned with 4 decimal places like "45.0000", which is certainly not what we want and doesn’t look like an integer; but if we use VARCHAR we will get "45" which looks much better and is really what we want to see. While eventually we will notice that we cannot add these VARCHAR numbers together, or sort them, or compare them, or do any kind of math with them, we can always temporarily convert those values to MONEY if we need to do these things, right? And as for ensuring that we only store valid numbers in our VARCHAR columns, we can handle that with some complicated CHECK expressions that parses the string to ensure that it stores only valid digits. We also need to make sure that everyone is aware of the rules for the format of integer data in our VARCHAR columns, to avoid problems as well: after all, we may wish to store values like 34934 as "34,942" so that it looks just right when the values are returned and is easier to work with.

Now, if I make this argument to you, what would you say? Would you think it's a good idea?

I think we can all agree that a much better approach would be to use MONEY. Now we can add, sort, compare, do math, and all kinds of things on our values which are stored as numeric data, not text. We can add a quick, simple CHECK constraint to ensure that our MONEY values have nothing stored after the decimal (e.g., CHECK round(value,0) = value), and we simply let our front end format those numbers without any decimal places. In this scenario, it's probably fair to say that no reasonable programmer would argue that VARCHARs are better for working with integers than a true, numeric data type like MONEY.

Manipulate and Return Data – not Strings

If we agree to use the MONEY data type, wouldn't it make sense to keep those values as MONEY throughout our T-SQL code, and only return MONEY values to the clients? We wouldn't want to try to convert our MONEY values to VARCHAR in our database code just so that things "look good" and we "don't see" that pesky little ".0000" after each value, right? It means nothing to us, it doesn’t affect our data, so we simply store and work with and return MONEY in all of our database code and we don't worry about formatting or how things "look" or the extra decimal places that we aren't using. This keeps our code short, clear, easy to read since we are not converting things back and forth between VARCHAR and MONEY over and over to make things "look good" in one spot but to make them “work correctly” in another.

Along those same lines, suppose we have a MONEY value like this:

1846.4069

... and we need to return that value as an integer. What should we return, a VARCHAR value or a MONEY value? The VARCHAR value of "1846" will look nice, but again: we cannot sort, compare or do anything with it -- it is just a picture of data, not actual data. If we return a MONEY value of 1846.0000, we can and should ignore the decimal portion since it does not affect anything and enjoy the benefit of returning an actual numeric value that both the database and the client can sort, compare, calculate, and so on.

If you don't agree with any of that, stop reading now and let me know in the comments. Otherwise, let's continue on ...

Another Obvious Example

What about if we want to store only values that are decimals, such as .0234 or .963? These values will never have any digits before the decimal, but will always have one to four digits after the decimal (we only need 4 digits of precision). That is, we are storing values from -.9999 to .9999 only. With only MONEY and VARCHAR available, what data type should we use to store and work with these values?

Once again, suppose I argue that VARCHAR is the way to go. After all, if we use MONEY, every value will be preceded by "0.", which is not what we want and again a waste of space. We don't need the integer portion, and having it returned and seeing it everywhere will be confusing. If we use VARCHAR it will always look perfect, and we again can use CHECK constraints that parse the string to ensure we have valid data. Things still don’t sort or compare or calculate, but we can CONVERT back and forth as needed.

Yet once again, I would hope that you will agree that the suggestion to use VARCHAR is not a good one. We should use a numeric data type, and therefore MONEY is the best choice. It handles our data perfectly with the accuracy we need, we can do math, sort, and compare. We know that it will be valid and accurate, and a simple CHECK constraint ensures that we only have values between -.9999 and .9999 as opposed to using string parsing to determine this. And, again, even though in the T-SQL world all of our money values have an integer portion of “0” attached to them, it should make sense that we should not worry about this since our data is accurate and correct, it doesn't affect calculations, and the client can output these decimals without the preceding “0” very easily.

This also affects doing some math on a value like 139.592 to return only the decimal portion; for all of the reasons stated, we should all agree that the value to return is a MONEY value of 0.5920 and not a VARCHAR value of ".5920".

Finally, if we always store and return MONEY for both our integer values and our decimal values, we have another huge benefit: we can do math on both of those values together and get accurate, consistent results. No conversions, no string manipulation or parsing or concatenation, no worrying about how things are formatted or stored or how they look; we can do simple, efficient and accurate math on all of our data with standard mathematical operations and it just works perfectly and intuitively.

Getting to the Point (Finally!)

Are we all agreed on the above? Good. So, then, what’s my point?

Shouldn't we follow the exact same logic and reasoning when storing date and time data?

The exact same scenario happens every day, only programmers are deciding how to store or return dates without times, or times without dates. Yet, very often we see beginners choosing to store their values as VARCHARs and/or return them and manipulate them as VARCHARs, instead of using the easiest, most obvious and appropriate data type: DATETIME.

Working with Date-Only Data

If you need to store just a date, use a DATETIME. Don’t worry about the “extra” time that is stored, just enforce that it is always midnight and it won’t affect anything – just like forcing a decimal to be “.000” and ignoring it! It is the exact same logic. And, by that same token, don’t worry about that time being there and feel you need to convert everything to VARCHAR to make it “look” right. Just use data in the proper type throughout your SQL code, ignore the time portion, and return the DATETIME value – in the correct type – to your clients. Don’t try to “help” them by converting those nice, accurate, and clean DATETIME values to a string to “hide” the time part – just return the value, just as you would not try to “hide” a decimal portion of “.000” in a numeric by converting it to a string.

Luckily, many programmers do indeed use DateTime when all they want to store is dates, but of course many do obsess about “hiding” that time portion. Don’t do it. Leave your data in the proper type, enforce a time of only midnight with a simple check constraint (e.g., CHECK dateAdd(dd,datediff(dd,0,yourdate),0) = yourdate) and now you can compare, sort, do math, and everything else you ever need on those values, and your client can do the same. Just as you would use a MONEY data type to store numeric data, if you had to, as opposed to a VARCHAR. It’s easier, shorter, and more efficient – it just makes sense.

The same concept applies if you have a DATETIME value like "5/6/2007 5:00 PM" and you'd like to return just the date portion: Should you return a VARCHAR or a DATETIME? For all of the reasons stated, it should be clear that you simply return a DATETIME value a midnight -- "5/6/2007 12:00AM" -- knowing that the time portion is now "zero" and even though it is there and we see it, it does not affect any calculations and can easily be excluded when the client presents the data.

To "round" a DATETIME so that the time at midnight is returned:

select dateadd(dd,0, datediff(dd,0, datetimeval)) as date_at_midnight

Working with Time-Only Data

Now, what about storing only a Time? Here’s where people really get confused, but, again, it is exactly the same as storing a value between -.9999 and .9999 in either MONEY or a VARCHAR. We agreed to use MONEY, and simply accept the fact that our integer portion of “0” is always there, but it doesn’t affect anything; we can simply ignore it and let our client worry about not displaying it. Remember, when storing a true decimal value, we had no choice – that zero was always there, whether we displayed it or not, right? Well, DATETIME values have the exact same concept! The decimal portion of a DATETIME is the time component, and even though we just want to store time values, a DATETIME works perfectly for us. We simply ensure that we store a 0 value as the date portion and we ignore it!

For DATETIME data, a date of “0” that doesn’t affect any calculations is “1/1/1900”, just as a time of “0” that doesn’t affect any calculations is “12:00:00 AM”. Thus, “1/1/1900 12:00:00AM” is the equivalent to a decimal value of 0.0 -- adding or subtracting that value to other values will not have any effect.

In fact, take a look at this:

select cast(0.0 as datetime) as ZeroDateTime

ZeroDateTime
-----------------------
1900-01-01 00:00:00.000

(1 row(s) affected)

select getdate() as Now, getdate() + '1/1/1900 12:00:00 AM' as NowPlusZeroDateTime

Now NowPlusZeroDateTime
----------------------- -----------------------
2007-08-30 20:55:48.513 2007-08-30 20:55:48.513

(1 row(s) affected)

Notice that the numeric value of 0.0 is converted nicely to '1/1/1900 12:00:00 AM', and that adding '1/1/1900 12:00:00 AM' to a DATETIME value has no effect.

So, if we want to store only a time, without a date, we just:

Use a DATETIME data type
Ensure that the date value of that time is 0 (that is, "1/1/1900") with a check constraint (e.g., CHECK datediff(dd,0,TransTime) = 0)
Ignore the date in our SQL code even though we might “see it” here and there
Keep our data in the correct data type throughout our code without worrying how it “looks” and trying to “hide” the date part
Return that DATETIME value to our client and let it worry about hiding the “1/1/1900”, just as we let the client worry about hiding the preceding “0.” in a decimal value such as 0.2394.

Once again, let's consider what to do if we have a DATETIME value with such as "6/1/2007 5:00 PM" and we'd like to just return the time portion: Do we return a VARCHAR or a DATETIME? By now, the answer should be clear: we return data in the correct type, DATETIME, and we simply use the "0" date of 1/1/1900 by returning "1/1/1900 5:00 PM" knowing that the date portion will not affect the time value just as a an integer portion of 0 will not affect a decimal value.

To return just the time portion of a DATETIME (i.e., at the base date of 1/1/1900):

select datetimeval - dateadd(dd,0, datediff(dd,0, datetimeval)) as time_at_base_date

Finally, just as in our previous MONEY example, if we exclusively work with dates and times using the correct DATETIME data type throughout our schema and our SQL code, we can simply add values together to combine a date and a time using simple math:

select somedate + sometime as somedatetime

No converting, no string manipulations or parsing, no worrying about AM or PM or time formats or anything. If we keep our data in the correct types, and constraint it properly, everything works as it should – quickly, accurately, and intuitively.

Conclusion

The next time you are working with dates and times, please remember: how would you handle things if you were working with integers and decimals? The same logic and reasoning applies. Be smart, let SQL do the work for you and use the right data types for the job, even if things don't always "look" right. It's not about how good your data looks, it's about how accurate it actually is.

see also:

Legacy Comments


bob 2007-08-29	re: Working with Date-Only and Time-Only data in SQL Server Makes me want to say DUH! but I've seen very similar things done all over the place. It's somewhat analogous to storing zip codes as numerics because: "Zip codes are always numbers! (and we'll never do business in Canada)" It's an easy way to spot inexperienced developers. Anyway . . . despite the strength of your points, I really wish that SQL had a date only datatype, if only for the convenience of not having to fiddle with unwanted time parts. It's the same as if we had to use floats to store integer values.

bob 2007-08-29	re: Working with Date-Only and Time-Only data in SQL Server I have to add that I get the incredible pleasure [/sarcasm] of working with a vendor built system that stores dates in 2 formats -- all integers: The most useful one is 2007163 which is year and 3 digit day of year (hey, it makes computing date differences easy because everyone knows that Dec 31 2006 and Jan 1 2007 are 636 days apart!) Next is MMDDYY but since it's an int, single digit months can't have leading zeros: 12503 is what now? For this system, storing dates as text would be a step up . . .

Jeff 2007-08-29	re: Working with Date-Only and Time-Only data in SQL Server Thanks for the comments, Bob. What's so funny to me is I remember the "good old days" before date data types existed, and you were forced to make up conventions and rules and formats and write long, complicated string or numeric expressions for sorting and date math and formatting. It was amazingly difficult to calculate by hand how many days there are between 2005-02-03 and 2006-10-31 doing it manually! Yet, we had to back in the day .... But now, we have these wonderful databases with these beautiful data types that do all this work for us, and we have these great front-end applications that let you format a date any way you want -- "Monday, June 3" or "2004-02-10" or "February 10" and so on -- using simple format strings, yet people avoid using them and insist on doing things manually the hard way! I think that's why I get so caught up in and annoyed by the abuse (or lack of use at all!) for date time data types ... they are so easy to use, so powerful, so NICE to have, yet people work overtime to AVOID using them and insist on converting things to strings every chance single they get.... can anyone explain this?

Mladen 2007-08-30	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME work stability? :)

Jim 2007-08-30	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME Also, just wanted to note that SQL Server 2008 has added 4 new data types: * DATE – a date only type * TIME – a time only type * DATETIMEOFFSET – a time zone aware datetime type * DATETIME2 – a datetime type w/ larger fractional seconds and year range than the existing DATETIME type So maybe in a few years, we won't need to worry so much about separating out dates and times. :)

Eric W. Bachtal 2007-08-31	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME Agreed on handling date/time values in their native type, instead of as strings. Yet, as the comments on my post from a few years ago make clear, this remains an issue of some confusion for folks: http://ewbi.blogs.com/develops/2004/11/sql_server_date.html There are also some additional date/time to just date conversion/rounding options described there. Thanks for giving this some additional attention.

Andre 2007-09-04	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME Agreed, However, a Date and a Time column data type would be a "nice to have" but I usually use the method that you specified - use datetime and ignore what you don't need. I'll rather take valid data than varchar which could be invalid, if someone decides to manually modify.

Soft-wikiteam 2007-09-05	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME Is very hard to work with datetime, or timestamp... In my work transform the datetime in string. I simple for me to work in string format

Jeff 2007-09-05	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME >>Is very hard to work with datetime, or timestamp... In my work transform the datetime in string. I simple for me to work in string format Wow! My entire article is written specifically for you, it seems ... Any chance you could provide some details on this, why dateTime data types are "hard to work with" ???

paresh parekh 2007-09-11	i want to store only date part in date datatype i want to store only date part in date data type column because when i see the table data in oracle enterprise manager it is showing date with time also so when i export those table to SAS it will select time also. please give me the solution.

Addy 2007-09-26	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME Hi I have received a SQL backup file of a database we are migrating from an existing database application to our own database application (also based around SQL). In the data we have received, we have approx. 220000 records and about 6 fields are supposedly date fields but the information has been stored as a 7 digit integer, year followed by number of days, exactly as described by another person above. What is the best way of transferring all these integer values into proper dates so that we can store them in DateTime fields in our database? Thanks.

Jeff 2007-09-26	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME Addy -- We just need to do some simple math. To get the year, we just divide our number by 1000, and to get the day number in that year we calculate the mod 1000 of that number. Thus, we have something like this: declare @val int set @val = 2004365 select dateadd(year, (@val/1000)-1900, 0) + (@val % 1000 - 1)

Addy 2007-09-27	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME Jeff Many Thanks for your help. Next question, I am somethign of a novice when it comes to programming in SQL (most of my work is done in Access using VBA). I tried your example in SQL Query Analyser and it worked perfectly. However, how do I actually apply this to a whole table of records, in multiple columns, and how do I either update the values that are there, or create new columns of DateTime data format and add the newly calculated dates in? Any assistance would be most appreciated

Addy 2007-09-27	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME Jeff Further to my earlier message, I have foudn a way fo doing it by creating new fields in the table design, setting them as datetime data types and then running an update query which updates these fields to the date time calculated by your example above. Probably not the easiest or most effective way of doing it but it works! Thanks again. P.S. If there is a more elegant solution to this I would be interested to know it to further my SQL knowledge.

Jeff 2007-09-27	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME Addy -- that's pretty much how you do it, I think you've got it. If you have specific SQL questions, I strongly recommend the SQLTeam.com forums, there's lots of great people who actively answer questions over there who really know their stuff. - Jeff

Addy 2007-09-28	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME Thanks again - will take a look at the forums.

Cupidazul 2007-10-26	Working with workshifts Hi all, I've been struggling to get some date time calculations to work in SQL, this has been a real challenge... I need a function that gets (starttime, endtime, workshift) and returns the timespent regarding the workshift. Workshift can be in one or more lines, each one separated by a char(10) and with the format of: day_range date_range [{time}] where: 1. day_range Specifies a day or range of days in this format: week_day \| week_day - week_day Valid week_day values are Sun, Mon, Tue, Wed, Thu, Fri, and Sat. 2. date_range Specifies a date or range of dates in this format: date \| date - date Enter date as mm/dd/yyyy, where mm is the month, dd is the day, and yyyy is the year. 3. time Specifies a time or range of times in this format: {hh:mm[:ss] [am or pm]} \| {hh:mm[:ss] [am or pm]} - {hh:mm[:ss] [am or pm]} Cheers gurus... cupi@zul

Jeff 2007-10-26	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME Cupidazul -- you are having trouble because that is a horrible design that doesn't use the correct datetime data types. Good luck, and if you can change the design at all, I highly recommend to do so. The entire point of this post was that if you use the correct data type -- datetime -- when dealing with dates, everything is easy and works. When you don't, you have a mess on your hands.

Roger 2008-05-20	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME Jeff, I am also extracting data from an SQL database with the intention of migrating some content to a new database. In the existing data is DOB and other such date fields. These are expressed as a six digit numeric format. Example - one record shows 702605 and I know the actual date is 1/09/1924 by looking up the same persons details in another system. I need the formats of dates to be the same as I have to merge the records and DOB is one of the qualifiers. I understand the methodology explained for a seven digit number but these are all six digits. Can you shed any light on this for me please? I am an SQL novice too. Cheers... Roger

tim 2008-05-23	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME your section on working with time only is flawed. if i have the date '20/05/08 7:00 AM' and i have the arbitary time of '8:00 AM' that i want to compare the first time to, i can't just "zero" the dte portion to 1/1/1900 because a datediff will return a century's worth of difference. the only way to handle this using date time is to strip the date portion into a STRING and concatenate it with my '8:00 AM' variable. if there is another way using only datetime datatypes in sql2005 i'm all ears and look forward to your full example proving me wrong.

Jeff 2008-05-23	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME tim -- You didn't read the article very carefully, did you? Let's go back to decimals. How would you write t-sql code to determine if the decimal portion of 2.75 is greater than .6 ? Would you use string manipulation or would you use math? If you do it with math, how would you do it? Then, try that exact same approach using DateTimes. I look forward to your response.

Sarath 2008-06-05	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME 1. I have stored date as 2008-01-01 in my db ad datatype date/time. 2. Using my SQL statement I have retrived the date from db in mydate=rs.getDate(1) 3. I have to check this date retrived and stored in mydate with two strings to check that the date retrived from db is laying in between these two date strings. How can I achieve this? Please help me.

mreynol5 2008-06-06	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME I must be missing something here or perhaps the issue lies between my ears. I am attempting to sort records that fall between two time stamps. All that I really want to do is return the count (). Here is my SQL (the base code is C#): String sqlcmd = "SELECT count() FROM RealTime WHERE CallStartTime BETWEEN @starttime AND @endtime AND TraversalString LIKE '%1.0%'"; I have instantiated the two variables with dates pulled from an AJAX calendar extender (6/3/2008) for example. The "LIKE" component is searching for an occurance of that item within a concatonated string. I keep getting a 0 count. Any thoughts?

Roger 2008-06-24	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME Hi, I resolved the six digit date problem. In SQL I changed the value from int for DOB to Date/Time. The date when the table was refreshed came out with a bogus year in the future (e.g.3879) but the day and month were correct. Using Excel I copied the data and applied the following : SQL Date 14/01/3879 minus 30/12/3798 equals 14/01/1980 - we checked the dates with our other system and there was a 100% correct match. Now if I could just figure out how they convert a six digit time into a meaningful human time! Same methodology to date does not work.

PeterD 2008-07-07	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME Hi Jeff Just found your post while googling to research a problem of mine. Great post! I'm somwhat of a novice using SQL but have been creating database with MS Access and programming in VBA for years. Your analogy makes perfect sens and I can see where this should be the better approach most of the time. But here is my dilema. I'm using an MS Access front end on an MS SQL server back end. My problem is that Access and MS SQL have different base date. As you suggested in T-SQL Select Cast(0.0 as datetime) returns 1900-01-01 00:00:00.000 But in Access VBA Format(CDate(0.0), "yyyy-mm-dd hh:nn:ss") returns 1899-12-30 00:00:00 So if I store the date using the MS Access Base, on the front end, everything is peachy. Access seem to natively suppress the date portion for any datetime value where the date is equal to day 0. But,should I use SQL directly in the future, all value will be offset by 2. So that if I for example I do the following select getdate() as Now, getdate() + '12/31/1899 12:00:00 AM' as NowPlusZeroDateTimeAccess Now NowPlusZeroDateTimeAccess ----------------------- ------------------------- 2008-07-07 16:55:33.840 2008-07-06 16:55:33.840 On the other hand, if I store the time values in the database using T-SQL base date, when I use the front end, the date (1/1/1900) is displayed whenever the user attempts to edit the time. With Access front end, I can speficy the display format to ignore the date component, but when one enters the field in edit mode, the full date and time are displayed. I don't think there is a way to turn this off in Access. I am not expeting a fix. Obviously working with an Access front end is in noy an up to date standard for DB application of any complexity. But I just wanted to point this out. From my side, I will just pick the Access Base Day to make things simpler for users, but I will have to be careful should I ever need to add or subtract time only fields with date & time fields outside of Access and VBA.

Gaurang Shah 2008-07-25	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME Jeff --- Excellent ! Thnks for enhancing the skill logically --- Good Bye

Burke 2008-08-05	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME Here is an article that explains precisely how to work with just time values in SQL Server... http://armsinfragilehands.blogspot.com/2008/08/adding-and-subtracting-time-values-in.html

jeff 2008-08-05	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME Burke -- that article represents everything that you should absolutely NOT be doing when working with just time values in SQL Server. Never, ever, ever, ever use VARCHAR to store dates or times -- use the correct data type! Did you even read the article?

agz86 2008-09-17	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME i have 2 dates in string format.i need to add these 2 dates to get the next date. is there any sql query for this?? Plz sum1 help me out with this. urgent!! Thanx in advance...

mml 2008-09-17	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME The difference between these two statements to return just the time portion doesn't stick out to me. Is there a difference in accurracy? select @test - dateadd(dd,0, datediff(dd,0, @test)) as time_at_base_date select @test - datediff(dd,0, @test) as time_at_base_date

noha 2008-11-06	i cant compare two fields as datetime SELECT @DateTime AS Expr1, LastModifiedDate FROM dbo.tblProjectHistory WHERE (LastModifiedDate = @DateTime) AND (RecordId = 1) LastModifiedDate fields type date time l LastModifiedDate=23/10/2008 2:18:51 PM @DateTime=23/10/2008 2:18:51 PM when run Query result null please help me

Andrew 2008-11-24	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME Heres on for you, I agree that using date and time should be the datatype to use, but this does not work when storing time spans longer than 24 hours, I have a situation where a user needs to input how long a staff member was OFF shift sometimea is could be 72 hours 35 Mins, the question is how do you work out the total hours off shift over a month using date and time to store time spans also how do you make sure your user eneter the value in correctly, 32 hrs 15 mins it would be great if sql could have a timespan datatype, that took days, hours and minutes so we could add up values to give you the total time for that kind of event using sum..

Jeff S 2008-11-24	re: Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME Andrew -- even for time spans over 1 day, you can still very easily use the DateTime data type: http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server