Convert String to Date

I have a database where the dates are stored as a string type ( ex.
"010699")
I need to selcect rows that fall between a given date range.


I've tried:

CONVERT(DATE,LEFT(stringdate,2) + '/' + SUBSTR(stringdate,3,2) + '/' +
RIGHT(stringdate,2) ,1 ) >= :begindate AND
CONVERT(DATE,LEFT(stringdate,2) + '/' + SUBSTR(stringdate,3,2) + '/' +
RIGHT(stringdate,2) ,1 ) <= :enddate

And I,ve also tried using CAST and the PowerBuilder DATE function

All return this datawindow error:

Select error: SQLSTATE = 07006 [SYBASE][ODBC DRIVER]: cannot convert
07/22/97 to a date/time.
07/22/97  is a date of a record in the database

TIA
Dan




0
Dan
1/6/1999 4:10:19 PM
sybase.powerbuilder.general 62418 articles. 17 followers. Follow

4 Replies
3556 Views

Similar Articles

[PageSpeed] 23

Dan,

Try parsing the string first to get 3 integer values (year, month and day)
and then call the Date() PB function passing it the integer values instead
of the strings.

Roy

Dan Silverman <dsilverm@tropical.com> wrote in message
news:nNmsM7YO#GA.245@forums.powersoft.com...
>I have a database where the dates are stored as a string type ( ex.
>"010699")
>I need to selcect rows that fall between a given date range.
>
>
>I've tried:
>
>CONVERT(DATE,LEFT(stringdate,2) + '/' + SUBSTR(stringdate,3,2) + '/' +
>RIGHT(stringdate,2) ,1 ) >= :begindate AND
>CONVERT(DATE,LEFT(stringdate,2) + '/' + SUBSTR(stringdate,3,2) + '/' +
>RIGHT(stringdate,2) ,1 ) <= :enddate
>
>And I,ve also tried using CAST and the PowerBuilder DATE function
>
>All return this datawindow error:
>
>Select error: SQLSTATE = 07006 [SYBASE][ODBC DRIVER]: cannot convert
>07/22/97 to a date/time.
>07/22/97  is a date of a record in the database
>
>TIA
>Dan
>
>
>
>


0
Roy
1/6/1999 4:14:02 PM
"Dan Silverman" <dsilverm@tropical.com> wrote:
[This reply posted and e-mailed]

>CONVERT(DATE,LEFT(stringdate,2) + '/' + SUBSTR(stringdate,3,2) + '/' +
>RIGHT(stringdate,2) ,1 ) >= :begindate AND
>CONVERT(DATE,LEFT(stringdate,2) + '/' + SUBSTR(stringdate,3,2) + '/' +
>RIGHT(stringdate,2) ,1 ) <= :enddate

This looks like it's part of your SQL statement. As a result, you should
probably check your database documentation to make sure the syntax is correct.

>And I,ve also tried using CAST and the PowerBuilder DATE function

Rather than converting the string to a date, why not convert the date retrieval
argument to a string? No, that won't work because of the order of the date
components (if your string were stored yymmdd it would work).

>All return this datawindow error:
>
>Select error: SQLSTATE = 07006 [SYBASE][ODBC DRIVER]: cannot convert
>07/22/97 to a date/time.

That's not a datawindow error, it's a database error. PowerBuilder is simply
passing it through.

Because you need to do the conversion at the database side, I think you need to
do more digging through the database documentation. Either that or change the
order of the date components to be yymmdd in the string column.
---
Craig Wagner                   | E-mail:  cwagner@metacorp.com
CPD Professional               | Web:     http://www.metacorp.com
Certified Powersoft Instructor | Phone:   (503) 452-6343
Portland, OR USA               |

Keeper of the PowerBuilder FAQ
http://www.teleport.com/~wagnerc/powerbuilder_faq.html
0
cwagner
1/6/1999 4:22:34 PM
Dan,

If it is at all possible, I would highly recommend changing the column to a
date or datetime datatype.  At the very least, you should also store the
year as 4 digits (ideally in the yyyymmdd format so you can use > and <
operators without doing date conversions).  You are opening yourself up to
some pretty significant year 2000 problems otherwise, you also run across
the problems you are experiencing below with comparisons and conversions.

To answer your question, it's not a PB problem.  The error message you're
getting is a database one, so you need to check the db syntax for your
conversion functions.

HTH,
Greg

--
______________________________
Gregory R. George
Greg_George@AscensionLabs.com
Ascension Labs, LLC
www.AscensionLabs.com

Dan Silverman wrote in message ...
>I have a database where the dates are stored as a string type ( ex.
>"010699")
>I need to selcect rows that fall between a given date range.
>
>
>I've tried:
>
>CONVERT(DATE,LEFT(stringdate,2) + '/' + SUBSTR(stringdate,3,2) + '/' +
>RIGHT(stringdate,2) ,1 ) >= :begindate AND
>CONVERT(DATE,LEFT(stringdate,2) + '/' + SUBSTR(stringdate,3,2) + '/' +
>RIGHT(stringdate,2) ,1 ) <= :enddate
>
>And I,ve also tried using CAST and the PowerBuilder DATE function
>
>All return this datawindow error:
>
>Select error: SQLSTATE = 07006 [SYBASE][ODBC DRIVER]: cannot convert
>07/22/97 to a date/time.
>07/22/97  is a date of a record in the database
>
>TIA
>Dan
>
>
>
>


0
Gregory
1/6/1999 7:43:09 PM
/*****
Sorry to post again as the prior post was missing a
few characters and the format was also
destroyed. Strange!!
*****/
Hi Dan,

Try this.... if the backend is SQLAnywhere

select * from table  where
    ymd( convert(int,substr(dtcol,5,2)),
            convert(int,substr(dtcol,3,2)),
            convert(int,substr(dtcol,1,2)) )
    between :fromdate and :todate

where dtcol is the name of your date field stored as string in format
"ddmmyy"

It has worked for me time and again earlier. I have used it in Y2k problems.
The above will also take care of your year problem as we are using YMD()
which is Y2K compliant. Means, if it finds years after 50 will take it as
19xx and from 00 to 49 will take it as 20xx.

Cheers,
Somnath Ukil
--
Quint Software Solutions
EC-5, Sector 1, Salt Lake,
Calcutta, INDIA. 700 064
WWW: http://quint-software.com
E-Mail: sukil@quint-software.com
Ring: 91-33-3343538, 91-33-3581574
Fax: 91-33-3581574
Dan Silverman wrote in message ...
>I have a database where the dates are stored as a string type ( ex.
>"010699")
>I need to selcect rows that fall between a given date range.
>
>
>I've tried:
>
>CONVERT(DATE,LEFT(stringdate,2) + '/' + SUBSTR(stringdate,3,2) + '/' +
>RIGHT(stringdate,2) ,1 ) >= :begindate AND
>CONVERT(DATE,LEFT(stringdate,2) + '/' + SUBSTR(stringdate,3,2) + '/' +
>RIGHT(stringdate,2) ,1 ) <= :enddate
>
>And I,ve also tried using CAST and the PowerBuilder DATE function
>
>All return this datawindow error:
>
>Select error: SQLSTATE = 07006 [SYBASE][ODBC DRIVER]: cannot convert
>07/22/97 to a date/time.
>07/22/97  is a date of a record in the database
>
>TIA
>Dan
>
>
>
>


begin 666 Somnath Ukil.vcf
M0D5'24XZ5D-!4D0-"E9%4E-)3TXZ,BXQ#0I..E5K:6P[4V]M;F%T: T*1DXZ
M4V]M;F%T:"!5:VEL#0I/4D<Z475I;G0@4V]F='=A<F4@4V]L=71I;VYS#0I4
M251,13I#:&EE9B!%>&5C=71I=F4@3V9F:6-E<@T*5$5,.U=/4DL[5D])0T4Z
M.3$M,S,M,S,T,S4S."P@.3$M,S,M,S4X,34W- T*5$5,.TA/344[5D])0T4Z
M.3$M,S,M,S,T,S4S."P@.3$M,S,M,S4X,34W- T*5$5,.U=/4DL[1D%8.CDQ
M+3,S+3,U.#$U-S0-"E1%3#M(3TU%.T9!6#HY,2TS,RTS-3@Q-3<T#0I!1%([
M5T]22SM%3D-/1$E.1SU154]4140M4%))3E1!0DQ%.CL[14,M-2 H1W)O=6YD
M($9L;V]R*3TP1#TP05-E8W1O<B Q/3!$/3!!4V%L="!,86ME.T-A;&-U='1A
M.U=E<W0@0F5N9V%L.S<P," ]#0HP-C0[24Y$24$-"DQ!0D5,.U=/4DL[14Y#
M3T1)3D<]455/5$5$+5!224Y404),13I%0RTU("A'<F]U;F0@1FQO;W(I/3!$
M/3!!4V5C=&]R(#$],$0],$%386QT($QA:V4],$0],$%#86QC=71T82P@5V5S
M="!"96YG86P@/0T*-S P(# V-#TP1#TP04E.1$E!#0I!1%([2$]-13M%3D-/
M1$E.1SU154]4140M4%))3E1!0DQ%.CL[14,M-3TP1#TP02@Q<W0@1FQO;W(I
M/3!$/3!!4V5C=&]R(#$],$0],$%386QT($QA:V4[0V%L8W5T=&$[5V5S="!"
M96YG86P[-S ]#0HP(# V-#M)3D1)00T*3$%"14P[2$]-13M%3D-/1$E.1SU1
M54]4140M4%))3E1!0DQ%.D5#+34],$0],$$H,7-T($9L;V]R*3TP1#TP05-E
M8W1O<B Q/3!$/3!!4V%L="!,86ME/3!$/3!!0V%L8W5T=&$L(%=E<W0@0F5N
M9V$]#0IL(#<P," P-C0],$0],$%)3D1)00T*55),.FAT=' Z+R]M96UB97)S
M+G1R:7!O9"YC;VTO?G-O;6YA=&AU:VEL#0I54DPZ:'1T<#HO+W%U:6YT+7-O
M9G1W87)E+F-O;0T*14U!24P[4%)%1CM)3E1%4DY%5#IS=6MI;$!Q=6EN="US
M;V9T=V%R92YC;VT-"E)%5CHQ.3DY,#$P-U0P-#,R,35:#0I%3D0Z5D-!4D0-
!"@``
`
end

0
Somnath
1/7/1999 4:32:15 AM
Reply:

Similar Artilces:

Date time string error
Hi all, Just downloaded the club site starter and was hit with the following error on the events calendar page: String was not recognized as a valid DateTime. I have just taken a minute to fix it and thought i would share. The steps i performed are listed below: Download EventCalendar code from http://forums.asp.net/938287/ShowPost.aspx Copy the unzipped folder into my club site project Add the project into my club site solution Alter the method CreateDataBoundChildren(System.Data.DataView dv, Table table, DateTime todaysDate, DateTime visibleDate, System.Global...

How can I convert a Json encoded date to a javascript date string?
Hi, I'm trying to convert a date that was encoded using the Json() encoding function to a string representation of the date.  This is how I did it but there must be a better way...  Can anyone give me a few tips? function myformater(jsonDt) { //incoming json date string is of the form "/Date(946702800000)/" // Parse away the slashes ("/") and add keyword "new" var jscriptDt = "new " + jsonDt.substring(1, jsonDt.length - 1); // Convert the string to a javascript date var jdate = e...

Converting date to string
I have created a dddw with datatype datetime. I want to populate a datastore with this dddw values. I need the datatype to be of type string. Therefore,I need to convert the date values to string in the dddw before it populates the datastore. Can you help? Do you want to populate the DataStore with the values in the dropdown itself or the values from the column the dropdown provides values for? Either way, you would walk the datawindow or datawindowchild, GetItemDateTime, convert it to you chosen format using the String function, InsertRow on the DataStore, and SetItem with the...

cannot convert string to date
dear all, can anyone help since i still have no idea how to solve it. i'm using the popup calendar which is written in javascript , to get the date & display at the txtStartDate.Text and txtEndDate.Text - sucessfully worked. but i cannot pass the parameter to the cmd as below code and i get the error - Input string was not in a correct format, Cast from string "2003-08-19" to type 'Long' is not valid. Private Sub GenButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles GenButton.Click Dim c...

Converting Date Strings
In a SQL database, I have a field storing a ToolPostingDate value (SmallDateTime, 4). In an ASP.NET page, I have this code: <asp:BoundColumn DataField="ToolPostingDate" SortExpression="ToolPostingDate" HeaderText="Posted On:" FooterText="Posted On:"> <HeaderStyle width="200px"></HeaderStyle> </asp:BoundColumn> A couple problems: 1. The date is being displayed on the page as "12/15/2003 12:00:00 AM" when I want "December 15, 2003" (no time). Is there an easy way to convert ...

How to convert a date to a string
How to convert a date to a string. I wanna put the result of "myDate.ToShortDateString" to a string variable. waiting for a kind reply... myDate.ToShortDateString() returns a string. To put it into a string variable, put: Dim str as String str = myDate.ToShortDateString Hope this helps, Russell Actually, I'm using this in my SELECT statement which is as following : Dim MySQL as string = "SELECT * from reservations WHERE pickup_date = " & myDate.ToShortDateString If I print this statement with Response.Write it shows : SELECT *...

Convert string to date!
Hello, I got ASP.NET AJAX calendar and textbox in ASP.NET code. When I select a date on calendar and textbox value as following:txtEnterDate.text = "01/30/2009". I would like to convert this date string to date, so I can run query against with SQL Server database with date format is 2009-30-01 00:00:00.000Any help to making it would be appreciated. Thanks in advance. DateTime dateTime = Convert.ToDateTime(txtEnterDate.text); and then pass it to your sql... Please, Mark as Answer if this reply helped you===============Esayas Bekeleesayas27 at yahoo dot com I believe SQL wi...

Converting a string to date
I am converting a string to a date. I have 2 options, Convert.ToDateTime(string) or Date.Parse(string).Which is the better to use and why?   Use DateTime.Parse(string), because Convert.ToDateTime(string) merely calls DateTime.Parse(string). Well, almost: it also actually checks to see if the string is null/Nothing, and if it is it returns a new DateTime set to DateTime.MinValue. As this would probably be undesirable in nearly all circumstances, I'd stick to DateTime.Parse().RegardsDave Actually, I prefer using the style strings, gives you much more control: Format(strDate, "...

Convert String to Date......
I have the following code defined in the click of a button. As u can see I am trying convert a string to Date using CDate..However I getting a constant error:Cast from string "11/23/2006" to type 'Date' is not valid.  If txtFim.Text = "" And txtinicio.Text = "" Then                DataIn = "01/01/1900"                DataFim = "01/01/1900"                 ...

Converting strings to dates
I'm looking for the best way to allow a user to enter a date into an editmask or single-line-edit in the format MM/DD/YY, then convert it to YY/MM/DD, so that it can be used to modify a where clause at runtime. Any ideas? TIA - Dave We have the same functionality in our application here. We handle it by storing the dates as dates, and applying the appropriate edit masks when we need them. When the user is entering the data, the edit mask for the date column can be MM/DD/YY. When you're modifying the where clause, concatenate using STRING( <date variable>, ...

converting string to date
i want to convert a string to a date with format dd/mm/yyyy and use transact-sql syntax like convert(date, '01/' + convert(varchar(2),datepart(mm, v_fatura_tarih)) + '/' + convert(varchar(4),datepart(yy, v_fatura_tarih)), 103) it gives an error for convert... is it true? or any other way to do this? convert to yy/mm/dd i used below statement :- select @yymmdd = cast(@YY +'/' +cast(cast(@MM AS integer) AS char(2))+'/31' as datetime) musa wrote: > i want to convert a string to a date with format dd/mm/yyyy and use > transact-s...

convert string to date
Hello,I have a string representing a date and time respecting the following format "yyyMMdd HHmmss".How can I make a DateTime object out of it?!I couldn't figure it out how to do it using Parse, ParseExact or Convert.DateTime. I don't understand what the IFormatProvider parameter of those functions should be.Thanks for your help.  you can use the DateTime struct to Parse a string.  For example: string strDate = '01/01/2008'DateTime.Parse(strDate); my personal blog: http://www.protienshow.comPlease mark the replies with right answer as the answe...

Convert date from string
Hi Everyone, New at this and have been given the already implemented IDM to look after. I have an oracle database that sends me the DOB for a user as a sting (12/02/1978). The IDM system sets the password with this DOB but I'm not sure how to get it working properly. The rule is : <actions> <do-set-dest-password> <arg-string> <token-lower-case> <token-substring length="2"> <token-local-variable name="user_SURNAME"/> </token-substring> </token-lower-case> <to...

convert from string to date
Hi I have a string containing 20/06/2007,I want to convert it to Date type Dim sDate As Date = "20/06/2007" But the following error appears: Conversion from string "20/06/2007" to type 'Date' is not valid. I want to save the value of sdate in a field of type smalldatetime in SQL SERVER 2000 Any help It is because you are inserting a date in dd-mm-yyyy format. and the sql server stores values in mm-dd-yyyy format.' Either you take all three values month, date and year and combine it and convert into mm-dd-yyyy format and store it in SQL.Please cli...

Web resources about - Convert String to Date - sybase.powerbuilder.general

File:Convert to SVG and move to Commons.svg - Wikipedia, the free encyclopedia
As a courtesy (but not a requirement), please e-mail me or leave a note on my talk page if you use this image outside of Wikipedia. Thanks! As ...

Facebook Co-Founder Dustin Moskovitz Sells 450K Shares, Converts 7M More
The great Facebook stock sale frenzy continues on with a report that Co-Founder Dustin Moskovitz unloaded 450,000 shares in the social network ...

Facebook Announces “App2User” Liquidity Program for Merchants to Convert Rewards Points into Credits
... for Credits, just announced a new Credits program called “App2User,” designed to enable merchants and loyalty program operators to convert their ...

How to Convert Leads Into Buying Customers - The 9 Step Sales Process EVERYONE Should Use
... School of Business in Toronto on the topics of Sales and Entrepreneurship. I shared with them the sales process that I find How to Convert Leads ...

Bitcoin : Should I convert my savings to Bitcoin?
Answer (1 of 7): You should think of this question as "Should I invest all my savings in Bitcoin?", and not "Should I have my savings denominated ...

Convertizo 2 - Convert Units and Currency in Style on the App Store on iTunes
Get Convertizo 2 - Convert Units and Currency in Style on the App Store. See screenshots and ratings, and read customer reviews.

Opinion: These 5 things made me a happy iPad Pro convert
... like I needed a bigger device. But after three weeks with my iPad Pro, several things have changed my mind, and I’m officially a happy convert ...

$100 device converts all your film to digital photos
... still on film negatives. Today, we've got the perfect Amazon Cyber Week deal for you: A Wolverine F2D Mighty 20MP 7-in-1 Film to Digital Converter ...

"ISIS 'ranks' the women, considering foreign women and converts to be especially 'valuable.' "
"According to French journalist Anna Erelle’s recent exposé , ISIS foreign fighters prefer foreign women and converts because the jihadists find ...

Stanford Designs Underwater Solar Cells That Convert Greenhouse Gases Into Fuel
... solar industry and the battle against climate change. Importantly, writes Stanford [&hellip Stanford Designs Underwater Solar Cells That Convert ...

Resources last updated: 12/10/2015 4:59:46 PM