Working with just time section of a datetime type
-
Hello again buddies, I wonder how can I work with just time section of a datetime or smalldatetime object ignoring its date section. wouldn't it be better to provide a "time" data type? What I am looking for is to query records that a datetime column of them is in a specified time with no importance on the date --- "Art happens when you least expect it."
-
Hello again buddies, I wonder how can I work with just time section of a datetime or smalldatetime object ignoring its date section. wouldn't it be better to provide a "time" data type? What I am looking for is to query records that a datetime column of them is in a specified time with no importance on the date --- "Art happens when you least expect it."
Use
DATEPART(). It is a bit of a mess if you want to do something like get me the records between 11:30 and 14:30 but it is doable.
-
Hello again buddies, I wonder how can I work with just time section of a datetime or smalldatetime object ignoring its date section. wouldn't it be better to provide a "time" data type? What I am looking for is to query records that a datetime column of them is in a specified time with no importance on the date --- "Art happens when you least expect it."
-
Set them all to the same date.
CAST('1900-01-01T' + SUBSTRING(CONVERT(varchar, theTime, 126), 12, 12) AS datetime)
Grim
(aka Toby)
MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL GO
(0 row(s) affected)
-
Use
DATEPART(). It is a bit of a mess if you want to do something like get me the records between 11:30 and 14:30 but it is doable.
Yes it works, thank you so much Colin :-) I am sorry if my questions seems so amature, I guess I am a good programmer but a newbie in SQL which I feel is a great different programming environment. I promiss that will change!!!! ;-) --- "Art happens when you least expect it."
-
Set them all to the same date.
CAST('1900-01-01T' + SUBSTRING(CONVERT(varchar, theTime, 126), 12, 12) AS datetime)
Grim
(aka Toby)
MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL GO
(0 row(s) affected)
-
Yes it works, thank you so much Colin :-) I am sorry if my questions seems so amature, I guess I am a good programmer but a newbie in SQL which I feel is a great different programming environment. I promiss that will change!!!! ;-) --- "Art happens when you least expect it."
Den2Fly wrote: a newbie in SQL .... I promiss that will change!!!! We've all got to start somewhere.
-
Seems a great trick, what about the performance? DATEPART or CAST?? --- "Art happens when you least expect it."
As Colin indicated in his original response, usability and efficiency both depend greatly on exactly what you're doing with it. Also, I've found that when you take two solutions to the same problem and compare them, it's possible that one solution is faster on one server, and the other is faster on a different one. For instance, stripping the time from a datetime. There are many ways to do this, the two most common being
CONVERT(varchar(8), theDate, 112)orDATEADD(day, 0, DATEDIFF(day, 0, theDate)). I find that DATEADD/DATEDIFF runs faster in our development environment by a factor of 5, but the CONVERT method is faster in our production environment by a factor of 2. So, the short answer is that you should write the query both ways, and benchmark them on your own system to see which one is faster on your own server. It also depends greatly on where/how you're using it. If you're using it in the WHERE clause, either one of them is going to be a pig. If you're just using it in the SELECT clause, then it will probably depend on your system hardware and configuration.
Grim
(aka Toby)
MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL GO
(0 row(s) affected)
-
As Colin indicated in his original response, usability and efficiency both depend greatly on exactly what you're doing with it. Also, I've found that when you take two solutions to the same problem and compare them, it's possible that one solution is faster on one server, and the other is faster on a different one. For instance, stripping the time from a datetime. There are many ways to do this, the two most common being
CONVERT(varchar(8), theDate, 112)orDATEADD(day, 0, DATEDIFF(day, 0, theDate)). I find that DATEADD/DATEDIFF runs faster in our development environment by a factor of 5, but the CONVERT method is faster in our production environment by a factor of 2. So, the short answer is that you should write the query both ways, and benchmark them on your own system to see which one is faster on your own server. It also depends greatly on where/how you're using it. If you're using it in the WHERE clause, either one of them is going to be a pig. If you're just using it in the SELECT clause, then it will probably depend on your system hardware and configuration.
Grim
(aka Toby)
MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL GO
(0 row(s) affected)