Convert Syntax Error?

Hi!
I have in one sqlserver 2000 table 3 fields with type datetime (8).
when I look at that table with the SQL Query Analyzer:
in field 'time_' --> 16.01.2003 00:00:04
in field 'date_' --> 01.01.2002
in field 'time_date_new' --> 01.01.2002 00:00:04 (I want to have!)
I want with a update statement write the time part from field 'time_' into
the field
'time_date_new' and then the date part from field 'date_' write also into
the field 'time_date_new'
my statement:
UPDATE Anrufe_2003_Test
SET time_date_new = convert(CHAR(11),date_,126) +
convert(CHAR(12),time_,114)
but I get in query analyser a syntax error on converting.
who has an idea?
ThanksAre you 100% certain that the columns are of the datetime datatype? I'm
surprised to the way that the dateformat is represented and also that the
time portion isn't returned for one row. Did you configure QA to honor the
machine's regional settings?
Anyhow, below should work:
DECLARE @.time_ datetime
DECLARE @.date_ datetime
DECLARE @.dt datetime
SET @.time_ = '14:00:05'
SET @.date_ = '2003-10-17'
SELECT @.time_, @.date_
SET @.dt = CONVERT(char(8), @.date_, 112) + ' ' + CONVERT(char(8), @.time_,
108)
SELECT @.dt
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hubert Mayr" <huma1@.gmx.net> wrote in message
news:1068471140.682741@.news.liwest.at...
> Hi!
> I have in one sqlserver 2000 table 3 fields with type datetime (8).
> when I look at that table with the SQL Query Analyzer:
> in field 'time_' --> 16.01.2003 00:00:04
> in field 'date_' --> 01.01.2002
> in field 'time_date_new' --> 01.01.2002 00:00:04 (I want to have!)
>
> I want with a update statement write the time part from field 'time_' into
> the field
> 'time_date_new' and then the date part from field 'date_' write also into
> the field 'time_date_new'
> my statement:
> UPDATE Anrufe_2003_Test
> SET time_date_new = convert(CHAR(11),date_,126) +
> convert(CHAR(12),time_,114)
> but I get in query analyser a syntax error on converting.
> who has an idea?
> Thanks
>|||Hi Tibor!
Columns are datetime (8) datatype. I have a German Sqlserver 2000 Version
running.
Where could I see the machines regional settings in QA?
in field 'time_' --> 16.01.2003 00:00:04
in field 'date_' --> 01.01.2002
the values in that two fiels came from a odbc import from a navision own
database. There is a field with time only and a other field with date only.
when I made with dts a automatic import with the navision odbc driver field
'time_' got the systemdate to the time value.
That way I got these values into sqlserver.
Thanks!
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
schrieb im Newsbeitrag news:Or0E4N5pDHA.2964@.tk2msftngp13.phx.gbl...
> Are you 100% certain that the columns are of the datetime datatype? I'm
> surprised to the way that the dateformat is represented and also that the
> time portion isn't returned for one row. Did you configure QA to honor the
> machine's regional settings?
> Anyhow, below should work:
> DECLARE @.time_ datetime
> DECLARE @.date_ datetime
> DECLARE @.dt datetime
> SET @.time_ = '14:00:05'
> SET @.date_ = '2003-10-17'
> SELECT @.time_, @.date_
> SET @.dt = CONVERT(char(8), @.date_, 112) + ' ' + CONVERT(char(8), @.time_,
> 108)
> SELECT @.dt
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Hubert Mayr" <huma1@.gmx.net> wrote in message
> news:1068471140.682741@.news.liwest.at...
> > Hi!
> >
> > I have in one sqlserver 2000 table 3 fields with type datetime (8).
> > when I look at that table with the SQL Query Analyzer:
> >
> > in field 'time_' --> 16.01.2003 00:00:04
> > in field 'date_' --> 01.01.2002
> > in field 'time_date_new' --> 01.01.2002 00:00:04 (I want to have!)
> >
> >
> > I want with a update statement write the time part from field 'time_'
into
> > the field
> > 'time_date_new' and then the date part from field 'date_' write also
into
> > the field 'time_date_new'
> >
> > my statement:
> >
> > UPDATE Anrufe_2003_Test
> > SET time_date_new = convert(CHAR(11),date_,126) +
> > convert(CHAR(12),time_,114)
> >
> > but I get in query analyser a syntax error on converting.
> > who has an idea?
> >
> > Thanks
> >
> >
>|||> Where could I see the machines regional settings in QA?
Tools, Options, Connections.
However, is you have datetime, then it is most probably fine. Personally, I
don't like it when tools "hides" the time portions just because you have
00:00:00, but I think that this is what it going on.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hubert Mayr" <huma1@.gmx.net> wrote in message
news:1068474028.262656@.news.liwest.at...
> Hi Tibor!
> Columns are datetime (8) datatype. I have a German Sqlserver 2000 Version
> running.
> Where could I see the machines regional settings in QA?
> in field 'time_' --> 16.01.2003 00:00:04
> in field 'date_' --> 01.01.2002
> the values in that two fiels came from a odbc import from a navision own
> database. There is a field with time only and a other field with date
only.
> when I made with dts a automatic import with the navision odbc driver
field
> 'time_' got the systemdate to the time value.
> That way I got these values into sqlserver.
> Thanks!
>
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> schrieb im Newsbeitrag news:Or0E4N5pDHA.2964@.tk2msftngp13.phx.gbl...
> > Are you 100% certain that the columns are of the datetime datatype? I'm
> > surprised to the way that the dateformat is represented and also that
the
> > time portion isn't returned for one row. Did you configure QA to honor
the
> > machine's regional settings?
> >
> > Anyhow, below should work:
> >
> > DECLARE @.time_ datetime
> > DECLARE @.date_ datetime
> > DECLARE @.dt datetime
> > SET @.time_ = '14:00:05'
> > SET @.date_ = '2003-10-17'
> > SELECT @.time_, @.date_
> > SET @.dt = CONVERT(char(8), @.date_, 112) + ' ' + CONVERT(char(8), @.time_,
> > 108)
> > SELECT @.dt
> >
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Hubert Mayr" <huma1@.gmx.net> wrote in message
> > news:1068471140.682741@.news.liwest.at...
> > > Hi!
> > >
> > > I have in one sqlserver 2000 table 3 fields with type datetime (8).
> > > when I look at that table with the SQL Query Analyzer:
> > >
> > > in field 'time_' --> 16.01.2003 00:00:04
> > > in field 'date_' --> 01.01.2002
> > > in field 'time_date_new' --> 01.01.2002 00:00:04 (I want to have!)
> > >
> > >
> > > I want with a update statement write the time part from field 'time_'
> into
> > > the field
> > > 'time_date_new' and then the date part from field 'date_' write also
> into
> > > the field 'time_date_new'
> > >
> > > my statement:
> > >
> > > UPDATE Anrufe_2003_Test
> > > SET time_date_new = convert(CHAR(11),date_,126) +
> > > convert(CHAR(12),time_,114)
> > >
> > > but I get in query analyser a syntax error on converting.
> > > who has an idea?
> > >
> > > Thanks
> > >
> > >
> >
> >
>