| Author |
 Topic  |
|
|
hog
Constraint Violating Yak Guru
United Kingdom
284 Posts |
 Posted - 06/02/2005 : 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
Flowing Fount of Yak Knowledge
Sweden
4110 Posts |
Posted - 06/02/2005 : 06:40:54
|
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 |
 |
|
|
hog
Constraint Violating Yak Guru
United Kingdom
284 Posts |
Posted - 06/02/2005 : 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
United Kingdom
284 Posts |
Posted - 06/02/2005 : 06:51:48
|
Or rather...
where convert(varchar(10),tran_date, 101) >= '2005-06-01' |
|
|
|
jen
Flowing Fount of Yak Knowledge
Sweden
4110 Posts |
Posted - 06/02/2005 : 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
United Kingdom
284 Posts |
Posted - 06/02/2005 : 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
USA
15559 Posts |
Posted - 06/02/2005 : 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
India
22461 Posts |
Posted - 06/02/2005 : 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
United Kingdom
284 Posts |
Posted - 06/02/2005 : 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
USA
7423 Posts |
|
|
hog
Constraint Violating Yak Guru
United Kingdom
284 Posts |
Posted - 06/02/2005 : 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
USA
1 Posts |
Posted - 06/08/2006 : 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)
USA
6997 Posts |
|
| |
Topic |
|
Getting the date portion of a datetime value
Forum Locked
