get time in SQL server

Hi ,
i am trying to get the time as well after the
conversion to datetime data type but could not do it.
is it possible to do so ?
declare @.date1 as string
set date1 = '12/5/2004 23:59:59'
declare @.date2 as datetime
set @.date2 = convert(@.date1) but it always return '12-05-
2004 00:00:00:000' which i want the time to be 23:59:59
thks & rdgs
max
SELECT CONVERT(CHAR(10),GETDATE(),108)
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:04c201c493f3$38c447a0$a401280a@.phx.gbl...
> Hi ,
> i am trying to get the time as well after the
> conversion to datetime data type but could not do it.
> is it possible to do so ?
> declare @.date1 as string
> set date1 = '12/5/2004 23:59:59'
> declare @.date2 as datetime
> set @.date2 = convert(@.date1) but it always return '12-05-
> 2004 00:00:00:000' which i want the time to be 23:59:59
> thks & rdgs
|||On Mon, 6 Sep 2004 02:23:51 -0700, maxzsim wrote:

>Hi ,
> i am trying to get the time as well after the
>conversion to datetime data type but could not do it.
> is it possible to do so ?
>declare @.date1 as string
>set date1 = '12/5/2004 23:59:59'
>declare @.date2 as datetime
>set @.date2 = convert(@.date1) but it always return '12-05-
>2004 00:00:00:000' which i want the time to be 23:59:59
>thks & rdgs
Hi Maxzsim,
Is this is SQL Server question or an Access question? You posted in a SQL
Server group, but your statements has some syntax elements that raise
syntax errors on SQL Server (and that look familiar from an Access point
of view): "as" in a declare statement, datatype "string" and "convert"
with only one argument are all illegal in SQL Server.
When I fix the syntax for SQL Server, I get either one of the following.
All of them leave the time part unchanged (ie 23:59:59, as requested).
(1)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = cast (@.date1 as datetime)
select @.date1, @.date2
(2)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = convert (datetime, @.date1)
select @.date1, @.date2
(3)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = @.date1-- implicit conversion
select @.date1, @.date2
Last but not least: the format of your date/time constant is ambiguous. Is
the date part formatted as mm/dd/yyyy or dd/mm/yyyy? Both readings can be
valid. If you want to be sure that SQL Server recognises your date and
time as you intended them, use one of these formats:
* yyyymmdd (for date only; time part will be set to midnight)
* yyyy-mm-ddThh:mm:ss (date plus time; the uppercase T is a constant)
* yyyy-mm-ddThh:mm:ss.mmm (as above, but including milliseconds)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)