SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Getting the date portion of a datetime value
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

hog
Constraint Violating Yak Guru

United Kingdom
284 Posts

Posted - 06/02/2005 :  06:37:45  Show Profile
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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 06/02/2005 :  06:40:54  Show Profile  Send jen a Yahoo! Message
select convert(varchar(10),'2005-06-01 15:37:30.087', 101)

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

Edited by - jen on 06/02/2005 06:42:05
Go to Top of Page

hog
Constraint Violating Yak Guru

United Kingdom
284 Posts

Posted - 06/02/2005 :  06:47:36  Show Profile
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)
Go to Top of Page

hog
Constraint Violating Yak Guru

United Kingdom
284 Posts

Posted - 06/02/2005 :  06:51:48  Show Profile
Or rather...

where convert(varchar(10),tran_date, 101) >= '2005-06-01'
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 06/02/2005 :  06:55:52  Show Profile  Send jen a Yahoo! Message
why not use datediff to compare the dates?

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

--------------------
keeping it simple...
Go to Top of Page

hog
Constraint Violating Yak Guru

United Kingdom
284 Posts

Posted - 06/02/2005 :  07:59:59  Show Profile
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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15680 Posts

Posted - 06/02/2005 :  08:05:39  Show Profile  Visit robvolk's Homepage
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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 06/02/2005 :  08:11:41  Show Profile  Send madhivanan a Yahoo! Message
Try this
Select * from yourTable where datecol>='01-Jan-2005'

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

hog
Constraint Violating Yak Guru

United Kingdom
284 Posts

Posted - 06/02/2005 :  08:17:30  Show Profile
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'
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 06/02/2005 :  08:58:48  Show Profile  Visit jsmith8858's Homepage
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
Go to Top of Page

hog
Constraint Violating Yak Guru

United Kingdom
284 Posts

Posted - 06/02/2005 :  09:03:51  Show Profile
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!
Go to Top of Page

robdog09
Starting Member

USA
1 Posts

Posted - 06/08/2006 :  15:36:56  Show Profile  Send robdog09 an AOL message
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/08/2006 :  17:39:00  Show Profile

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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.34 seconds. Powered By: Snitz Forums 2000