Need help wiht Date format. 1

[jameslx]

I am using a variable DateStart to write current date time to a SQL table field defined as Char(20).
DateStart = Now()
The problem arises when this field of 20 character length gets the value where the small date time on the client computer is defined for yyyy. I have a truncate error returned.
I have tried to use the function below in various ways and always get an error "variable is undefined :'format'"
DateStart = format(Now(),"MM/DD/YY HH:NN:SS AMPM&quot
or writng to the table with this as the variable for the value in the insert statement.
format(DateStart,"MM/DD/YY HH:NN:SS AMPM&quot
The vbs is not part of asp. It is run from a batch file.
Any ideas?

 

[mwolf00]

It is best to store date/time values in SQL in date/time fields. It is much more efficient. Having said that...

DateStart = formatDateTime(Now(),vbShortDate) Get the Best Answers! faq333-2924
Happy 2003!
mikewolf@tst-us.com

 

[jameslx]

mwolf00 Thanks for the reply. I still get variable is undefined : 'format' when I run the process. I don't know if this makes a difference or not, but I am testing the script with Command Prompt.

 

[jameslx]

Sorry disregard last comment. Left the old statement in as well as the new. Worked great. Thanks. Liked the FORMAT function I use in VB, but it doesn't seem to work with script. Again Thanks

 

[jameslx]

The state ment formatDateTime(Now(),vbShortDate) works fine, but again if the stem time on the client PC is set for date mm/dd/yyyy the complete field will be 22 characters long. Can't change the format of the defined SQL field which is text and used by other programs. I will just have to use datepart and build my date in it. If you have any other ideas I would welcome them.

 

[mwolf00]

My best solution is to make your table store the dates as datetime not char(20). Any chance you can switch it? It is much more efficient... Get the Best Answers! faq333-2924
Happy 2003!
mikewolf@tst-us.com

 

[jameslx]

mwolf00 Thanks. Too many programs using the fields in SQL. Due to the four different systems accessing the fields they were definned as text. I am not sure why we were set up this way, but I was told SQL can due some funky things when you compare date fields with date entries from other sources. Any way I solved the problem as long as the system date is not set to display year first.

DateStart = formatDateTime(Now(),vbShortDate)
if len(DateStart) > 8 then DateStart = Left(DateStart,6)+right(DateStart,2)
TME = formatDateTime(Now(),vbLongTime)
DTEStart = DTEStart +" "+TME

Chances are since we are not using the programs outside of the US people will not be setting their date format to display the year first.
Thanks for the help.

 

[jonscott8]

FWIW, VBS contains no Format function.

http://msdn.microsoft.com/library/en-us/script56/html/vtorifunctions.asp

Personally, I would do this on the DB side, probably thru a SP. However, if you prefer to do this on the front end, use DatePart to extract the elements you need and build your string or use the individual Month,Day,Year,etc.. functions to create it. Jon Hawkins

 

[jameslx]

Thanks jonscott8. Most all of the information we use in our CrystalReports are doen with stored Procs, but this one is simply indicating that the process is running and what day and time. It keeps from having another process from grabbing the information already being processed. Another process looks at the date and time and compares it with what new parameters are in the control table, and waits till the program has finished and deletes the date and time from the table on SQL. The method I am using seems to work fine and since it will run on the server side we will use standard date time format there of month,day year. If the date part is greater than 8 characters then we process it and slter the date to have eight characters mm/dd/yy. Thanks for your reply.