MS SQL Date Only Without Time

Question

Question  Hello All   I ve had some confusion for quite some time with essentially flooring a DateTime SQL type using T-SQL   Essentially  I want to take a DateTime value of say 2008-12-1 14 30 12 and make it 2008-12-1 00 00 00   Alot of the queries we run for reports use a date value in the WHERE clause  but I either have a start and end date value of a day and use a BETWEEN  or I find some other method   Currently I m using the following   WHERE CAST CONVERT VARCHAR   tstamp   102  AS DATETIME     dateParam   However  this seems kinda clunky   I was hoping there would be something more simple like  CAST  tstamp  AS DATE   Some places online recommend using DATEPART   function  but then I end up with something like this     WHERE DATEPART year   tstamp     DATEPART year   dateParam  AND DATEPART month   tstamp     DATEPART month   dateParam  AND DATEPART day   tstamp     DATEPART day   dateParam     Maybe I m being overly concerned with something small and if so please let me know   I just want to make sure the stuff I m writing is as efficient as possible   I want to eliminate any weak links   Any suggestions   Thanks  C   Solution  Thanks everyone for the great feedback   A lot of useful information   I m going to change around our functions to eliminate the function on the left hand side of the operator   Although most of our date columns don t use indexes  it is probably still a better practice

User · Answer

Alternatively you could use  declare  d datetimeselect  d     2008-12-1 14 30 12  where tstamp    BETWEEN dateadd dd  datediff dd  0   d  0  0     AND dateadd dd  datediff dd  0   d  1  0

User · Answer

Yes  T-SQL can feel extremely primitive at times  and it is things like these that often times push me to doing a lot of my logic in my language of choice  such as C     However  when you absolutely need to do some of these things in SQL for performance reasons  then your best bet is to create functions to house these  algorithms    Take a look at this article  He offers up quite a few handy SQL functions along these lines that I think will help you   http   weblogs sqlteam com jeffs archive 2007 01 02 56079 aspx

User · Answer

Alternatively you could use  declare  d datetimeselect  d     2008-12-1 14 30 12  where tstamp    BETWEEN dateadd dd  datediff dd  0   d  0  0     AND dateadd dd  datediff dd  0   d  1  0

User · Answer

If you re using SQL Server 2008 it has this built in now  see this in books online   CAST GETDATE   AS date

User · Answer

WHERE DATEDIFF day  tstamp   dateParam    0   This should get you there if you don t care about time   This is to answer the meta question of comparing the dates of two values when you don t care about the time

User · Answer

The Date functions posted by others are the most correct way to handle this   However  it s funny you mention the term  floor   because there s a little hack that will run somewhat faster   CAST FLOOR CAST  dateParam AS float   AS DateTime

User · Answer

that is very bad for performance  take a look at Only In A Database Can You Get 1000    Improvement By Changing A Few Lines Of Code  functions on the left side of the operator are bad  here is what you need to do   declare  d datetime select  d     2008-12-1 14 30 12   where tstamp  gt   dateadd dd  datediff dd  0   d  0  0  and tstamp  lt  dateadd dd  datediff dd  0   d  1  0    Run this to see what it does  select dateadd dd  datediff dd  0  getdate    1  0  select dateadd dd  datediff dd  0  getdate    0  0

User · Answer

DATEADD d  0  DATEDIFF d  0   tstamp      Edit  While this will remove the time portion of your datetime  it will also make the condition non SARGable  If that s important for this query  an indexed view or a between clause is more appropriate

User · Answer

CONVERT date  GETDATE    and CONVERT time  GETDATE    works in SQL Server 2008  I m uncertain about 2005

User · Answer

DATEADD d  0  DATEDIFF d  0   tstamp      Edit  While this will remove the time portion of your datetime  it will also make the condition non SARGable  If that s important for this query  an indexed view or a between clause is more appropriate

User · Answer

FWIW  I ve been doing the same thing as you for years  CAST CONVERT VARCHAR   tstamp   102  AS DATETIME     dateParam    Seems to me like this is one of the better ways to strip off time in terms of flexibility  speed and readabily   sorry   Some UDF functions as suggested can be useful  but UDFs can be slow with larger result sets

User · Answer

WHERE DATEDIFF day  tstamp   dateParam    0   This should get you there if you don t care about time   This is to answer the meta question of comparing the dates of two values when you don t care about the time

User · Answer

DATEADD d  0  DATEDIFF d  0   tstamp      Edit  While this will remove the time portion of your datetime  it will also make the condition non SARGable  If that s important for this query  an indexed view or a between clause is more appropriate

User · Answer

Here s a query that will return all results within a range of days   DECLARE  startDate DATETIME DECLARE  endDate DATETIME  SET  startDate   DATEADD day  -30  GETDATE    SET  endDate   GETDATE    SELECT   FROM table WHERE dateColumn  gt   DATEADD day  DATEDIFF day  0   startDate   0    AND dateColumn  lt   DATEADD day  1  DATEDIFF day  0   endDate

User · Answer

Here s a query that will return all results within a range of days   DECLARE  startDate DATETIME DECLARE  endDate DATETIME  SET  startDate   DATEADD day  -30  GETDATE    SET  endDate   GETDATE    SELECT   FROM table WHERE dateColumn  gt   DATEADD day  DATEDIFF day  0   startDate   0    AND dateColumn  lt   DATEADD day  1  DATEDIFF day  0   endDate

User · Answer

Careful here  if you use anything a long the lines of WHERE CAST CONVERT VARCHAR   tstamp   102  AS DATETIME     dateParam it will force a scan on the table and no indexes will be used for that portion   A much cleaner way of doing this is defining a calculated column   create table  t       d datetime        d2 as          cast  datepart year d  as varchar 4      -            right  0    cast  datepart month d  as varchar 2   2     -             right  0    cast  datepart day d  as varchar 2   2      -- notice a lot of care need to be taken to ensure the format is comparable   zero padding   insert  t  values  getdate     create index idx on  t d2   select d2  count d2  from  t  where d2 between  2008-01-01  and  2009-01-22  group by d2 -- index seek is used   This way you can directly check the d2 column and an index will be used and you dont have to muck around with conversions

User · Answer

Careful here  if you use anything a long the lines of WHERE CAST CONVERT VARCHAR   tstamp   102  AS DATETIME     dateParam it will force a scan on the table and no indexes will be used for that portion   A much cleaner way of doing this is defining a calculated column   create table  t       d datetime        d2 as          cast  datepart year d  as varchar 4      -            right  0    cast  datepart month d  as varchar 2   2     -             right  0    cast  datepart day d  as varchar 2   2      -- notice a lot of care need to be taken to ensure the format is comparable   zero padding   insert  t  values  getdate     create index idx on  t d2   select d2  count d2  from  t  where d2 between  2008-01-01  and  2009-01-22  group by d2 -- index seek is used   This way you can directly check the d2 column and an index will be used and you dont have to muck around with conversions

User · Answer

WHERE DATEDIFF day  tstamp   dateParam    0   This should get you there if you don t care about time   This is to answer the meta question of comparing the dates of two values when you don t care about the time

User · Answer

If you re using SQL Server 2008 it has this built in now  see this in books online   CAST GETDATE   AS date

User · Answer

Yes  T-SQL can feel extremely primitive at times  and it is things like these that often times push me to doing a lot of my logic in my language of choice  such as C     However  when you absolutely need to do some of these things in SQL for performance reasons  then your best bet is to create functions to house these  algorithms    Take a look at this article  He offers up quite a few handy SQL functions along these lines that I think will help you   http   weblogs sqlteam com jeffs archive 2007 01 02 56079 aspx

User · Answer

FWIW  I ve been doing the same thing as you for years  CAST CONVERT VARCHAR   tstamp   102  AS DATETIME     dateParam    Seems to me like this is one of the better ways to strip off time in terms of flexibility  speed and readabily   sorry   Some UDF functions as suggested can be useful  but UDFs can be slow with larger result sets

User · Answer

If you re using SQL Server 2008 it has this built in now  see this in books online   CAST GETDATE   AS date

User · Answer

WHERE DATEDIFF day  tstamp   dateParam    0   This should get you there if you don t care about time   This is to answer the meta question of comparing the dates of two values when you don t care about the time

User · Answer

The Date functions posted by others are the most correct way to handle this   However  it s funny you mention the term  floor   because there s a little hack that will run somewhat faster   CAST FLOOR CAST  dateParam AS float   AS DateTime

User · Answer

that is very bad for performance  take a look at Only In A Database Can You Get 1000    Improvement By Changing A Few Lines Of Code  functions on the left side of the operator are bad  here is what you need to do   declare  d datetime select  d     2008-12-1 14 30 12   where tstamp  gt   dateadd dd  datediff dd  0   d  0  0  and tstamp  lt  dateadd dd  datediff dd  0   d  1  0    Run this to see what it does  select dateadd dd  datediff dd  0  getdate    1  0  select dateadd dd  datediff dd  0  getdate    0  0

User · Answer

Yes  T-SQL can feel extremely primitive at times  and it is things like these that often times push me to doing a lot of my logic in my language of choice  such as C     However  when you absolutely need to do some of these things in SQL for performance reasons  then your best bet is to create functions to house these  algorithms    Take a look at this article  He offers up quite a few handy SQL functions along these lines that I think will help you   http   weblogs sqlteam com jeffs archive 2007 01 02 56079 aspx

User · Answer

that is very bad for performance  take a look at Only In A Database Can You Get 1000    Improvement By Changing A Few Lines Of Code  functions on the left side of the operator are bad  here is what you need to do   declare  d datetime select  d     2008-12-1 14 30 12   where tstamp  gt   dateadd dd  datediff dd  0   d  0  0  and tstamp  lt  dateadd dd  datediff dd  0   d  1  0    Run this to see what it does  select dateadd dd  datediff dd  0  getdate    1  0  select dateadd dd  datediff dd  0  getdate    0  0

User · Answer

Careful here  if you use anything a long the lines of WHERE CAST CONVERT VARCHAR   tstamp   102  AS DATETIME     dateParam it will force a scan on the table and no indexes will be used for that portion   A much cleaner way of doing this is defining a calculated column   create table  t       d datetime        d2 as          cast  datepart year d  as varchar 4      -            right  0    cast  datepart month d  as varchar 2   2     -             right  0    cast  datepart day d  as varchar 2   2      -- notice a lot of care need to be taken to ensure the format is comparable   zero padding   insert  t  values  getdate     create index idx on  t d2   select d2  count d2  from  t  where d2 between  2008-01-01  and  2009-01-22  group by d2 -- index seek is used   This way you can directly check the d2 column and an index will be used and you dont have to muck around with conversions

User · Answer

that is very bad for performance  take a look at Only In A Database Can You Get 1000    Improvement By Changing A Few Lines Of Code  functions on the left side of the operator are bad  here is what you need to do   declare  d datetime select  d     2008-12-1 14 30 12   where tstamp  gt   dateadd dd  datediff dd  0   d  0  0  and tstamp  lt  dateadd dd  datediff dd  0   d  1  0    Run this to see what it does  select dateadd dd  datediff dd  0  getdate    1  0  select dateadd dd  datediff dd  0  getdate    0  0

User · Answer

Here s a query that will return all results within a range of days   DECLARE  startDate DATETIME DECLARE  endDate DATETIME  SET  startDate   DATEADD day  -30  GETDATE    SET  endDate   GETDATE    SELECT   FROM table WHERE dateColumn  gt   DATEADD day  DATEDIFF day  0   startDate   0    AND dateColumn  lt   DATEADD day  1  DATEDIFF day  0   endDate

User · Answer

CONVERT date  GETDATE    and CONVERT time  GETDATE    works in SQL Server 2008  I m uncertain about 2005

User · Answer

Yes  T-SQL can feel extremely primitive at times  and it is things like these that often times push me to doing a lot of my logic in my language of choice  such as C     However  when you absolutely need to do some of these things in SQL for performance reasons  then your best bet is to create functions to house these  algorithms    Take a look at this article  He offers up quite a few handy SQL functions along these lines that I think will help you   http   weblogs sqlteam com jeffs archive 2007 01 02 56079 aspx

User · Answer

If you re using SQL Server 2008 it has this built in now  see this in books online   CAST GETDATE   AS date

User · Answer

The Date functions posted by others are the most correct way to handle this   However  it s funny you mention the term  floor   because there s a little hack that will run somewhat faster   CAST FLOOR CAST  dateParam AS float   AS DateTime

User · Answer

FWIW  I ve been doing the same thing as you for years  CAST CONVERT VARCHAR   tstamp   102  AS DATETIME     dateParam    Seems to me like this is one of the better ways to strip off time in terms of flexibility  speed and readabily   sorry   Some UDF functions as suggested can be useful  but UDFs can be slow with larger result sets

User · Answer

How about this   SELECT DATEADD dd  DATEDIFF dd 0 GETDATE     0

User · Answer

Here s a query that will return all results within a range of days   DECLARE  startDate DATETIME DECLARE  endDate DATETIME  SET  startDate   DATEADD day  -30  GETDATE    SET  endDate   GETDATE    SELECT   FROM table WHERE dateColumn  gt   DATEADD day  DATEDIFF day  0   startDate   0    AND dateColumn  lt   DATEADD day  1  DATEDIFF day  0   endDate

User · Answer

DATEADD d  0  DATEDIFF d  0   tstamp      Edit  While this will remove the time portion of your datetime  it will also make the condition non SARGable  If that s important for this query  an indexed view or a between clause is more appropriate

User · Answer

Careful here  if you use anything a long the lines of WHERE CAST CONVERT VARCHAR   tstamp   102  AS DATETIME     dateParam it will force a scan on the table and no indexes will be used for that portion   A much cleaner way of doing this is defining a calculated column   create table  t       d datetime        d2 as          cast  datepart year d  as varchar 4      -            right  0    cast  datepart month d  as varchar 2   2     -             right  0    cast  datepart day d  as varchar 2   2      -- notice a lot of care need to be taken to ensure the format is comparable   zero padding   insert  t  values  getdate     create index idx on  t d2   select d2  count d2  from  t  where d2 between  2008-01-01  and  2009-01-22  group by d2 -- index seek is used   This way you can directly check the d2 column and an index will be used and you dont have to muck around with conversions

User · Answer

FWIW  I ve been doing the same thing as you for years  CAST CONVERT VARCHAR   tstamp   102  AS DATETIME     dateParam    Seems to me like this is one of the better ways to strip off time in terms of flexibility  speed and readabily   sorry   Some UDF functions as suggested can be useful  but UDFs can be slow with larger result sets

User · Answer

The Date functions posted by others are the most correct way to handle this   However  it s funny you mention the term  floor   because there s a little hack that will run somewhat faster   CAST FLOOR CAST  dateParam AS float   AS DateTime

User · Answer

How about this   SELECT DATEADD dd  DATEDIFF dd 0 GETDATE     0

[sql] MS SQL Date Only Without Time

Examples related to sql

Examples related to sql-server

Examples related to tsql