Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ADO : formatting date from DB

Posted on 2003-03-12
10
Medium Priority
?
181 Views
Last Modified: 2010-05-01
Hi,
i have an ADO control and a MSHFlexgrid that displays data :
adodc1.recordsource = "select format(mydate) as date from mytable"
Since the format function is not given a second argument,
it uses the regional settings.
It works with Access, but on SQL Server, the format function does
not exist. How to replace it ?
Coding an user-defined function with same name ? how ?

I looked at the existing functions Cast and Convert, but they do not
allow to use regional settings.

I would like to avoid rewriting all the sql queries in my vb project ;)

Thanks.
0
Comment
Question by:batman27
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 2

Expert Comment

by:uncle_med
ID: 8118523
Well you won't be able to write a format function of your own with that name even if you wanted to because format is a reserved keyword. How about writing a new function, say Myformat and then do a global replace of
Format(mydate) with MyFormat(mydate).
The function could look something like this:

Function MyFormat(byval vSQLDate) as variant

That way you can return the date in any format you like

An example here for you:

MyFormat = Day(vSQLDate) & "-" & Month(vSQLDate) & "-" & Year(vSQLDate)


Hope it helps
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 8118525
Hi batman27,

You either have to perform the formatting in VB when you display the results, or put up with the way that cast/convert work. The problem being that you cannot specify the local computer's regional settings when running the query on the server.

This is a good reason for always using standard SQL in your application to avoid cross-platform differences in implementation. When I work with dates in this way, I always return the date field from SQL server unmodified and handle the formatting when displaying the information.

Tim Cottee MCSD, MCDBA, CPIM
http://www.timcottee.tk 

Brainbench MVP for Visual Basic
http://www.brainbench.com

Experts-Exchange Advisory Board Member
0
 
LVL 3

Expert Comment

by:OblivionSY
ID: 8118595
When selecting dates use this code and all will be fine, after hitting my head for few days as to why a win95 machine was handling the SQL differently it was down to date formats even though the system settings were the same. Anyways

Dim MyDate as date
MyDate = dateAdd("d", -100, Date) 'Set the variable as 100 days ago from today

"SELECT * FROM Orders WHERE OrderDate >= #" & _
   format(MyDate, "mm-dd-yy") & "#"


This ensures the SQL is taken in the American date format which SQL needs. The same would applyto updates and additions. It works for me so good luck
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 3

Expert Comment

by:OblivionSY
ID: 8118633
PS,

to avoid re-writing them all, use the find and replace for the # marks and can find  a pattern which would help replace most of them eg

WHERE MyDate = #" & TheDate & "#"

search for [#" &] and replace with
[#" & format(

search for [& "#] and replace with
[, "mm-dd-yy") & "#]

Something like that should work
0
 

Author Comment

by:batman27
ID: 8118861
just a test...
0
 

Author Comment

by:batman27
ID: 8118862
some answers/comments on your suggestions :

uncle_med :

I meant user-defined functions inside SQL Server, not vb functions...



OblivionSY :

'#' is not recognized in SQL Server.
Moreover, I want to format the output displayed in the grid.
That's why I used the format function INSIDE the sql query.


TimCottee :

I agree with you. Now, suppose that I use this code :

adodc1.recordsource = "select * from mytable where mydate>'01.01.2003'"

In this case, the ado recordset is not formatted.
In order to display the dates with regional format, I will have to apply
the format VB function to the entire column of the grid, cell by cell... ?

Another question :

Which format will I use for the where clause ? Will SQL server understand
'dd.mm.yyyy' or 'mm.dd.yyyy' in the query "...where mydate>'01.01.2003'" ?
Does it use its own local settings ?

0
 
LVL 3

Expert Comment

by:OblivionSY
ID: 8118947
The SQL i use needs to be in "mm-dd-yy" format, but some are "clever" enough to realise that there is not a 25th month, yet some are not so... have experience problems with both!



0
 
LVL 3

Expert Comment

by:OblivionSY
ID: 8118959
The SQL i use needs to be in "mm-dd-yy" format, but some are "clever" enough to realise that there is not a 25th month, yet some are not so... have experience problems with both!



0
 
LVL 43

Accepted Solution

by:
TimCottee earned 375 total points
ID: 8119013
Hi, %%QUESTIONER%%

Personally I always use yyyy-mm-dd in order for it to be completely unambiguous. There is also an argument for using something like dd mmm yyyy as this is also unambiguous though it depends more on locale settings. yyyy-mm-dd has many advantages in that you can cast it to a character or integer and still use it for sorting (assuming you remove the - characters when casting to an integer).

Tim Cottee MCSD, MCDBA, CPIM
http://www.timcottee.tk 

Brainbench MVP for Visual Basic
http://www.brainbench.com

Experts-Exchange Advisory Board Member
0
 

Author Comment

by:batman27
ID: 8129421
Thank you all !
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month11 days, 8 hours left to enroll

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question