MS SQL Server MSEQ - sql server 2000

Author

sql server 2000

Wendy Elizabeth

2005-07-14, 11:23 am

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!

Hari Prasad

2005-07-14, 8:24 pm

Hi,

See Functions DATEPART and CONVERT in books online.

Thanks
Hari
SQL Server MVP

"Wendy Elizabeth" < WendyElizabeth@discu
ssions.microsoft.com> wrote in
message news:2098B955-D0AF-4C4D-A7FC- 930EFC2CB6BC@microso
ft.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!
>

David Portas

2005-07-17, 8:24 pm

For 1 and 3, use date ranges wherever possible so as to avoid complex
expressions and type conversions and to help maximize the benefit of any
indexes. Examples:

One day:

SELECT *
FROM YourTable
WHERE date_col >= '20050717'
AND date_col < '20050718'

One month:

SELECT *
FROM YourTable
WHERE date_col >= '20050701'
AND date_col < '20050801'

For 2 and 4, take a look at the DATEPART function in Books Online.

--
David Portas
SQL Server MVP
--

Sponsored Links