getting the most latest date

I have a column which stores dates (datetime data type). I would like
to fetch one data which is the most latest date among them. So if there
are 04/01/06, 05/08/06, 05/12/06, 06/15/06, then 06/15/06 is the one I
need to output.

how can I write this in sql stmt?
select datebegin
from testtable
where datebegin = ???

I don't think it is simple as I thought. I have no idea what I need to
write in where clause to make this work.
thanks.select MAX(datebegin) from testtable

TGEAR wrote:
> I have a column which stores dates (datetime data type). I would like
> to fetch one data which is the most latest date among them. So if there
> are 04/01/06, 05/08/06, 05/12/06, 06/15/06, then 06/15/06 is the one I
> need to output.
> how can I write this in sql stmt?
> select datebegin
> from testtable
> where datebegin = ???
> I don't think it is simple as I thought. I have no idea what I need to
> write in where clause to make this work.
> thanks.sql

getting the latest datetime

Hi

I have a DateTime column in my table, and I want to select the first couple of rows whose DateTime values are closest to the currect serve rdatetime. Is there a function like Max() that I can use?

Also, quesion about timestamp. If I create a column of type timestamp, is the value automatically entered by the database on every insert entry to the table?
I have had problems using timestamp so I switched to using datetime now.

Help appreciated!For the first part of your question, I would use the DATEDIFF() function to find the difference between the current date (GETDATE()) and the date value stored in your column, sorted in DESC order, taking the TOP 1 or 2 (however many you want).

The Timestamp data type is actually a unique rowversion containing binary/varbinary data, and its value only has meaning to SQL Server itself. SQL Server updates the value in this column upon every INSERT, UPDATE, and DELETE. You could make use of this Timestamp value for concurrency control, but it does not contain datetime data -- it contains binary/varbinary data.

Terri

Getting rid of the time value from a datetime field

I want to get rid of the time stamp in a datetime field and also i need to include that in a group by query.

For Ex:

select join_dt, avg(salary) from employee
group by join_dt

Here in the above example, join_dt is a datetime field, i need some function similar datepart which gives only the date part of the join_dt.you can use the function DATEPART. See Books Online for more information.|||I think you just answered you own question...

why don't u use datepart?

you could do something like this:

select join_dt, avg(salary) from employee
group by datepart("datepartchoice",join_dt)

or are u trying to say something else?|||@.Patrick: hopefully you are not insulted by this remark, but didn't I post just the same answer as you did?|||No offence taken, I was just wondering what was getbabs really asking, since his question was already containing the answer :

getbabs>>i need some function similar datepart which gives only the date part of the join_dt.

:)|||I am looking for a formatted date ("dd/mm/yy") from a datetime field
and group by the same.|||select convert(varchar(12), getDate(), 6) where the 6 stands for the format used. Look under CONVERT in Books Online for the formats.|||i think the real problem here is why does join_dt have a time component

i could see the need for a time component in other datetime fields, but not this one

if you need to use DATEPART in order to use GROUP BY on the date portion only, this a sure sign that you have been manouevered into a very bad situation by poor design or coding

using DATEPART usually rules out indexes, and the query will perform poorly

do yourself a favour, and reset the time portion of all the join_dt to midnight

then you can go ahead and write simple queries like this --

select join_dt, avg(salary) from employee
group by join_dt

;)|||As an extension to Rudy's post, you could consider an additional time column if time is really required. Update the time column with the time component from the join_dt field and then blank the join_dt field to midnight. Having said that, I agree with him inasmuch as what's a time doing in a join date?

Getting records between the specified date range

I have a table with two columns.
two columns are of datetime datatype
I have two records in the table
2/1/2004 12/30/2004
2/1/2004 6/30/2004
I need to retreive the record from the range 2/1/2004 to 7/15/2004. When I
use the < and > symbol. it is not working with the datetime feild.
Can anyone help me how to retreive those records in the specified date range
.
Thanks in Advance
- VigneshUse the BETWEEN operator.
ex. WHERE somedate BETWEEN '02/01/04' AND '07/15/04'
Jeff Lynch
"A BizTalk Enthusiast"
http://dotnetjunkies.com/WebLog/jlynch/
"Vignesh" <Vignesh@.discussions.microsoft.com> wrote in message
news:F248183B-DC8D-4D43-8198-BB3849864193@.microsoft.com...
>I have a table with two columns.
> two columns are of datetime datatype
> I have two records in the table
> 2/1/2004 12/30/2004
> 2/1/2004 6/30/2004
> I need to retreive the record from the range 2/1/2004 to 7/15/2004. When I
> use the < and > symbol. it is not working with the datetime feild.
> Can anyone help me how to retreive those records in the specified date
> range.
> Thanks in Advance
> - Vignesh
>|||First, does both colomns need to be within the date range, or just one?
Using the follow DDL's
create table lookup(date1 datetime, date2 datetime)
insert lookup values ('2004-2-1','2004-12-30')
insert lookup values ('2004-2-1','2004-6-30')
--If both between dates
select *
from lookup
where date1 between '2004-2-1' and '2004-7-15'
and date2 between '2004-2-1' and '2004-7-15'
--If either between dates
select *
from lookup
where date1 between '2004-2-1' and '2004-7-15'
or date2 between '2004-2-1' and '2004-7-15'|||BETWEEN works well with one column and with two dates.
but I have only one date for both the column.
col1 > somedate1 and col2 < somedate2
having the column name as startdate and enddate to get more clarity.
I need to retreive the records from the specified startdate till the
specified end date.
Can this be acheived?
Thanks
- Vignesh.
"Jeff Lynch" wrote:

> Use the BETWEEN operator.
> ex. WHERE somedate BETWEEN '02/01/04' AND '07/15/04'
> --
> Jeff Lynch
> "A BizTalk Enthusiast"
> http://dotnetjunkies.com/WebLog/jlynch/
>
> "Vignesh" <Vignesh@.discussions.microsoft.com> wrote in message
> news:F248183B-DC8D-4D43-8198-BB3849864193@.microsoft.com...
>
>|||If you try to represent a date as a string and leave off the hh:mm:ss
portion, it is assumed 00:00:00. So, 12/30/2004 03:30pm is not between
2/1/2004 and 12/30/2004, becuase it is greater than "12/30/2004".
"Vignesh" <Vignesh@.discussions.microsoft.com> wrote in message
news:F248183B-DC8D-4D43-8198-BB3849864193@.microsoft.com...
> I have a table with two columns.
> two columns are of datetime datatype
> I have two records in the table
> 2/1/2004 12/30/2004
> 2/1/2004 6/30/2004
> I need to retreive the record from the range 2/1/2004 to 7/15/2004. When I
> use the < and > symbol. it is not working with the datetime feild.
> Can anyone help me how to retreive those records in the specified date
range.
> Thanks in Advance
> - Vignesh
>|||
hi u can try
this one
suppose u want to retrive record between 02/02/2005 to 04/02/2005
select *
from tablename
where
datediff(dd,02/02/2005,ur_datetime_column)>=0
and
datediff(dd,ur_datetime_column,04/02/2005)>=0
Message posted via http://www.webservertalk.com

Getting last (newest) one record (datetime column or id)

Hello everybody,

---------------

CREATE TABLE [T1] (
[IDX] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[DateEvt] [datetime] NOT NULL,
[Value] [varchar] (10) NOT NULL ,
[DataX] [varchar] (10) NULL ,
CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED
(
[IDX]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:00',
'0000000001', 'AAAAAAAAAA')
insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:01',
'0000000002', 'AAAAAAAAAA')
insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:02',
'0000000003', 'AAAAAAAAAA')
insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:01:00',
'0000000001', 'BBBBBBBBBB')
insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:02:00',
'0000000001', 'CCCCCCCCCC')
insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:03:00',
'0000000001', 'DDDDDDDDDD')
GO

---------------

and the question is:
In which fastes and best for the preformance way, get the last IDX of
specified Value.

I could do this like this:

---------------
declare @.nIDX numeric
declare @.sValue varchar(10)

select top 1 @.nIDX = IDX from T1
where Value = @.sValue
order by DateEVT desc
---------------

But I know, this is not fast (even if I have index on DateEVT field),
and I'm quite sure, that there is better way to get this IDX.
Anyway, this table can be big (like 20 milions records).

I could take the max of IDX, but is it a sure way?
Any help? Thanks in advance

Matik> declare @.nIDX numeric
> declare @.sValue varchar(10)
> select top 1 @.nIDX = IDX from T1
> where Value = @.sValue
> order by DateEVT desc

To optimize this query, consider creating a non-clustered index on Value and
using MAX. This index will cover the query becuase the clustered index
value (IDX) is also stored in the non-clustered index.

DECLARE @.nIDX numeric
DECLARE @.sValue varchar(10)
SELECT @.nIDX = MAX(IDX)
FROM T1
WHERE Value = @.sValue

Also, if you are using SQL 2000, consider bigint instead of numeric(18, 0).
This will save a little space.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Matik" <marzec@.sauron.xo.pl> wrote in message
news:1102374422.534762.91540@.f14g2000cwb.googlegro ups.com...
> Hello everybody,
> ---------------
> CREATE TABLE [T1] (
> [IDX] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [DateEvt] [datetime] NOT NULL,
> [Value] [varchar] (10) NOT NULL ,
> [DataX] [varchar] (10) NULL ,
> CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED
> (
> [IDX]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:00',
> '0000000001', 'AAAAAAAAAA')
> insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:01',
> '0000000002', 'AAAAAAAAAA')
> insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:02',
> '0000000003', 'AAAAAAAAAA')
> insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:01:00',
> '0000000001', 'BBBBBBBBBB')
> insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:02:00',
> '0000000001', 'CCCCCCCCCC')
> insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:03:00',
> '0000000001', 'DDDDDDDDDD')
> GO
> ---------------
> and the question is:
> In which fastes and best for the preformance way, get the last IDX of
> specified Value.
> I could do this like this:
> ---------------
> declare @.nIDX numeric
> declare @.sValue varchar(10)
> select top 1 @.nIDX = IDX from T1
> where Value = @.sValue
> order by DateEVT desc
> ---------------
> But I know, this is not fast (even if I have index on DateEVT field),
> and I'm quite sure, that there is better way to get this IDX.
> Anyway, this table can be big (like 20 milions records).
> I could take the max of IDX, but is it a sure way?
> Any help? Thanks in advance
> Matik

getting just the date or from DateTime field

Hi,
I have used smalldatetime datatype to store my date and time values. i want to store just the date or time but the problem is it stores both the date and time. For eg, if i add the the date 03/11/2004, it also the stores the current time automatically. so the new value will be something like 03/11/2004 10:00:00 AM where i want just 03/11/2004. further problem is even though i managed to store just the date like 03/11/2004 in the database, whole date and time shows up when i display it in my pages.

any help will be appreciated.

thanx,If i want to display just the date portion I use the following.
This is just using a datareader then putting the date in a label

 While drDate.Read()
 lblSent.Text = Format(drDate("datesent_column"), "dd/MM/yyyy")
 End While

If you are in the US you might use "MM/dd/yyyy" instead of our aussie way.
It doesn't matter then if the whole date and time is in the column.
Hope this helps
Pete|||You should just use the more generic "short date formats" rather than hard-coding the format, but the general idea is sound.|||hi pkr,
even though i use short date formats, they will be in long date format when saved in database. i have used shortdatetime datatype in database as well.

seems like i need to format it again while displaying. is that the only solution?

thanx,|||SQL Server *only* supports date AND time. There's no getting around that fact, well not really anyway. My posting was talking about using the generic formats for rendering the values not storing them. You should always strive to use the users date formatting not impose one for them.

Getting Just The Date

This sounds stupid, but I cannot find a way to just get today's date without
the time to pull a date range...
I have tried convert (datetime, GetDate(), 112) and some others, all to no
avail... isn't there just a date function that doesn't include the time?
Try,
select convert (varchar(12), GetDate(), 101)
"Atley" <atley_1@.homtmail.com> wrote in message
news:%2315icKpEEHA.2640@.TK2MSFTNGP09.phx.gbl...
> This sounds stupid, but I cannot find a way to just get today's date
without
> the time to pull a date range...
> I have tried convert (datetime, GetDate(), 112) and some others, all to no
> avail... isn't there just a date function that doesn't include the time?
>
>
|||convert (char(10), GetDate(), 111)
"Atley" <atley_1@.homtmail.com> wrote in message
news:%2315icKpEEHA.2640@.TK2MSFTNGP09.phx.gbl...
> This sounds stupid, but I cannot find a way to just get today's date
without
> the time to pull a date range...
> I have tried convert (datetime, GetDate(), 112) and some others, all to no
> avail... isn't there just a date function that doesn't include the time?
>
>
|||You need to use a convert and convert it into a string. There is no date-only datatype in SQL Server. You can
of course have a datetime with time 00:00:00, but there's still a time portion.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Atley" <atley_1@.homtmail.com> wrote in message news:%2315icKpEEHA.2640@.TK2MSFTNGP09.phx.gbl...
> This sounds stupid, but I cannot find a way to just get today's date without
> the time to pull a date range...
> I have tried convert (datetime, GetDate(), 112) and some others, all to no
> avail... isn't there just a date function that doesn't include the time?
>
>
|||> I have tried convert (datetime, GetDate(), 112) and some others, all to no
> avail...
How exactly did you try this? What does "to no avail" mean?
Here is how I would retrieve all the rows that have a datetime within today.
DECLARE @.dt SMALLDATETIME
SET @.dt = {fn CURDATE()}
SELECT cols
FROM tbl
WHERE dtCol >= @.dt
AND dtCol < (@.dt + 1)

> isn't there just a date function that doesn't include the time?
SELECT {fn CURDATE()}
SELECT CONVERT(SMALLDATETIME,
CONVERT(CHAR(8), GETDATE(), 112))
Why is this posted to clients and datamining? Follow-ups adjusted.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/