issue with formatting dates in vb 6 2

[SpiritOfLennon]

Hi,
I'm struggling with dates in vb6. I'm trying to take todays date and output in the format 'ddmmyy'.
I thought this would be relatively easy and currently have
the code below.

Dim today As Date
today = Format(Date, "ddmmyy")
MsgBox today

The output is 04/07/2751, so obviously I'm missing something.
Does anyone know how I can get today's date in the correct format for example 311007
?
Your help would be greatly appreciated.



SOL
I'm only guessing but my guess work generally works for me.

 

[HughLerwill]

You cannot Format a variable Dimmed as Date; you can only format the text produced when you report it.

Private Sub Command1_Click()

Dim today As Date
today = Now()

MsgBox Format(today, "ddmmyy")


End Sub

 

[gmmastros]

Or... you could change the data type for the today variable.

Dim today As [!]String[/!]
today = Format(Date, "ddmmyy")
MsgBox today

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SBerthold]

Just don't mislead yourself and do something like this:

Dim today As String
today = Date
MsgBox Format(today, "ddmmyy")


if the "today" variable is being used further, unless you know exactly for what, and under what conditions the "today" variable is being used.

 

[BobRodes]

I'm not sure I get your point, SB. Your code does the same thing as George's code, although there's an implicit conversion in it that isn't in George's. Are you saying that the self misleading has to do with trying to use date functions on the today variable down the line?

 

[SpiritOfLennon]

Thanks to all for your answers.


SOL
I'm only guessing but my guess work generally works for me.

 

[SBerthold]

Working with dates in a string variable is risky, especially if you do not know what the user settings for the date format in the country settings are at any particular time.

Dim today As String
today = Date

today could look something like:

06.01.2007

meaning possibly 5 Jan 2007 instead of 1 June 2007, as maybe the remaining of your code may depend upon, if you are expecting the date to always be in US or ISO 8601 format. Using date/double variables to store dates and as a minimum date conversion functions, lets you not have to worry about the user set format when calculating and working with dates (except in SQL Strings format to use US or ISO 8601 format)

 

[gmmastros]

(except in SQL Strings format to use US or ISO 8601 format)

Certainly, dates can be a source of problems for many programmers. Once you understand a few simple rules, you really shouldn't have that many problems.

I will not pretend to know everything about different types of databases, but I do know that this statement is misleading as it pertains to Microsoft SQL Server. In fact, my purpose for bringing this up is to clear away any misunderstandings.

The root of the problem is the way various countries specify their dates. In the US, it is month/day/year. In the UK it is day/month/year. There is a lot of mistaken people out there, even many MVP's. For example, take a look at this:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=366190&SiteID=1

Louis Davidson, a noted MVP for SQL Server says...

There are some ISO date time formates that are the best practice. Generally speaking, the formats are:

YYYYMMDD or YYYY-MM-DD

Luckily, Umachandar Jayachandran, a Microsoft employee working on the development of SQL Server later responded with:

There are only two unambiguous formats (one for date and another for datetime). Both are ISO formats.

YYYYMMDD - ISO unseparated date format
YYYY-MM-DDThh:mm:ss.nnn - ISO 8601 timestamp format

While Umachander has cleared the air, I think he has missed an unambiguous format. Specifically, YYYYMMDD hh:mm:ss.nnn.

To prove my point, open a Query Analyzer window and run this code.

Set DateFormat [!]MDY[/!]
[green]-- Returns [!]1[/!] and 1[/green]
Select Month('2007-01-02'), Month('20070102')

Set DateFormat [!]DMY[/!]
[green]-- Returns [!]2[/!] and 1[/green]
Select Month('2007-01-02'), Month('20070102')

The next part of the puzzle...
By now, you're probably thinking, "I've been using a date format of mm/dd/yyyy for several years now, and I've never had a problem". For me, this was also true until I sold my software in Canada, where they use dd/mm/yyyy. It took me a long time to figure out why, but I finally did.

SQL Server is a client/server technology. In most cases, the application is running on a different computer than the database. As such, it is possible for the regional settings to be different between the machines. In SQL Server, each login has a language. You can see what the language setting is by running the following command:

[tt][blue]Select @@language[/blue][/tt]

To see what your dateformat is:

[tt][blue]Select DateFormat from syslanguages Where Name = @@Language[/blue][/tt]

The important part to realize is that each login can have a different language setting. Therefore, it's possible that a query built in VB would work for one user but not another. By using the ISO Unseparated Date Format (yyyymmdd), this will not happen.

The last piece of the puzzle...
Many of us know that we should be using the ADO Command object when interacting with the database. Equally important is to use the ADO Parameter object in conjunction with the command object. When you use a parameter object and specify a data parameter, all of the silly date conversions happen locally. This is important. If your local regional settings are dd/mm/yyyy but your SQL Server login is configured for mm/dd/yyyy, it will not matter. When setting the value of the parameter object, a conversion from string to date occurs locally, using your computers regional settings. Then, by the time the sql command is actually executed, you are dealing with a proper date, so there is no ambiguity. This is a benefit of using the command object.

Summary....
When dealing with dates and SQL Server, it is best to use a command object. If that is not practical, then using the ISO Unseparated Date Format (yyyymmdd) is the ONLY safe way to handle dates.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TysonLPrice]

This is a nice write up on using dates in VB:

http://www.vbforums.com/showthread.php?t=489286

 

[BobRodes]

Isn't

select month(39082)

also unambiguous, though cumbersome?

 

[gmmastros]

Bob.

I get what you are saying (I think). Unfortunately, there is a problem with that.

In SQL Server, Day 0 is Jan 1, 1900. In VB, Day 0 is December 30, 1899.

Ex:

In Query Analyzer, run this:
Select Convert(DateTime, 39082) [green]-- 2 Jan 2007[/green]

In VB, run this...
MsgBox CDate(39082) [green]-- 31 Dec 2006[/green]


Using an integer (actually a long) number of days will surely cause a lot of headaches.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SBerthold]

Yes, sorry about that. That quote was more than misleading. Good pointing it out.
I generally tell everyone to use the ISO 8601 format, and I do not know of any "good" dbms which doesn't understand this format - with or with out the dash separators.
At that moment I was possibly thinking VB "string" variable dates: "if you are expecting the date to always be in US or ISO 8601 format" and copied and pasted too fast, as seen by the funny wording later ("SQL Strings format"?)

Most of my general customers use non-US dates and decimal seperators, so that is the first thing I had to learn.

Anyways, yes, SQL Server client is local sensitive by default, as I see that always in my system, when using the Query Analyzer locally or just executing an sql statement through in VB (as opposed to some other dbms's - I haven't been using Sql Server that long).

>Many of us know that we should be using the ADO Command object when interacting with the database.

Well, I cannot say this would always apply, especially if the user has no direct influence of the statements being executed.

>"Set DateFormat DMY"

I would call this "abuse" and not setting standards.
But yes, it can happen, and I never considered something like this, - so, you are right, away with the seperators!

 

[gmmastros]

>"Set DateFormat DMY"

I would call this "abuse" and not setting standards.

I wasn't clear. Personally, I have never written any production code that uses the Set DateFormat command.

I used that as an example where YYYY-MM-DD is NOT always safe. I could just as easily have shown this code.
[tt][blue]
Set language us_english
Select Month('2007-01-02')

Set language british
Select Month('2007-01-02')
[/blue][/tt]

Using that in a query would be just as bad as setting the dateformat. However, it is important to realize that every sql server login has a language associated with it. Furthermore, the language has a dateformat associated with it.

So, eventhough you don't specify the language (or the dateformat), it does exist. You can run this query to see what it is set to.

[COLOR=blue]Select[/color] sysxlogins.name, 
       sysxlogins.language, 
       syslanguages.dateformat 
[COLOR=blue]from[/color]   sysxlogins 
       [COLOR=blue]inner[/color] [COLOR=blue]join[/color] syslanguages 
         [COLOR=blue]on[/color] sysxlogins.language = syslanguages.name

You can change the default language for a server, such that when you create a new login, it will use the newly set default language. You can also change the language for a user, by using sp_configure. Finally, you can temporarily change the language by using the SET Language command.

For example, if you wanted to know the russian word for november...

Set Language Russian

Select DateName(Month, GetDate())

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BobRodes]

<there is a problem with that.

Wow. Thanks for pointing that out.

For the edification of all who are presently unaware, the Russian name for November is ??????.

 

[gmmastros]

Tek-tips does not support unicode in a post. Sure, it looks goon in a 'compose post' window. Also looks good in the preview window. By the time it becomes a 'real' post, it is replaced with question marks.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BobRodes]

Rats. It looked fine in the preview window.

 

[SBerthold]

>I wasn't clear

I think you were. Therefore I said I would now do away with the seperators.
However, I still cannot understand someone imposing this onto external, "foreign" applications after a standard was long set, long used, and to even take the chance where those other external applications have been faithfully sticking to those long established and used (ISO) standards - unless this was a totally encapsuled system - otherwise, it seems to be a right-out stupid idea and risk.
But, fact is, it can be still done.

Anyhow, thanks for the sql server info.