DateTime Data Type

Hi,
I am new to SQL Server, I am using SQL Server 2000, I want to store only
date (not time) in my table, is it possible in sql , is there any data types
except datetime and smalldatetime.
Thanks in advance.
Hardik Shah.Hardik Shah wrote:
> Hi,
> I am new to SQL Server, I am using SQL Server 2000, I want to store
> only date (not time) in my table, is it possible in sql , is there
> any data types except datetime and smalldatetime.
>
> Thanks in advance.
>
> Hardik Shah.
No, you need to store a time portion as well. You can set it to 12am if
you want. Use a smalldatatime if you don't require full time precision
as it's only 4 bytes instead of 8.
David Gugick
Imceda Software
www.imceda.com|||No. Sql2k does not have date only datatype.
Here is Tibor's excellent article on the subject. I would suggest you take a
quick look.
http://www.karaszi.com/sqlserver/info_datetime.asp
-oj
"Hardik Shah" <har_sha_99@.hotmail.com> wrote in message
news:OvHsm9NTFHA.2560@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am new to SQL Server, I am using SQL Server 2000, I want to store only
> date (not time) in my table, is it possible in sql , is there any data
> types
> except datetime and smalldatetime.
>
> Thanks in advance.
>
> Hardik Shah.
>|||No and no to both questions.
When you write a date to the column, you can save it as so:
'20050429'
The time part will be '00:00:00.000'
So a query: ...where dateColumn = '20050429'
will return that row.
You must be coming from Access, right?
"Hardik Shah" <har_sha_99@.hotmail.com> wrote in message
news:OvHsm9NTFHA.2560@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am new to SQL Server, I am using SQL Server 2000, I want to store only
> date (not time) in my table, is it possible in sql , is there any data
> types
> except datetime and smalldatetime.
>
> Thanks in advance.
>
> Hardik Shah.
>|||By default, no. However, there are numerous tricks to accomplish the same go
al.
1. Use a smallInt instead of a datetime or smalldatetime. That simply means
you
have to do a bunch of casting back and forth to get data out of the table.
Another downside to this approach is that it is not obvious to other develop
ers
that this is what you are doing.
2. Use a datetime/smalldatetime and put a check constraint on the table that
requires all values have zero for the time element. That would require that
the
client code strip the time portion before it passes it to the database lest
it
get an error.
3. Use a datetime/smalldatetime and put an Instead Of trigger on the table t
hat
strips the time portion before the value is entered.
4. Let the time portion be stored, but ignore it or strip it for purposes of
retrieval. It does mean you have to be a bit more careful with your queries,
but
it obviously simplifies table structure. In addition, if you are following a
modular design and have all your database calls in a central location, you c
an
strip the time portion in your database calls.
HTH
Thomas
"Hardik Shah" <har_sha_99@.hotmail.com> wrote in message
news:OvHsm9NTFHA.2560@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am new to SQL Server, I am using SQL Server 2000, I want to store only
> date (not time) in my table, is it possible in sql , is there any data typ
es
> except datetime and smalldatetime.
>
> Thanks in advance.
>
> Hardik Shah.
>