?
Solved

ADO : formatting date from DB

Posted on 2003-03-12
10
Medium Priority
?
174 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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

777 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