sql server 2000 datetime data type question

14 Jul 2005 3:46 PM

Wendy Elizabeth

I just started to work with sql server 2000. I want to write a query

against "datetime" columns in the sql server 2000 database. I am to be

able to do the following with the sql server 2000 "datetime" columns:

1. Be able to access the date portion only.
2. Be able to access the time portion only.
3. Be able to access only a portion of the date like the month and/or

year only.
4. Be able to access only a portion of the time like the milliseconds

and/or the minutes.

What kind of query statements would I need to write, to be able to

access only a portion of "datetime" columns?

Thanks!

14 Jul 2005 3:51 PM

Alejandro Mesa

See functions datepart, datename, day, month, year and convert in BOL.

AMB

Show quote

"Wendy Elizabeth" wrote:

> I just started to work with sql server 2000. I want to write a query
>
> against "datetime" columns in the sql server 2000 database. I am to be
>
> able to do the following with the sql server 2000 "datetime" columns:
>
> 1. Be able to access the date portion only.
> 2. Be able to access the time portion only.
> 3. Be able to access only a portion of the date like the month and/or
>
> year only.
> 4. Be able to access only a portion of the time like the milliseconds
>
> and/or the minutes.
>
> What kind of query statements would I need to write, to be able to
>
> access only a portion of "datetime" columns?
>
> Thanks!
>

14 Jul 2005 4:01 PM

Wendy Elizabeth

Thank you for your immediate response. However, I do not know what "convert
in BOL" means. What does "convert in BOL" mean?

Thanks!

Show quote

"Alejandro Mesa" wrote:

> See functions datepart, datename, day, month, year and convert in BOL.
>
>
> AMB
>
> "Wendy Elizabeth" wrote:
>
> > I just started to work with sql server 2000. I want to write a query
> >
> > against "datetime" columns in the sql server 2000 database. I am to be
> >
> > able to do the following with the sql server 2000 "datetime" columns:
> >
> > 1. Be able to access the date portion only.
> > 2. Be able to access the time portion only.
> > 3. Be able to access only a portion of the date like the month and/or
> >
> > year only.
> > 4. Be able to access only a portion of the time like the milliseconds
> >
> > and/or the minutes.
> >
> > What kind of query statements would I need to write, to be able to
> >
> > access only a portion of "datetime" columns?
> >
> > Thanks!
> >

14 Jul 2005 4:05 PM

David Gugick

BOL = Book on Line - or the SQL Server Help File... It's just shorthand
we use around here to avoid a lot of typing. You can research all the
functions Alejandro mentioned in the help file included with SQL Server.

--
David Gugick
Quest Software
www.imceda.com
www.quest.com

14 Jul 2005 4:27 PM

Aaron Bertrand [SQL Server MVP]

> Thank you for your immediate response. However, I do not know what
> "convert
> in BOL" means. What does "convert in BOL" mean?

See functions datepart, datename, day, month, year and convert -- all of
which are listed in BOL.

BOL is Books Online, the essential documentation for SQL Server.
http://www.aspfaq.com/2229

14 Jul 2005 4:13 PM

Tibor Karaszi

I have an elaboration about searching for date only here:
http://www.karaszi.com/SQLServer/info_datetime.asp
The article also contains a lot of "must know" about the datetime datatype in SQL Server.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"Wendy Elizabeth" <WendyElizab***@discussions.microsoft.com> wrote in message
news:E3318084-2CAB-4465-A3D0-7C07DB80C4BE@microsoft.com...
>I just started to work with sql server 2000. I want to write a query
>
> against "datetime" columns in the sql server 2000 database. I am to be
>
> able to do the following with the sql server 2000 "datetime" columns:
>
> 1. Be able to access the date portion only.
> 2. Be able to access the time portion only.
> 3. Be able to access only a portion of the date like the month and/or
>
> year only.
> 4. Be able to access only a portion of the time like the milliseconds
>
> and/or the minutes.
>
> What kind of query statements would I need to write, to be able to
>
> access only a portion of "datetime" columns?
>
> Thanks!
>

14 Jul 2005 4:37 PM

Aaron Bertrand [SQL Server MVP]

> 1. Be able to access the date portion only.

(a) use range queries, this will work best if you have an index on the
column.

e.g. instead of
WHERE dt = '20050714'
you say
WHERE dt >= '20050714' AND dt < '20050715'

See http://www.aspfaq.com/2280

(b) store the date only with a midnight time stamp, if time is irrelevant.

See http://www.aspfaq.com/2206

(c) you can use convert, but this will negate any indexes, e.g.
WHERE CONVERT(CHAR(8), dt, 112) = '20050714'

CONVERT is documented in Books Online.

> 2. Be able to access the time portion only.

See the thread from yesterday in this group, "Number of records for Month X,
created between 7AM and Noon"

> 3. Be able to access only a portion of the date like the month and/or year
> only.
> 4. Be able to access only a portion of the time like the milliseconds
> and/or the minutes.

Use DATEPART or DATENAME. These are documented in Books Online.

Aaron