Getting the date portion of a datetime value

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

Old Forums

CLOSED - General SQL Server

Getting the date portion of a datetime value

Author

Topic

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-02 : 06:37:45

Is there an easier way than using a series of datepart statements to extract the date portion out of....

2005-06-01 15:37:30.087

All the date fields in the tables I am looking at include the time. I need the date bit.

Thinking as I type is left('2005-06-01 15:37:30.087', 9) untidy?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-06-02 : 06:40:54

select convert(varchar(10),'2005-06-01 15:37:30.087', 101)

--------------------
keeping it simple...

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-02 : 06:47:36

But can this be used in a where clause to compare dates as in...

where <datefromdb> >= convert(varchar(10),'2005-06-01 15:37:30.087', 101)

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-02 : 06:51:48

Or rather...

where convert(varchar(10),tran_date, 101) >= '2005-06-01'

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-06-02 : 06:55:52

why not use datediff to compare the dates?

datediff(day,@d1,@d2)=0 or whatever your condition is

--------------------
keeping it simple...

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-02 : 07:59:59

But that only deals with part of the date, the day, month etc?

I need to compare dates to be equal or greater than.

For example if I wanted to see all data for dates greater or equal to 01/01/2005

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-06-02 : 08:05:39

DatePart() deals with month, day, etc., DateDiff() will compare two dates and count the number of intervals between them.

If you want to select rows with a date greater than 1/1/05, then this works:

SELECT * FROM myTable WHERE dateCol>='1/1/2005'

The time portion is immaterial. You can also use DateAdd() to truncate the time portion like so:

SELECT DateAdd(day, DateDiff(day, 0, dateCol), 0) FROM myTable

Here, DateDiff counts the number of days since 0 (Jan. 1, 1900) and then DateAdd adds that number of days to zero, thus giving you the date at midnight (essentially, no time value).

You can use DateAdd in this fashion to get any kind of date interval, weeks, months, years, etc. It's actually the easiest and most flexible method for this kind of work.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-02 : 08:11:41

Try this
Select * from yourTable where datecol>='01-Jan-2005'

Madhivanan

Failing to plan is Planning to fail

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-02 : 08:17:30

This works a treat. I do believe the ole american style date got me!

SELECT * FROM myTable WHERE dateCol>='6/1/2005'

rather than

SELECT * FROM myTable WHERE dateCol>='1/6/2005'

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-02 : 08:58:48

Also, consider:

http://weblogs.sqlteam.com/jeffs/archive/2004/12/02/2954.aspx

I personally recommend against storing times with dates for any column that is often filtered by date only.

- Jeff

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-02 : 09:03:51

Unfortunately I have no power of this!

The tables are on our corporate system and all I can do is query them, the data gets written to the tables from the application we bought!

robdog09
Starting Member

1 Post

Posted - 2006-06-08 : 15:36:56

I had the same problem. I had two date fields, StartDate and EndDate. I wanted to find if getDate() was between them, BUT if StartDate and EndDate are updated at the same time AS the same date, then they both would be (i.e.) '6/8/2006 15:36:061'. This will make a 'getdate() between StartDate and EndDate' fail. So, I made a UDF that extracts just the date portion and returns a datetime type with 00:00:00 as time

CREATE FUNCTION udf_JustTheDate
(@d datetime) --returns just the date with no time
RETURNS datetime AS
BEGIN
return cast(convert(varchar,@d,110) as datetime)
END

Then my query...
select * from tblSchedule where
dbo.udf_JustTheDate(getdate()) >= dbo.udf_JustTheDate(StartDate) and
dbo.udf_JustTheDate(getdate()) <= dbo.udf_JustTheDate(EndDate)

probably inefficient to go from a date -> varchar -> date, but whatever

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-08 : 17:39:00

This is a much faster way to get the date without time:
dateadd(dd,datediff(dd,0,@DAY),0)

It is used in the F_START_OF_DAY function on the link below. F_START_OF_DAY will return what you are looking for.

Start of Time Period Functions:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755

There is other info about date converions here.
Date/Time Info and Script Links
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources