Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Cannot convert 1268199137 to a date

9 posts in General Discussion Last posting was on 2010-04-01 17:56:03.0Z
juliette.matsushita Posted on 2010-03-18 12:38:36.0Z
Sender: 1501.4ba2188f.1804289383@sybase.com
From: juliette.matsushita@cra-arc.gc.ca
Newsgroups: sybase.public.ase.general
Subject: Cannot convert 1268199137 to a date
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ba21ecc.166a.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 18 Mar 2010 04:38:36 -0800
X-Trace: forums-1-dub 1268915916 10.22.241.41 (18 Mar 2010 04:38:36 -0800)
X-Original-Trace: 18 Mar 2010 04:38:36 -0800, 10.22.241.41
Lines: 22
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29054
Article PK: 78292

Hi,

Is it possible to convert int to datetime?

I am trying to extract yesterday data using starttime field
of the table. Can you advise me?

I have a field of type int representing datetime in a table


1> select distinct starttime
2> from hourly_jitterstats
3> where datepart(dd, starttime) >= (select dateadd(dd, -1,
getdate()))
4> go
Msg 257, Level 16, State 0:
ASA Error -157: Cannot convert 1268199137 to a date
(0 rows affected)


Thanks,
Juliette


Ignacio Vera Posted on 2010-03-18 12:45:10.0Z
From: Ignacio Vera <ivera@nospam.org>
Organization: European Southern Observatory
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Cannot convert 1268199137 to a date
References: <4ba21ecc.166a.1681692777@sybase.com>
In-Reply-To: <4ba21ecc.166a.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ba22056@forums-1-dub>
Date: 18 Mar 2010 04:45:10 -0800
X-Trace: forums-1-dub 1268916310 10.22.241.152 (18 Mar 2010 04:45:10 -0800)
X-Original-Trace: 18 Mar 2010 04:45:10 -0800, vip152.sybase.com
Lines: 31
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29055
Article PK: 78293

assuming the nuber is the number of seconds since 1970:

datepart(dd, dateadd(ss,1268199137,'1970-01-01'))

Cheers,

Ignacio

juliette.matsushita@cra-arc.gc.ca wrote:
> Hi,
>
> Is it possible to convert int to datetime?
>
> I am trying to extract yesterday data using starttime field
> of the table. Can you advise me?
>
> I have a field of type int representing datetime in a table
>
>
> 1> select distinct starttime
> 2> from hourly_jitterstats
> 3> where datepart(dd, starttime) >= (select dateadd(dd, -1,
> getdate()))
> 4> go
> Msg 257, Level 16, State 0:
> ASA Error -157: Cannot convert 1268199137 to a date
> (0 rows affected)
>
>
> Thanks,
> Juliette


juliette.matsushita Posted on 2010-03-18 14:13:47.0Z
Sender: 1962.4ba22ccd.1804289383@sybase.com
From: Juliette.Matsushita@cra-arc.gc.ca
Newsgroups: sybase.public.ase.general
Subject: Re: Cannot convert 1268199137 to a date
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ba2351b.1b41.1681692777@sybase.com>
References: <4ba22056@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 18 Mar 2010 06:13:47 -0800
X-Trace: forums-1-dub 1268921627 10.22.241.41 (18 Mar 2010 06:13:47 -0800)
X-Original-Trace: 18 Mar 2010 06:13:47 -0800, 10.22.241.41
Lines: 58
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29057
Article PK: 78294

Thanks Ignacio for the answer.

Why the following just displays 10 and not a day as
yyyy-dd-mm format?

1> print datepart(dd, dateadd(ss,1268199137,'1970-01-01'))
2> go
10


I am trying to select all yesterday rows in a table where a
field "starttime" is of datatype int, representing the
datetime.

I cannot convert starttime to a date format so I can compare
it
to yesterday.

eg.
select *
from hourly_jitterstats
where starttime >= (select dateadd(dd,1, getdate()))


Thanks
Juliette

> assuming the nuber is the number of seconds since 1970:
>
> datepart(dd, dateadd(ss,1268199137,'1970-01-01'))
>
> Cheers,
>
> Ignacio
>
> juliette.matsushita@cra-arc.gc.ca wrote:
> > Hi,
> >
> > Is it possible to convert int to datetime?
> >
> > I am trying to extract yesterday data using starttime
> > field of the table. Can you advise me?
> >
> > I have a field of type int representing datetime in a
> > table
> >
> > 1> select distinct starttime
> > 2> from hourly_jitterstats
> > 3> where datepart(dd, starttime) >= (select dateadd(dd,
> > -1, getdate()))
> > 4> go
> > Msg 257, Level 16, State 0:
> > ASA Error -157: Cannot convert 1268199137 to a date
> > (0 rows affected)
> >
> >
> > Thanks,
> > Juliette


Ignacio Vera Posted on 2010-03-18 14:26:47.0Z
From: Ignacio Vera <ivera@nospam.org>
Reply-To: ivera@eso.org
Organization: European Southern Observatory
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Cannot convert 1268199137 to a date
References: <4ba22056@forums-1-dub> <4ba2351b.1b41.1681692777@sybase.com>
In-Reply-To: <4ba2351b.1b41.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ba23827@forums-1-dub>
Date: 18 Mar 2010 06:26:47 -0800
X-Trace: forums-1-dub 1268922407 10.22.241.152 (18 Mar 2010 06:26:47 -0800)
X-Original-Trace: 18 Mar 2010 06:26:47 -0800, vip152.sybase.com
Lines: 73
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29058
Article PK: 78296

Hi,

To get the date:

select dateadd(ss,1268199137,'1970-01-01')

to get the day part:

select datepart(dd, dateadd(ss,1268199137,'1970-01-01'))

Cheers,

Ignacio

Juliette.Matsushita@cra-arc.gc.ca wrote:
> Thanks Ignacio for the answer.
>
> Why the following just displays 10 and not a day as
> yyyy-dd-mm format?
>
> 1> print datepart(dd, dateadd(ss,1268199137,'1970-01-01'))
> 2> go
> 10
>
>
> I am trying to select all yesterday rows in a table where a
> field "starttime" is of datatype int, representing the
> datetime.
>
> I cannot convert starttime to a date format so I can compare
> it
> to yesterday.
>
> eg.
> select *
> from hourly_jitterstats
> where starttime >= (select dateadd(dd,1, getdate()))
>
>
> Thanks
> Juliette
>
>> assuming the nuber is the number of seconds since 1970:
>>
>> datepart(dd, dateadd(ss,1268199137,'1970-01-01'))
>>
>> Cheers,
>>
>> Ignacio
>>
>> juliette.matsushita@cra-arc.gc.ca wrote:
>>> Hi,
>>>
>>> Is it possible to convert int to datetime?
>>>
>>> I am trying to extract yesterday data using starttime
>>> field of the table. Can you advise me?
>>>
>>> I have a field of type int representing datetime in a
>>> table
>>>
>>> 1> select distinct starttime
>>> 2> from hourly_jitterstats
>>> 3> where datepart(dd, starttime) >= (select dateadd(dd,
>>> -1, getdate()))
>>> 4> go
>>> Msg 257, Level 16, State 0:
>>> ASA Error -157: Cannot convert 1268199137 to a date
>>> (0 rows affected)
>>>
>>>
>>> Thanks,
>>> Juliette


juliette.matsushita Posted on 2010-03-18 16:25:59.0Z
Sender: 1962.4ba22ccd.1804289383@sybase.com
From: juliette.matsushita@cra-arc.gc.ca
Newsgroups: sybase.public.ase.general
Subject: Re: Cannot convert 1268199137 to a date
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ba25417.1ff3.1681692777@sybase.com>
References: <4ba23827@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 18 Mar 2010 08:25:59 -0800
X-Trace: forums-1-dub 1268929559 10.22.241.41 (18 Mar 2010 08:25:59 -0800)
X-Original-Trace: 18 Mar 2010 08:25:59 -0800, 10.22.241.41
Lines: 13
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29059
Article PK: 78300

Hi Ignacio,

Would you be able to help me on this part?

I am trying to select all yesterday rows in a table
where a field "starttime" is of datatype int,
representing the datetime.

I cannot convert starttime to a date format so I can
compare it to yesterday.

The where clause does not work because I cannot convert
starttime to date format to be similar to the second operand


Sherlock, Kevin [TeamSybase] Posted on 2010-03-18 16:57:43.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4ba23827@forums-1-dub> <4ba25417.1ff3.1681692777@sybase.com>
Subject: Re: Cannot convert 1268199137 to a date
Lines: 48
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ba25b87@forums-1-dub>
Date: 18 Mar 2010 08:57:43 -0800
X-Trace: forums-1-dub 1268931463 10.22.241.152 (18 Mar 2010 08:57:43 -0800)
X-Original-Trace: 18 Mar 2010 08:57:43 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29060
Article PK: 78297

You have to tell us how the integer "represents" a date. Is it the number
of seconds since some fixed date? Until you can communicate how this
integer is to be interpreted, nobody can help you.

Ignacio is attempting to help you assuming that "starttime" represents the
number of seconds since January 1, 1970.

If that is true, you can convert starttime to a "datetime" datatype as he
has already stated by the expression:

dateadd(ss,starttime,'1/1/1970')

A datetime value "y" is dated yesterday relative to today "t" by:

where datediff(dd,y,t) = 1

Substituting your converted "starttime" expression for "y" and "getdate()"
for "t" , you can now compute which "starttime" values are "yesterday"
relative to today with :

select *
from mytable
where datediff(dd, dateadd(ss,starttime,'1/1/1970') , getdate() ) = 1

simlarly, get all records that are older than yesterday

select *
from mytable
where datediff(dd, dateadd(ss,starttime,'1/1/1970') , getdate() ) > 1

<juliette.matsushita@cra-arc.gc.ca> wrote in message
news:4ba25417.1ff3.1681692777@sybase.com...
> Hi Ignacio,
>
> Would you be able to help me on this part?
>
> I am trying to select all yesterday rows in a table
> where a field "starttime" is of datatype int,
> representing the datetime.
>
> I cannot convert starttime to a date format so I can
> compare it to yesterday.
>
> The where clause does not work because I cannot convert
> starttime to date format to be similar to the second operand


juliette.matsushita Posted on 2010-03-18 18:30:45.0Z
Sender: 1962.4ba22ccd.1804289383@sybase.com
From: juliette.matsushita@cra-arc.gc.ca
Newsgroups: sybase.public.ase.general
Subject: Re: Cannot convert 1268199137 to a date
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ba27155.25a2.1681692777@sybase.com>
References: <4ba25b87@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 18 Mar 2010 10:30:45 -0800
X-Trace: forums-1-dub 1268937045 10.22.241.41 (18 Mar 2010 10:30:45 -0800)
X-Original-Trace: 18 Mar 2010 10:30:45 -0800, 10.22.241.41
Lines: 64
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29061
Article PK: 78298

Hello,

Great! I got it.

Thank you for your help and patience. It is very useful
hint.

Juliette

> You have to tell us how the integer "represents" a date.
> Is it the number of seconds since some fixed date?
> Until you can communicate how this integer is to be
> interpreted, nobody can help you.
>
> Ignacio is attempting to help you assuming that
> "starttime" represents the number of seconds since
> January 1, 1970.
>
> If that is true, you can convert starttime to a "datetime"
> datatype as he has already stated by the expression:
>
> dateadd(ss,starttime,'1/1/1970')
>
> A datetime value "y" is dated yesterday relative to today
> "t" by:
>
> where datediff(dd,y,t) = 1
>
> Substituting your converted "starttime" expression for "y"
> and "getdate()" for "t" , you can now compute which
> "starttime" values are "yesterday" relative to today with
> :
>
> select *
> from mytable
> where datediff(dd, dateadd(ss,starttime,'1/1/1970') ,
> getdate() ) = 1
>
> simlarly, get all records that are older than yesterday
>
> select *
> from mytable
> where datediff(dd, dateadd(ss,starttime,'1/1/1970') ,
> getdate() ) > 1
>
>
> <juliette.matsushita@cra-arc.gc.ca> wrote in message
> news:4ba25417.1ff3.1681692777@sybase.com...
> > Hi Ignacio,
> >
> > Would you be able to help me on this part?
> >
> > I am trying to select all yesterday rows in a table
> > where a field "starttime" is of datatype int,
> > representing the datetime.
> >
> > I cannot convert starttime to a date format so I can
> > compare it to yesterday.
> >
> > The where clause does not work because I cannot convert
> > starttime to date format to be similar to the second
> operand
>
>


PDreyer Posted on 2010-03-23 08:31:48.0Z
Sender: 33f.4ba876c3.1804289383@sybase.com
From: PDreyer
Newsgroups: sybase.public.ase.general
Subject: Re: Cannot convert 1268199137 to a date
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ba87c74.3bb.1681692777@sybase.com>
References: <4ba27155.25a2.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 23 Mar 2010 00:31:48 -0800
X-Trace: forums-1-dub 1269333108 10.22.241.41 (23 Mar 2010 00:31:48 -0800)
X-Original-Trace: 23 Mar 2010 00:31:48 -0800, 10.22.241.41
Lines: 79
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29067
Article PK: 78304

Warning: When you use functions on a column in the where
clause then you won't be able to use any index defined on
that column

Instead do:
select distinct starttime
from hourly_jitterstats
where starttime >=
(datediff(dd,'19700101',getdate())-1)*86400 -- yesterday
midnight
and starttime < datediff(dd,'19700101',getdate())*86400 --
today midnight

> Hello,
>
> Great! I got it.
>
> Thank you for your help and patience. It is very useful
> hint.
>
> Juliette
>
> > You have to tell us how the integer "represents" a date.
> > Is it the number of seconds since some fixed date?
> > Until you can communicate how this integer is to be
> > interpreted, nobody can help you.
> >
> > Ignacio is attempting to help you assuming that
> > "starttime" represents the number of seconds since
> > January 1, 1970.
> >
> > If that is true, you can convert starttime to a
> > "datetime" datatype as he has already stated by the
> expression: >
> > dateadd(ss,starttime,'1/1/1970')
> >
> > A datetime value "y" is dated yesterday relative to
> > today "t" by:
> >
> > where datediff(dd,y,t) = 1
> >
> > Substituting your converted "starttime" expression for
> > "y" and "getdate()" for "t" , you can now compute which
> > "starttime" values are "yesterday" relative to today
> > with :
> >
> > select *
> > from mytable
> > where datediff(dd, dateadd(ss,starttime,'1/1/1970') ,
> > getdate() ) = 1
> >
> > simlarly, get all records that are older than yesterday
> >
> > select *
> > from mytable
> > where datediff(dd, dateadd(ss,starttime,'1/1/1970') ,
> > getdate() ) > 1
> >
> >
> > <juliette.matsushita@cra-arc.gc.ca> wrote in message
> > news:4ba25417.1ff3.1681692777@sybase.com...
> > > Hi Ignacio,
> > >
> > > Would you be able to help me on this part?
> > >
> > > I am trying to select all yesterday rows in a table
> > > where a field "starttime" is of datatype int,
> > > representing the datetime.
> > >
> > > I cannot convert starttime to a date format so I can
> > > compare it to yesterday.
> > >
> > > The where clause does not work because I cannot
> > > convert starttime to date format to be similar to the
> > second operand
> >
> >


juliette.matsushita Posted on 2010-04-01 17:56:03.0Z
Sender: 2c2f.4bb4af3a.1804289383@sybase.com
From: juliette.matsushita@cra-arc.gc.ca
Newsgroups: sybase.public.ase.general
Subject: Re: Cannot convert 1268199137 to a date
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4bb4de33.34e2.1681692777@sybase.com>
References: <4ba87c74.3bb.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 1 Apr 2010 09:56:03 -0800
X-Trace: forums-1-dub 1270144563 10.22.241.41 (1 Apr 2010 09:56:03 -0800)
X-Original-Trace: 1 Apr 2010 09:56:03 -0800, 10.22.241.41
Lines: 85
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29088
Article PK: 78326

Hi,

Awesome help! Thanks for the explanation.

Juliette.

> Warning: When you use functions on a column in the where
> clause then you won't be able to use any index defined on
> that column
>
> Instead do:
> select distinct starttime
> from hourly_jitterstats
> where starttime >=
> (datediff(dd,'19700101',getdate())-1)*86400 -- yesterday
> midnight
> and starttime < datediff(dd,'19700101',getdate())*86400
> -- today midnight
>
>
>
> > Hello,
> >
> > Great! I got it.
> >
> > Thank you for your help and patience. It is very useful
> > hint.
> >
> > Juliette
> >
> > > You have to tell us how the integer "represents" a
> > > date. Is it the number of seconds since some fixed
> > > date? Until you can communicate how this integer is
> > > to be interpreted, nobody can help you.
> > >
> > > Ignacio is attempting to help you assuming that
> > > "starttime" represents the number of seconds since
> > > January 1, 1970.
> > >
> > > If that is true, you can convert starttime to a
> > > "datetime" datatype as he has already stated by the
> > expression: >
> > > dateadd(ss,starttime,'1/1/1970')
> > >
> > > A datetime value "y" is dated yesterday relative to
> > > today "t" by:
> > >
> > > where datediff(dd,y,t) = 1
> > >
> > > Substituting your converted "starttime" expression for
> > > "y" and "getdate()" for "t" , you can now compute
> > > which "starttime" values are "yesterday" relative to
> > > today with :
> > >
> > > select *
> > > from mytable
> > > where datediff(dd, dateadd(ss,starttime,'1/1/1970')
> > > , getdate() ) = 1
> > >
> > > simlarly, get all records that are older than
> > yesterday >
> > > select *
> > > from mytable
> > > where datediff(dd, dateadd(ss,starttime,'1/1/1970')
> > > , getdate() ) > 1
> > >
> > >
> > > <juliette.matsushita@cra-arc.gc.ca> wrote in message
> > > news:4ba25417.1ff3.1681692777@sybase.com...
> > > > Hi Ignacio,
> > > >
> > > > Would you be able to help me on this part?
> > > >
> > > > I am trying to select all yesterday rows in a table
> > > > where a field "starttime" is of datatype int,
> > > > representing the datetime.
> > > >
> > > > I cannot convert starttime to a date format so I can
> > > > compare it to yesterday.
> > > >
> > > > The where clause does not work because I cannot
> > > > convert starttime to date format to be similar to
> > > the second operand
> > >
> > >