Getting a Date from DateTime
-
Hello,
I would like to introduce a couple of issues related to stripping (or not) the datetime field of the time. There already was a good discussion on 2005 \Administering forum started by SQL DBA3, named datetime, Posted 1/2/2005 4:22:00 PM. I would post the issues first then summarize the solutions from the discussion and why I can not use them.
Issue 1: I can not find a date type in SQL Server that does not contain time
Issue 2: Conversion to char/varchar types using Convert with style affects sort order that is now string - wise, not date - wise so 01/01/2005 comes BEFORE 12/30/2004
Issue 3: Grouping by date, for example, how many of calls each date is a problem with times
Issuer 4: First and Last values in the selected date interval are not correct if you don't pay special attention
If someone has a good solution on the back-end (SQL Server level) please, come forward. My best one so far was to create a function:
CREATE FUNCTION dbo.ConvShortDate (@MyDate datetime)
RETURNS smalldatetime
AS
BEGIN
Declare @d smalldatetime
Set @d = Convert(smalldatetime, Convert(varchar(10), @MyDate,101))
RETURN (@d)
END
The reason I am converting to string first to drop the time and to the smalldatetime after is because of the Issue # 2 - sort order.
Other solutions from the mentioned above topic were:
CONVERT(Varchar(12),GetDate(),101) - sort order is incorrect
WHERE orderdate > Getdate() - 3 This is for use in Where: does not work for the first and the last value, see Issue # 4 because it does use a date 3 days before or after, but not a whole day, just a part of the day starting with the time the GetDate() is run.
Where Day(Mydate) = Day(GetDate())
AND Month(Mydate) = Month(GetDate())
AND Year(Mydate) = Year(GetDate())
Those 3 lines I use myself in the Where to get something for today. I forgotten to mention that there is another way for the Where:
where mydayetime >= '01/01/2005' (this will put it at midnight 01/01/2005) AND mydatetime <'01/02/2005 this is to get things for all day 01/01/2005 but I am not sure it is sutable for grouping
Is there an EASY way to get things from the database for a certain day or group by day to return all things (like calls) for each day in a certain date interval?
Yelena
Regards,Yelena Varsha
-
CONVERT(Varchar(12),GetDate(),101) - sort order is incorrect
If you use 102, the sort order will be correct.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Some responses:
re Issue 1: SQL Server only contains a DateTime data type. Date and Time are always combined.
re Issue 2: Use CONVERT style 112 ISO (yyyymmdd) which will always sort correctly (and will work on non USA regionalised servers, EG: Uk / AU regional settings that use dd/mm/yyyy making 12/30/2004 an invalid date).
re Issue 3: GROUP BY CONVERT(CHAR(8),myDate,112)
re Issue 4: A human VS computer problem. Human thinks 2-jan-2005@01:01:01 is included in dates between 1-jan-2005 and 2-jan-2005. Computer thinks any date after 2-jan-2005@00:00:00 is out of range. Use WHERE CONVERT(CHAR(8),Mydate,112) = CONVERT(CHAR(8),GetDate(),112) to compare dates. You are then actually comparing a CHAR(8) string of 'YYYYMMDD', so no time values involved.
"WHERE orderdate > Getdate() - 3 "
use: DateAdd(day, -3, GetDate())
"Where Day(Mydate) = Day(GetDate())
AND Month(Mydate) = Month(GetDate())
AND Year(Mydate) = Year(GetDate()) "
WHERE CONVERT(CHAR(8),Mydate,112) = CONVERT(CHAR(8),GetDate(),112)
- "where mydayetime >= '01/01/2005' (this will put it at midnight 01/01/2005) AND mydatetime <'01/02/2005 this is to get things for all day 01/01/2005 but I am not sure it is sutable for grouping"
Again WHERE CONVERT(CHAR(8),mydayetime,112) = CONVERT(CHAR(8),'01/01/2005' ,112) will solve that
Julian Kuiters
juliankuiters.id.au -
Again, echoing partially Julian
1) You have to wait until the next version of SQL Server which should introduce a DATE datatype. Currently a DATETIME, just like the name says, contains *always* both a DATE and a TIME portion. However, when you set this time portion to midnight you avoid the common pitfalls.
2)+3) Set the time to midnight and CAST back to DATETIME. That way you avoid sorting and/or grouping trouble
4) There is no such thing as a free lunch.
You always have to pay attention to what you do. A UDF is IMO among the worst possible solution to set the time to midnight. Here are some variations on this topic. I like the first two approaches, but you should use that one which is most readable and intuitive to you:
SELECT CAST(CAST(SUBSTRING(CAST(GETDATE() AS BINARY(8)),1,4) AS INT) AS DATETIME)
SELECT DATEADD(d,DATEDIFF(d,0,getdate()),0)
SELECT CONVERT(DATETIME,CONVERT(CHAR(8),GETDATE(),112))
SELECT CONVERT(CHAR(8),GETDATE(),112)
SELECT CAST(CAST(GETDATE() AS VARCHAR(12)) AS DATETIME)
SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
SELECT CAST(SUBSTRING(CAST(GETDATE() AS BINARY(8)),1,4) + 0x00000000 AS DATETIME)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
Why do you believe creating a function is among the worst possible solutions? I guess I like to create functions for commonly used conversions. Not only do I help assure I do the conversion the same way every time, but I also tend to save some keystrokes within my code. Just curious.
Brian
-
Scalar UDF's like this are performance killers on larger tables.
Compare the results of doing this with a UDF and a single SELECT statement. They should speak for themselves. While you will maybe save some keystrokes (and, hey, I'm completely with you at this point), I think you'll save them in the wrong place.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Kathi, Julian, Frank and Brian,
Thanks!!! Those are great examples.
I new I had to ask real developers. I am sure aware about year/month/date format being great for sorting, and if someone else would ask this question I would maybe reply myself with it. I just did not think of it in my own case.
I new that SQL server contains only datetime, but I needed to do a reality check, maybe I missed something. Frank, when you say I have to wait till the next version, do you mean 2005? I do have a beta of 2005, I will check this out. I can not convert the database in question to 2005, it is supported by the vendor
UDF: The reason I need a UDF or a View or some other object is that I am using Business Objects reports. The Business Objects (BO) server is administered by another department. BO, as some maybe don't know does not access the database directly. There is a meta-layer called Universe that is a sort of a view with security and defined joins on database objects. You may use only objects in the Universe for the report. Grouping in BO goes by objects. That is why I need the field converted as a database object, maybe a view with the calculated field will be a good solution for me, I can use some expressions here that you posted. Performance on the other hand is not a problem yet. First, the table is not really that big yet. Second, the job will be running at night
Thanks again,
Yelena
Regards,Yelena Varsha
-
date is really a float where the decimal part is the time. To convert a date to date only, do this:
CAST(CAST(GetDate() AS INT) AS DATETIME)
Bam!
- John
-
Some more info:
If you want to count all items in a certain date, there is no need to spend cpu cycles casting back to datetime.
SELECT COUNT(*) FROM SomeTable Group By CAST(SomeTable.SomeDate AS INT)
You can sort by that int, since the whole part is the # of days since 1900-01-01 (try SELECT CAST(0 AS DATETIME))
-
John,
It is so COOL!
The only thing: do you know how I am loosing one day? Today is Jan 18 2005 and
GetDate() returns 2005-01-18 12:07:19.863
select cast(GetDate() as Integer) returns 38369
select cast(cast(GetDate() as Integer AS DateTime) returns 2005-01-19 00:00:00:000
Is it because it tries to round it to the nearest integer and now it is the afternoon? I tried the FLOOR like Frank posted:
SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
it returns 2005-01-18 00:00:00:000
What is interesting, DateTime is virtually a float, but you have to convert to Float explicitly, implicit conversion gives an error message
Yelena
Regards,Yelena Varsha
-
date is really a float where the decimal part is the time. To convert a date to date only, do this:
This is incorrect! The internal storage format for a DATETIME as it is currently implemented is BINARY(8), not FLOAT! First mistake!
Now, the second one is
CAST(CAST(GetDate() AS INT) AS DATETIME)
This rounds up to the next whole number for all time portions after 12:00:00. This is what Yelena experienced.
Bam!
Yes, Bam!
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Oh, and I forgot:
SELECT COUNT(*) FROM SomeTable Group By CAST(SomeTable.SomeDate AS INT)
Consider this:
CREATE TABLE #fool_me
(
dt DATETIME
)
INSERT INTO #fool_me VALUES ('20050118 12:00:01.997')
INSERT INTO #fool_me VALUES ('20050119 00:00:59.997')
INSERT INTO #fool_me VALUES ('20050119 23:00:01.997')
INSERT INTO #fool_me VALUES ('20050120 12:00:01.997')
SELECT MAX(dt), COUNT(*)
FROM #fool_me
GROUP BY CAST(#fool_me.dt AS INT)
DROP TABLE #fool_me
------------------------------------------------------ -----------
2005-01-19 00:00:59.997 2
2005-01-19 23:00:01.997 1
2005-01-20 12:00:01.997 1
If I had my way, I would call this slightly incorrect, too
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
sorry about the bad advice. I was working off memory. I think I remember that we cast to float first then cast to int. But using it as a number for comparison purposes is much faster than calling convert or making a string etc. Just check on the float int stuff to make sure the conversion works ok.
Use:
CAST(CAST(GetDate() AS FLOAT) AS INT)
here are some test cases:
DECLARE @jam as DATETIME
DECLARE @jpm as DATETIME
SELECT @jam = '2005/1/18 10:15 am'
SELECT @jpm = '2005/1/18 10:15 pm'
select CAST(@jAM AS FLOAT) , CAST(@jPM AS FLOAT)
-- returns 38368.427083333336, 38368.927083333336
select CAST(@jAM AS INT) , CAST(@jPM AS INT)
-- returns 38368, 38369
select CAST(CAST(@jAM AS FLOAT) AS INT) , CAST(CAST(@jPM AS FLOAT) AS INT)
-- returns 38368, 38368
-
I just wanted to add the reason just casting to int does not work is that for some reason casting a date to an int rounds the number rather than truncating the decimal part. When you cast a float to an int, it does not round the number.
Just for the hell of it, does anyone know why this behavior is a good idea?
-
John,
It is a good idea. As someone said, the more complex way they design it the more your manager values you for being able to work with it.
Yelena
Regards,Yelena Varsha
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply