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.

convert varchar to datetime

5 posts in General Discussion Last posting was on 2010-02-26 14:10:47.0Z
AJ Posted on 2010-02-24 21:00:02.0Z
Sender: 14de.4b858f72.1804289383@sybase.com
From: Aj
Newsgroups: sybase.public.ase.general
Subject: convert varchar to datetime
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b859352.1557.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 Feb 2010 13:00:02 -0800
X-Trace: forums-1-dub 1267045202 10.22.241.41 (24 Feb 2010 13:00:02 -0800)
X-Original-Trace: 24 Feb 2010 13:00:02 -0800, 10.22.241.41
Lines: 11
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28999
Article PK: 78237

HI,
I am trying to convert varchar field such as '1/03/1995' or
'04/22/1999' to datetime(ex.1/03/1995 12:00:00 AM) as below-

Declare @temp varchar(13)
Declare @EXP_DATE datetime

select @temp = '1/03/1995'
SELECT @EXP_DATE = CONVERT(DATETIME,@temp,101)

PRINT @EXP_DATE


AJ Posted on 2010-02-24 21:02:38.0Z
Sender: 14de.4b858f72.1804289383@sybase.com
From: aj
Newsgroups: sybase.public.ase.general
Subject: Re: convert varchar to datetime
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b8593ee.156a.1681692777@sybase.com>
References: <4b859352.1557.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 Feb 2010 13:02:38 -0800
X-Trace: forums-1-dub 1267045358 10.22.241.41 (24 Feb 2010 13:02:38 -0800)
X-Original-Trace: 24 Feb 2010 13:02:38 -0800, 10.22.241.41
Lines: 12
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29000
Article PK: 78238


> HI,
> I am trying to convert varchar field such as '1/03/1995'
> or '04/22/1999' to datetime(ex.1/03/1995 12:00:00 AM) as
> below-
> sorry, I made some correction to my posting
> Declare @temp varchar(13)
> Declare @EXP_DATE datetime
>
SET @temp = '1/03/1995'
> SET @EXP_DATE = CONVERT(DATETIME,@temp,101)
>
> PRINT @EXP_DATE


Sherlock, Kevin [TeamSybase] Posted on 2010-02-24 21:50:50.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4b859352.1557.1681692777@sybase.com> <4b8593ee.156a.1681692777@sybase.com>
Subject: Re: convert varchar to datetime
Lines: 17
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: <4b859f3a$1@forums-1-dub>
Date: 24 Feb 2010 13:50:50 -0800
X-Trace: forums-1-dub 1267048250 10.22.241.152 (24 Feb 2010 13:50:50 -0800)
X-Original-Trace: 24 Feb 2010 13:50:50 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29001
Article PK: 78239

do you have a question? looks like you are well on your way to me :)

<aj> wrote in message news:4b8593ee.156a.1681692777@sybase.com...
>> HI,
>> I am trying to convert varchar field such as '1/03/1995'
>> or '04/22/1999' to datetime(ex.1/03/1995 12:00:00 AM) as
>> below-
>> sorry, I made some correction to my posting
>> Declare @temp varchar(13)
>> Declare @EXP_DATE datetime
>>
> SET @temp = '1/03/1995'
>> SET @EXP_DATE = CONVERT(DATETIME,@temp,101)
>>
>> PRINT @EXP_DATE


"Mark A. Parsons" <iron_horse Posted on 2010-02-24 23:26:13.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: convert varchar to datetime
References: <4b859352.1557.1681692777@sybase.com> <4b8593ee.156a.1681692777@sybase.com>
In-Reply-To: <4b8593ee.156a.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 100218-0, 02/18/2010), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b85b595$1@forums-1-dub>
Date: 24 Feb 2010 15:26:13 -0800
X-Trace: forums-1-dub 1267053973 10.22.241.152 (24 Feb 2010 15:26:13 -0800)
X-Original-Trace: 24 Feb 2010 15:26:13 -0800, vip152.sybase.com
Lines: 27
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29002
Article PK: 78240

FWIW, the 3rd argument to the convert() function is usually used when converting from datetime to (var)char. In your
case it doesn't really do anything for you (and can cause errors for some numeric values placed in the 3rd position).

Other than that there doesn't appear to be any problems with your attempt to convert the varchar(13) value to a datetime
value.

The PRINT statement is another issue ... as is written you should be getting an error, something along the lines of the
PRINT command needing a (var)char argument. If this is what you're posting about then you may want to try the following:

PRINT "%1!", @EXP_DATE

The use of the format string ("%1!") allows you to automagically convert just about any datatype into a valid (var)char
string that the PRINT command can understand.

aj wrote:
>> HI,
>> I am trying to convert varchar field such as '1/03/1995'
>> or '04/22/1999' to datetime(ex.1/03/1995 12:00:00 AM) as
>> below-
>> sorry, I made some correction to my posting
>> Declare @temp varchar(13)
>> Declare @EXP_DATE datetime
>>
> SET @temp = '1/03/1995'
>> SET @EXP_DATE = CONVERT(DATETIME,@temp,101)
>>
>> PRINT @EXP_DATE


Cory Sane [TeamSybase] Posted on 2010-02-26 14:10:47.0Z
From: "Cory Sane [TeamSybase]" <cory!=sane>
Newsgroups: sybase.public.ase.general
References: <4b859352.1557.1681692777@sybase.com> <4b8593ee.156a.1681692777@sybase.com> <4b85b595$1@forums-1-dub>
In-Reply-To: <4b85b595$1@forums-1-dub>
Subject: Re: convert varchar to datetime
Lines: 53
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18005
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b87d667$1@forums-1-dub>
Date: 26 Feb 2010 06:10:47 -0800
X-Trace: forums-1-dub 1267193447 10.22.241.152 (26 Feb 2010 06:10:47 -0800)
X-Original-Trace: 26 Feb 2010 06:10:47 -0800, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29004
Article PK: 78242

Actually,
Your convert function cound be considered as backwards...
Your convert should be to type varchar(22)

Declare @new_time varchar(22)
Declare @old_time datetime

SET @old_time = '1/03/1995' -- implicit conversion of string to date - this is always 00:00:00 hour if not given.

-- old_time is now a dbms value for datetime and you need to find a way to pull that datetime value out with the format that you
want.

SET @new_time = CONVERT(varchar(22),@temp,101) -- explicit conversion to a '101' formatted string.

PRINT "%1!", @new_time





--
Cory Sane
[TeamSybase]
Certified Sybase Associate DBA for ASE 15.0

"Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in message news:4b85b595$1@forums-1-dub...
> FWIW, the 3rd argument to the convert() function is usually used when converting from datetime to (var)char. In your case it
> doesn't really do anything for you (and can cause errors for some numeric values placed in the 3rd position).
>
> Other than that there doesn't appear to be any problems with your attempt to convert the varchar(13) value to a datetime
> value.
>
> The PRINT statement is another issue ... as is written you should be getting an error, something along the lines of the PRINT
> command needing a (var)char argument. If this is what you're posting about then you may want to try the following:
>
> PRINT "%1!", @EXP_DATE
>
> The use of the format string ("%1!") allows you to automagically convert just about any datatype into a valid (var)char string
> that the PRINT command can understand.
>
> aj wrote:
>>> HI,
>>> I am trying to convert varchar field such as '1/03/1995'
>>> or '04/22/1999' to datetime(ex.1/03/1995 12:00:00 AM) as
>>> below-
>>> sorry, I made some correction to my posting
>>> Declare @temp varchar(13)
>>> Declare @EXP_DATE datetime
>>>
>> SET @temp = '1/03/1995'
>>> SET @EXP_DATE = CONVERT(DATETIME,@temp,101)
>>>
>>> PRINT @EXP_DATE