Hello,

I have column as varchar(10) that allows null, all rows format as US format, 11/20/2012,
Now I want to get date different between current date today and above date
After reading the sql help, I found the date should be as YYYY/MM/DD , this means I have to convert my rows to this format and do CAST,  I spent hours to find solution, but I failed can someone help me with example please. Thanks

On 29/11/2012 12:48 AM, Linda_web wrote:
> Hello,
>
> I have column as varchar(10) that allows null, all rows format as US format, 11/20/2012,
> Now I want to get date different between current date today and above date
> After reading the sql help, I found the date should be as YYYY/MM/DD , this means I have to convert my rows to this format and do CAST,  I spent hours to find solution, but I failed can someone help me with example please. Thanks
>


Linda

a quick Google produced this ....

http://www.delphibasics.co.uk/RTL.asp?Name=FormatDateTime

Malcolm

Thank you Malcolm,
My question on done within EDB sql not through Delphi, I know I do it with Delphi.

There is no one example on the help file or even on this site

Thanks



Malcolm Cheyne wrote:



On 29/11/2012 12:48 AM, Linda_web wrote:
> Hello,
>
> I have column as varchar(10) that allows null, all rows format as US format, 11/20/2012,
> Now I want to get date different between current date today and above date
> After reading the sql help, I found the date should be as YYYY/MM/DD , this means I have to convert my rows to this format and do CAST,  I spent hours to find solution, but I failed can someone help me with example please. Thanks
>


Linda

a quick Google produced this ....

http://www.delphibasics.co.uk/RTL.asp?Name=FormatDateTime

Malcolm

Converting the date would be fairly straightforward - just extract the
day, month and year parts and then combine them in proper format and do
cast.

Doing it in Delphi/.net you can just use regular string manipulation
functions. For SQL things are not that much more complex.

Are you always using 2 digit month and day ?

If so then something as simple as this would work in converting the
stiring string to new string format

concat(concat(substring(sourcefield,7 for 2),'/') ,
concat( concat(substring(sourcefield,1 for
2),'/'),substring(sourcefield,4 for 2)))

if you have single digit months and/or days then bit more work is needed
to find and extract the values

Raul




On 11/28/2012 9:48 AM, Linda_web wrote:
> Hello,
>
> I have column as varchar(10) that allows null, all rows format as US format, 11/20/2012,
> Now I want to get date different between current date today and above date
> After reading the sql help, I found the date should be as YYYY/MM/DD , this means I have to convert my rows to this format and do CAST,  I spent hours to find solution, but I failed can someone help me with example please. Thanks
>

Sorry - the year extract should be substring(sourcefield,7 for 4)

Raul

On 11/28/2012 11:35 AM, Raul wrote:
> Converting the date would be fairly straightforward - just extract the
> day, month and year parts and then combine them in proper format and do
> cast.
>
> Doing it in Delphi/.net you can just use regular string manipulation
> functions. For SQL things are not that much more complex.
>
> Are you always using 2 digit month and day ?
>
> If so then something as simple as this would work in converting the
> stiring string to new string format
>
> concat(concat(substring(sourcefield,7 for 2),'/') ,
> concat( concat(substring(sourcefield,1 for
> 2),'/'),substring(sourcefield,4 for 2)))
>
> if you have single digit months and/or days then bit more work is needed
> to find and extract the values
>
> Raul
>
>
>
>
> On 11/28/2012 9:48 AM, Linda_web wrote:
>> Hello,
>>
>> I have column as varchar(10) that allows null, all rows format as US
>> format, 11/20/2012,
>> Now I want to get date different between current date today and above
>> date
>> After reading the sql help, I found the date should be as YYYY/MM/DD ,
>> this means I have to convert my rows to this format and do CAST,  I
>> spent hours to find solution, but I failed can someone help me with
>> example please. Thanks
>>
>

Thank you for your reply,
But looks like this is not standard sql and a lot of work just to format date
I thought this database is standard just like any other database,
Do you mean there is no function on this db to do this ?

Thanks

Raul wrote:

Converting the date would be fairly straightforward - just extract the
day, month and year parts and then combine them in proper format and do
cast.

Doing it in Delphi/.net you can just use regular string manipulation
functions. For SQL things are not that much more complex.

Are you always using 2 digit month and day ?

If so then something as simple as this would work in converting the
stiring string to new string format

concat(concat(substring(sourcefield,7 for 2),'/') ,
concat( concat(substring(sourcefield,1 for
2),'/'),substring(sourcefield,4 for 2)))

if you have single digit months and/or days then bit more work is needed
to find and extract the values

Raul




On 11/28/2012 9:48 AM, Linda_web wrote:
> Hello,
>
> I have column as varchar(10) that allows null, all rows format as US format, 11/20/2012,
> Now I want to get date different between current date today and above date
> After reading the sql help, I found the date should be as YYYY/MM/DD , this means I have to convert my rows to this format and do CAST,  I spent hours to find solution, but I failed can someone help me with example please. Thanks
>

EDB does support a large subset of SQL-2003 including the standard date
format which happens to be YYYY-MM-DD. In this case data was stored as
varchar and in different format so you need to handle it.

EDB might not have all the helper functions but most of those are not in
the standard anyways. You can extend the EDB yourself or request the
Elevatesoft to add/extend existing functionality.

You can also skip the concat and just + so the query would be

select cast( substring(mydate,7 for 4) + '-' + substring(mydate,1 for 2)
+ '-' + substring(mydate,4 for 2) as DATE) from mytable

which does not seem that much work IMHO

Raul






On 11/28/2012 11:41 AM, Linda_web wrote:
> Thank you for your reply,
> But looks like this is not standard sql and a lot of work just to format date
> I thought this database is standard just like any other database,
> Do you mean there is no function on this db to do this ?
>
> Thanks

in addition to Raul's answer: you can create a function using his code.
That makes life easier. If you need further help, just tell me.

Uli

Linda,

<< But looks like this is not standard sql and a lot of work just to format date >>

Why would it matter if it is standard SQL or not, as long as it does what you want?
Anyway, if for some reason it does matter, just use the || standard concatenation operator instead on the Concat function, it is also implemented in EDB.

<< I thought this database is standard just like any other database>>

What is not standard at all is the way you are storing dates - the standard SQL way for storing dates is the DATE SQL data type, not VARCHAR, and the 'format' concept doesn't even apply to the DATE type. If you were using the DATE format instead of VARCHAR you would now be able to retrieve any date and format it with any format specification you wanted.

About EDB being standard or not, ElevateDB was in fact developed according to the SQL 2003 standard, except for a few deviations that are documented in the manuals. When you say *as any other database* ... hmmm weelll.... what other databases do you know that are fully SQL-standard compliant with no deviations?
I'm not aware of any, but I'm curious

<< Do you mean there is no function on this db to do this ? >>
Do you mean a non standard function to convert dates from the non standard format you decided to use to another format?
It would be near to impossible to cover all the cases I guess... And it would not be standard anyway

--
Fernando Dias
[Team Elevate]

Thank you for your reply,

Well, I’m new to this edb, and trying to convert one app to it,
Here is what I mean by simple function (just used convert in sql server) without going to all substring dilemma,
What I mean, function like this should be in the db and I don’t have to look for a solution.

declare @mydate varchar(10)

set @mydate='3/10/2012'
--or set @mydate='03/10/2012' (same result)

select convert(datetime,@mydate)


I agree with you, the column should be date instead of varchar, but this is old program, I’m sql server dba, also I found many functionalities are missing like TOP, Truncate table , or maybe named something else, so far just trying to see how long will take to convert all queries, sp and tables to this edb (Couple hundreds objects)

I'm not comparing apple to apple, this is good db and wish the best for the company.

Thanks



Fernando Dias wrote:

Linda,

<< But looks like this is not standard sql and a lot of work just to format date >>

Why would it matter if it is standard SQL or not, as long as it does what you want?
Anyway, if for some reason it does matter, just use the || standard concatenation operator instead on the Concat function, it is also implemented in EDB.

<< I thought this database is standard just like any other database>>

What is not standard at all is the way you are storing dates - the standard SQL way for storing dates is the DATE SQL data type, not VARCHAR, and the 'format' concept doesn't even apply to the DATE type. If you were using the DATE format instead of VARCHAR you would now be able to retrieve any date and format it with any format specification you wanted.

About EDB being standard or not, ElevateDB was in fact developed according to the SQL 2003 standard, except for a few deviations that are documented in the manuals. When you say *as any other database* ... hmmm weelll.... what other databases do you know that are fully SQL-standard compliant with no deviations?
I'm not aware of any, but I'm curious

<< Do you mean there is no function on this db to do this ? >>
Do you mean a non standard function to convert dates from the non standard format you decided to use to another format?
It would be near to impossible to cover all the cases I guess... And it would not be standard anyway

--
Fernando Dias
[Team Elevate]