date prolem

I have created a table in which one column is datetime data type. But its
storing date andtimetogether. How can i chane the format to store only dates
in dd-mon-yyyy?
thanks in advanceHi
CREATE TABLE #Test (dt DATETIME)
#1
INSERT INTO #Test SELECT CONVERT(VARCHAR(15),GETDATE(),112)
#2
INSERT INTO #Test SELECT CAST(FLOOR(CAST(GETDATE()+1 AS FLOAT)) AS DATETIME)
SELECT * FROM #Test
"Rajani" <Rajani@.discussions.microsoft.com> wrote in message
news:37A7B0A2-F126-402D-A0B1-1D70009C45CA@.microsoft.com...
>I have created a table in which one column is datetime data type. But its
> storing date andtimetogether. How can i chane the format to store only
> dates
> in dd-mon-yyyy?
> thanks in advance|||Hi
To expand on Hugo's post. The SQL Server datetime datatype will hold both a
date and time. If you insert a date value with no time portion then the time
will be defaulted to 00:00:00.000 and if you specify only a time the date is
defaulted to
1900-01-01. With SQL Server there is the CONVERT function that will format a
datetime datetype value as a string. This will take a format specifier to
determine what format the string is. The getdate function will return the
current date and time, Hugo's second INSERT statement will use the FLOOR
function to truncate the time portion of a datetime data value, the effect of
adding 1 to getdate will add one day. Therefore his code will add one day to
the current date and time and then truncate the time portion.
See Books Online for more on the datetime data type and the CONVERT function.
John
"Rajani" wrote:
> I have created a table in which one column is datetime data type. But its
> storing date andtimetogether. How can i chane the format to store only dates
> in dd-mon-yyyy?
> thanks in advance|||> To expand on Hugo's post.
:--) sorry may name is Uri
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:63DE3158-5C2F-4598-9CF4-2E9638BBA1C2@.microsoft.com...
> Hi
> To expand on Hugo's post. The SQL Server datetime datatype will hold both
> a
> date and time. If you insert a date value with no time portion then the
> time
> will be defaulted to 00:00:00.000 and if you specify only a time the date
> is
> defaulted to
> 1900-01-01. With SQL Server there is the CONVERT function that will format
> a
> datetime datetype value as a string. This will take a format specifier to
> determine what format the string is. The getdate function will return the
> current date and time, Hugo's second INSERT statement will use the FLOOR
> function to truncate the time portion of a datetime data value, the effect
> of
> adding 1 to getdate will add one day. Therefore his code will add one day
> to
> the current date and time and then truncate the time portion.
> See Books Online for more on the datetime data type and the CONVERT
> function.
> John
>
> "Rajani" wrote:
>> I have created a table in which one column is datetime data type. But its
>> storing date andtimetogether. How can i chane the format to store only
>> dates
>> in dd-mon-yyyy?
>> thanks in advance|||Ooopps!
"Uri Dimant" wrote:
> > To expand on Hugo's post.
> :--) sorry may name is Uri
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:63DE3158-5C2F-4598-9CF4-2E9638BBA1C2@.microsoft.com...
> > Hi
> >
> > To expand on Hugo's post. The SQL Server datetime datatype will hold both
> > a
> > date and time. If you insert a date value with no time portion then the
> > time
> > will be defaulted to 00:00:00.000 and if you specify only a time the date
> > is
> > defaulted to
> > 1900-01-01. With SQL Server there is the CONVERT function that will format
> > a
> > datetime datetype value as a string. This will take a format specifier to
> > determine what format the string is. The getdate function will return the
> > current date and time, Hugo's second INSERT statement will use the FLOOR
> > function to truncate the time portion of a datetime data value, the effect
> > of
> > adding 1 to getdate will add one day. Therefore his code will add one day
> > to
> > the current date and time and then truncate the time portion.
> >
> > See Books Online for more on the datetime data type and the CONVERT
> > function.
> >
> > John
> >
> >
> > "Rajani" wrote:
> >
> >> I have created a table in which one column is datetime data type. But its
> >> storing date andtimetogether. How can i chane the format to store only
> >> dates
> >> in dd-mon-yyyy?
> >>
> >> thanks in advance
>
>