Checking Date Content - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Checking Date Content

Author

Topic

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2010-04-23 : 12:09:42

I have a field that is date/time defined, but i need to compare only the date - format yyyy-mm-dd. how can i pull just the date part and not the time?

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2010-04-23 : 12:12:54

something like: CONVERT(varchar(20),yourdate,112)

Note, it will not include the "-", simply yyyymmdd

Terry

-- The problem with socialism is that you eventually run out of other people’s money. -- Margaret Thatcher

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-23 : 12:13:36

use this to get time part 00:00
select dateadd(d,datediff(d,0,getdate()),0)

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 12:37:34

quote:
Originally posted by tosscrosby

something like: CONVERT(varchar(20),yourdate,112)

Note, it will not include the "-", simply yyyymmdd

Terry

-- The problem with socialism is that you eventually run out of other people’s money. -- Margaret Thatcher

not a good approach to change datatype just for formatting as it will make date manipulations difficult

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-23 : 13:35:32

use

datediff(dd, startdate, enddate)

for compare.

quote:
Originally posted by pgmr1998

I have a field that is date/time defined, but i need to compare only the date - format yyyy-mm-dd. how can i pull just the date part and not the time?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 13:37:52

quote:
Originally posted by hanbingl

use
datediff(dd, startdate, enddate)
for compare.

quote:
Originally posted by pgmr1998

I have a field that is date/time defined, but i need to compare only the date - format yyyy-mm-dd. how can i pull just the date part and not the time?

how do you use datediff for comparison? what will you compare above to?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2010-04-23 : 13:48:59

quote:
Originally posted by visakh16

quote:
Originally posted by tosscrosby

something like: CONVERT(varchar(20),yourdate,112)

Note, it will not include the "-", simply yyyymmdd

Terry

-- The problem with socialism is that you eventually run out of other people’s money. -- Margaret Thatcher

not a good approach to change datatype just for formatting as it will make date manipulations difficult

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

It's not for formatting, it's for comparison. Personally I use it all the time:

where CONVERT(varchar(20),yourdate,112) = '20100423'

Usually the date is passed in as a paramter within mystored proc. Sure, a between would work but this is so much simpler.

I would never advocate changing the datatype within the database if that's what your implying. And, if it's for presentation, it should be done at the application level. Datetime is datetime for a reason at the database level.

Terry

-- The problem with socialism is that you eventually run out of other people’s money. -- Margaret Thatcher

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 14:03:38

quote:
Originally posted by tosscrosby

quote:
Originally posted by visakh16

quote:
Originally posted by tosscrosby

something like: CONVERT(varchar(20),yourdate,112)

Note, it will not include the "-", simply yyyymmdd

Terry

-- The problem with socialism is that you eventually run out of other people’s money. -- Margaret Thatcher

not a good approach to change datatype just for formatting as it will make date manipulations difficult

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

It's not for formatting, it's for comparison. Personally I use it all the time:

where CONVERT(varchar(20),yourdate,112) = '20100423'

Usually the date is passed in as a paramter within mystored proc. Sure, a between would work but this is so much simpler.

I would never advocate changing the datatype within the database if that's what your implying. And, if it's for presentation, it should be done at the application level. Datetime is datetime for a reason at the database level.

Terry

-- The problem with socialism is that you eventually run out of other people’s money. -- Margaret Thatcher

ok even in your case what if you need all data greater than a particular date value? will you use the same approach then too?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-23 : 14:24:49

comparing differences in number of days will not consider time part at all. Use

WHERE datediff(dd, dttmcolumn, '2010-04-23') = 0

for matching records. all of the following sql will return 0.


select datediff(dd, '2010-04-23 00:00:00.000','2010-04-23 23:59:59.99');
select datediff(dd, '2010-04-23 00:00:00.000','2010-04-23');
select datediff(dd, '2010-04-23','2010/04/23 15:32:23.00');

since the question is asking how to compare the date, why worry about the format?

quote:
Originally posted by visakh16

quote:
Originally posted by hanbingl

use
datediff(dd, startdate, enddate)
for compare.

quote:
Originally posted by pgmr1998

I have a field that is date/time defined, but i need to compare only the date - format yyyy-mm-dd. how can i pull just the date part and not the time?

how do you use datediff for comparison? what will you compare above to?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 14:34:05

thats fine. but it wont use an available index if present on any of included date fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-23 : 14:42:02

then datediff is very bad.

quote:
Originally posted by visakh16

thats fine. but it wont use an available index if present on any of included date fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2010-04-23 : 14:54:47

quote:
Originally posted by visakh16

quote:
Originally posted by tosscrosby

quote:
Originally posted by visakh16

quote:
Originally posted by tosscrosby

something like: CONVERT(varchar(20),yourdate,112)

Note, it will not include the "-", simply yyyymmdd

Terry

-- The problem with socialism is that you eventually run out of other people’s money. -- Margaret Thatcher

not a good approach to change datatype just for formatting as it will make date manipulations difficult

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

It's not for formatting, it's for comparison. Personally I use it all the time:

where CONVERT(varchar(20),yourdate,112) = '20100423'

Usually the date is passed in as a paramter within mystored proc. Sure, a between would work but this is so much simpler.

I would never advocate changing the datatype within the database if that's what your implying. And, if it's for presentation, it should be done at the application level. Datetime is datetime for a reason at the database level.

Terry

-- The problem with socialism is that you eventually run out of other people’s money. -- Margaret Thatcher

ok even in your case what if you need all data greater than a particular date value? will you use the same approach then too?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sure, why not? What is wrong with this approach? Enlighten me, I'm always willing to see something better or be shown when I'm wrong. I'm here to learn and to provide anwers whenever I can. If I am incorrect, please show what's the correct approach, for my benefit, but especially the OP.

Terry

-- The problem with socialism is that you eventually run out of other people’s money. -- Margaret Thatcher

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 15:15:51

quote:
Originally posted by tosscrosby

Sure, why not? What is wrong with this approach? Enlighten me, I'm always willing to see something better or be shown when I'm wrong. I'm here to learn and to provide anwers whenever I can. If I am incorrect, please show what's the correct approach, for my benefit, but especially the OP.

Terry

-- The problem with socialism is that you eventually run out of other people’s money. -- Margaret Thatcher

One thing is it depends on format of how date values are passed through parameter or hardcoded in query. so if this is in a procedure and being called by an appln where you cant guarantee the date format then this can give wrong results else you've to standardise format in all the applications which calls this before sending value to this.

see example below


DECLARE @Table table
(
Dt datetime
)

INSERT @Table
SELECT '2010-02-01 13:10' UNION ALL
SELECT '2010-01-30 03:10' UNION ALL
SELECT '2010-03-30 14:24' UNION ALL
SELECT '2010-04-03 12:15' UNION ALL
SELECT '2009-08-14 16:40' UNION ALL
SELECT '1999-12-30 07:36' UNION ALL
SELECT '2009-10-01 05:34'

--query1
SELECT * FROM @Table WHERE CONVERT(varchar(10),Dt,112) >= '2010-01-30' 
--query2
SELECT * FROM @Table WHERE CONVERT(varchar(10),Dt,112) >= '01/30/2010' 
--query3
SELECT * FROM @Table WHERE DATEADD(dd,DATEDIFF(dd,0,Dt),0) >= '2010-01-30' 
--query4
SELECT * FROM @Table WHERE DATEADD(dd,DATEDIFF(dd,0,Dt),0) >= '01/30/2010' 

output
-----------------------------
query1
-------------
Dt
2010-02-01 13:10:00.000
2010-01-30 03:10:00.000
2010-03-30 14:24:00.000
2010-04-03 12:15:00.000


query2
---------------
Dt
2010-02-01 13:10:00.000
2010-01-30 03:10:00.000
2010-03-30 14:24:00.000
2010-04-03 12:15:00.000
2009-08-14 16:40:00.000
1999-12-30 07:36:00.000
2009-10-01 05:34:00.000

query3
---------------
Dt
2010-02-01 13:10:00.000
2010-01-30 03:10:00.000
2010-03-30 14:24:00.000
2010-04-03 12:15:00.000

query4
----------------
Dt
2010-02-01 13:10:00.000
2010-01-30 03:10:00.000
2010-03-30 14:24:00.000
2010-04-03 12:15:00.000

Another thing is it has to do an unnecessary convertion which is dispensed with in the case of webfred's suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-04-23 : 17:23:01

quote:
Originally posted by tosscrosby
What is wrong with this approach? Enlighten me

Using CONVERT Date to String is, by my tests, 5.6 times slower than a native DateTime function like DATEADD

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=38940#120953

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-04-24 : 21:44:49

In response to Terry's version, there at two problems with your approach. First, you are using a function to convert a columns value which will exclude the use of any indexes on that column. Second, you are now comparing strings which can give you wrong results if the strings are not formatted the same way.

Generally, the preferred approach to evaluating dates is to use an open interval. Here is an example:

DECLARE @fromDate datetime;
DECLARE @toDate datetime;

SET @fromDate = '20100401';
SET @toDate = DATEADD(day, 1, @fromDate);

-- The above will give us 2010-04-01 00:00:00.000 for the from date and 2010-04-02 00:00:00.000 as the to date

SELECT {your columns}
FROM {your tables}
WHERE yourDateColumn >= @fromDate
AND yourDateColumn < @toDate;

-- the above will use an index on yourDateColumn (if it exists) and will include all times in the date range

Your version precludes the use of any indexes and uses string comparison which is considerably slower than a date comparison.

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources