SEO

#1 (**permalink**) 04-08-2008, 06:16 PM

Hi,

I'm new to SQL and looking to a way to retrieve the day part of a
DATETIME column.

By now I'm using CONVERT, which gives me a CHAR. To keep the result
sortable, I have to use a format apropriate to that demand.

But I'd prefer getting a DATETIME back, just with cut off (or reset to
00:00:00.000) time part.

Yep, I could re-convert my CHAR to DATETIME, but maybe there is a
better, more elegant way to achieve this?

Regards,
Bernd

#2 (**permalink**) 04-08-2008, 06:16 PM

Which Sybase software are you using, ASA or ASE?

In article <drodm0$fts$00$1@news.t-online.com>, bgi0815-for-news-use-
only@this-is-a-valid-mailaddress-but...not-spam-me.de says...
> Hi,
>
>
> I'm new to SQL and looking to a way to retrieve the day part of a
> DATETIME column.
>
> By now I'm using CONVERT, which gives me a CHAR. To keep the result
> sortable, I have to use a format apropriate to that demand.
>
> But I'd prefer getting a DATETIME back, just with cut off (or reset to
> 00:00:00.000) time part.
>
> Yep, I could re-convert my CHAR to DATETIME, but maybe there is a
> better, more elegant way to achieve this?
>
> Regards,
> Bernd
>

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

#3 (**permalink**) 04-08-2008, 06:16 PM

Hi,

> Which Sybase software are you using, ASA or ASE?

ASE 12.5.2

>>Hi,
>>
>>
>>I'm new to SQL and looking to a way to retrieve the day part of a
>>DATETIME column.
>>
>>By now I'm using CONVERT, which gives me a CHAR. To keep the result
>>sortable, I have to use a format apropriate to that demand.
>>
>>But I'd prefer getting a DATETIME back, just with cut off (or reset to
>>00:00:00.000) time part.
>>
>>Yep, I could re-convert my CHAR to DATETIME, but maybe there is a
>>better, more elegant way to achieve this?
>>
>>Regards,
>>Bernd
>>
>
>

#4 (**permalink**) 04-08-2008, 06:16 PM

Bernd Giegerich wrote:
> Hi,
>
>> Which Sybase software are you using, ASA or ASE?
>
> ASE 12.5.2
>
>>> Hi,
>>>
>>>
>>> I'm new to SQL and looking to a way to retrieve the day part of a
>>> DATETIME column.
>>>
>>> By now I'm using CONVERT, which gives me a CHAR. To keep the result
>>> sortable, I have to use a format apropriate to that demand.
>>>
>>> But I'd prefer getting a DATETIME back, just with cut off (or reset
>>> to 00:00:00.000) time part.
>>>
>>> Yep, I could re-convert my CHAR to DATETIME, but maybe there is a
>>> better, more elegant way to achieve this?

declare @d datetime
set @d = getdate()
convert(varchar, @d, 112)

.... return the current date as '20060131'.

That may not be pretty (a concern if this is to be displayed to an
end-user), but it *is* sortable.

If this is not an acceptable solution, you might consider returning both a
formattet (using convert) version of the date and the date itself, then
filter out column with the unformattet date at a later stage.

--
Kristian Damm Jensen

#5 (**permalink**) 04-08-2008, 06:16 PM

Kristian Damm Jensen wrote:

> declare @d datetime
> set @d = getdate()
> convert(varchar, @d, 112)
>
> ... return the current date as '20060131'.
>
> That may not be pretty (a concern if this is to be displayed to an
> end-user), but it *is* sortable.

yep, that's more or less what I'm doing here by now.

I thought, that there may be a better solution around, as e.g.
aggregating on the day part of DATETIMEs is surely very common, but it
looks as I didn't miss a chapter in the "date functions" chapter of my
SQL book... :-)

Thanks,
Bernd

#6 (**permalink**) 04-08-2008, 06:16 PM

As of ASE 12.5.1, then the date type
is available, so you can do a convert to that which
would also seem to be what you are after, i.e.

convert( date, date_time_col )

By "day part of DATETIMEs" I am assuming that you want to ignore the
hours mins/secs part of a particular datetime value.

- Paul

Bernd Giegerich wrote:
> Kristian Damm Jensen wrote:
>
>> declare @d datetime
>> set @d = getdate()
>> convert(varchar, @d, 112)
>>
>> ... return the current date as '20060131'.
>>
>> That may not be pretty (a concern if this is to be displayed to an
>> end-user), but it *is* sortable.
>
> yep, that's more or less what I'm doing here by now.
>
> I thought, that there may be a better solution around, as e.g.
> aggregating on the day part of DATETIMEs is surely very common, but it
> looks as I didn't miss a chapter in the "date functions" chapter of my
> SQL book... :-)
>
> Thanks,
> Bernd

#7 (**permalink**) 04-08-2008, 06:16 PM

Paul Dow wrote:

> As of ASE 12.5.1, then the date type
> is available, so you can do a convert to that which
> would also seem to be what you are after, i.e.
>
> convert( date, date_time_col )

great, that's exactly what I was looking for (working with 12.5.2).

Thanks,
Bernd

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

How to retrieve the date part of a DATETIME column via (T-)SQL?