?
Solved

date Format in vb script

Posted on 2007-08-08
21
Medium Priority
?
4,074 Views
Last Modified: 2013-11-05
Hello expert,

I'm writing some scripting using VB script and  wanted to store the date in the format of dd/mm/yyyy  on SQL Server back-end. I did try to use the VB Format function - Format( Now(), "dd/mm/yyyy") - but getting type mismatch error. Can any one tell me any way around for this problem please.

Thanks in advance!!

0
Comment
Question by:ube100
  • 9
  • 6
  • 4
  • +1
20 Comments
 
LVL 33

Expert Comment

by:hongjun
ID: 19655447
What is the datatype of the field in SQL Server?
If it is datetime, then you can simply use Now()
There's no need format at all.

hongjun
0
 
LVL 33

Expert Comment

by:hongjun
ID: 19655449
Post your code.
0
 
LVL 3

Expert Comment

by:vladh
ID: 19655812
just a note - using Now() will format the date based on the local computers settings. I always like to parse the date and force it to be in certain format so it's consistent regardless how it's set on each individual computer...
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 33

Expert Comment

by:hongjun
ID: 19655836
I would prefer to use getdate() in the sql statement itself and let sql server do the job.

sql = "insert into ... values (... , getdate())"


hongjun
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 19659777
The Format function doesn't exist in VBScript, you have to write your own:
'=========
MsgBox TimeNow(Now)

strDate = Left(TimeNow(Now), 4) & Mid(TimeNow(Now), 9, 2) & Mid(TimeNow(Now), 6, 2)

MsgBox strDate

Function TimeNow(dDateTime)
      TimeNow =      Year(Now) & "-" &_
                        Right("00" & Month(Now), 2) & "-" &_
                        Right("00" & Day(Now), 2) & "-" &_
                        Right("00" & Hour(Now), 2) & "-" &_
                        Right("00" & Minute(Now), 2) & "-" &_
                        Right("00" & Second(Now), 2)
End Function
'============

Regards,

Rob.
0
 

Author Comment

by:ube100
ID: 19666478
Rob,

In your function you are passing a now() as a parameter and get the output. In my case I got a parameter which giving me the date value in the format of 'ddmmyyyy' and I just wanted to insert into a sql server in the format of 'dd/mm/yyyy'. This is how I'm doing:

objCmd.Parameters.Append objCmd.CreateParameter("DOB", adDBDate, adParamInput, 8, P(4))

Where P(4) = '13091965'

0
 
LVL 65

Expert Comment

by:RobSampson
ID: 19666928
I used the Now() function because you mentioned you had tried it in your original question text.
If you have ddmmyyyy stored in a variable already in VBScript, you can change it to dd/mm/yyyy by using the following:
' assume the variable strTheDate holds your ddmmyyyy formatted date
strTheDate = "10082007"
strNewDate = Left(strTheDate, 2) & "/" & Mid(strTheDate, 3, 2) & "/" & Right(strTheDate, 4)

Regards

Rob.
0
 
LVL 33

Expert Comment

by:hongjun
ID: 19667041
What is the datatype of DOB in sql server?
If it is datetime, then you can use getdate() straight.

Post your sql statement. I assume it's a insert.

... = "insert into yourtable ... values (.. , getdate())"

hongjun
0
 

Author Comment

by:ube100
ID: 19668567
hello hongjun,

This is my SP

CREATE PROCEDURE AddCustomer
(
      @BTAccountNumber             nVarChar(20),
      @BarcodeBTAccountNumber      nVarchar(50),
      @TelephoneNumber            nVarChar(22),
      @ContactID                  nVarChar(15),
      @ActivityID                  nVarChar(12),
      @Name                        nVarchar(25),
      @PostCode                  nVarChar(12),
      @DOB                        nVarchar(50),
      @NINO                        nVarChar(12),
      @IncomeSupport            bit,
      @JobseekersAllowance        bit,
      @PensionCredit                  bit,
      @CustomerConsent            bit,
      @ConsentDate                  nVarChar(20),
      @ImageFile                  nVarchar(100),
      @Status                  Int,
      @AdditionalInfo                  Bit,
      @Deleted                  Bit,
      @Burn                        nVarChar(50)
)
 AS

      Insert into Customers
            (BTAccountNumber, BarcodeBTAccountNumber, TelephoneNumber, ContactID, ActivityID, [Name], PostCode, DOB, NINO, IncomeSupport, JobseekersAllowance, PensionCredit, CustomerConsent, ConsentDate,  ImageFile, Status, DateCreated, AdditionalInfo, Deleted, Burn)
      Values
            (@BTAccountNumber, @BarcodeBTAccountNumber, @TelephoneNumber,@ContactID,@ActivityID, @Name, @PostCode,@DOB,@NINO,@IncomeSupport,@JobseekersAllowance,@PensionCredit,@CustomerConsent,@ConsentDate,@ImageFile,@Status, GetDate(), @AdditionalInfo, @Deleted, @Burn)
0
 
LVL 33

Expert Comment

by:hongjun
ID: 19668589
Looks like you are doing fine with the posted code. The datetime field "DateCreated" is using getdate() as its value. Any problems?

hongjun
0
 

Author Comment

by:ube100
ID: 19669433

Hongjun,

No problem with DateCreated because I'm not passing that value from the script as I'm doing it in the database.

The problem is when you are passing from the script to the parameters for the stored procedure.

ube100
0
 
LVL 33

Expert Comment

by:hongjun
ID: 19669513
I don't see a datetime field from the parameter list.
0
 

Author Comment

by:ube100
ID: 19670364
Thats I made a changes with SP so I can insert in this format - ddmmyyyy- as string and it worked. But I need this to be in datetime rather than nVarChar
0
 
LVL 33

Expert Comment

by:hongjun
ID: 19670453
Try this first.
There's no need to specify size for adDBDate.


objCmd.Parameters.Append objCmd.CreateParameter("DOB", adDBDate, adParamInput, , Now())

OR

objCmd.Parameters.Append objCmd.CreateParameter("DOB", adDBDate, adParamInput, , DateValue(Year(Now). Month(Now). Day(Now))


hongjun
0
 
LVL 33

Expert Comment

by:hongjun
ID: 19670460
Change back to date datatype.
0
 

Author Comment

by:ube100
ID: 19670874
Here this what I done:


             strTheDate = P(4)
      strNewDate = Left(P(4), 2) & "/" & Mid(P(4), 3, 2) & "/" & Right(P(4), 4)
objCmd.Parameters.Append objCmd.CreateParameter("DOB", adDate, adParamInput, 12, strNewDate)

Where P(4) have a value of '09121945'

I also changed back every thing to DateTime filed in SP and tbl.

When I output the value in the script : 09/12/1945

But in the database its writing as : 1945-09-12 00:00:00.000
 
I do not know why and can you tell me what can I do to  get this in this format : 09/12/1945


       
0
 
LVL 33

Expert Comment

by:hongjun
ID: 19670965
There's no problem with what is saved in sql server.
It's storing as the correct date except for the format isn't it?

Most importantly, it's storing the correct value.

hongjun
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 19680508
Perhaps the best way to overcome the ambiguity of the date format, and which field the SQL Sever picks as the day as opposed to the month, is to explicitly define the month with letters. For example, something like:
'=============
strTheDate = P(4)
Select Case Int(Mid(P(4), 3, 2))
      Case 1: strMonth = "JAN"
      Case 2: strMonth = "FEB"
      Case 3: strMonth = "MAR"
      Case 4: strMonth = "APR"
      Case 5: strMonth = "MAY"
      Case 6: strMonth = "JUN"
      Case 7: strMonth = "JUL"
      Case 8: strMonth = "AUG"
      Case 9: strMonth = "SEP"
      Case 10: strMonth = "OCT"
      Case 11: strMonth = "NOV"
      Case 12: strMonth = "DEC"
End Select
strNewDate = Left(P(4), 2) & "-" & strMonth & "-" & Right(P(4), 4)
objCmd.Parameters.Append objCmd.CreateParameter("DOB", adDate, adParamInput, 12, strNewDate)
'===========

That way, when the date gets to SQL, it should know exactly which field to use as the month.

Regards,

Rob.
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 750 total points
ID: 20522893
ube100,

Can you use my suggestion to change the month of your string to letters, so that SQL knows which field is the month, and converts the date accordingly?

Regards,

Rob.
0
 

Author Closing Comment

by:ube100
ID: 31407605
Sorry for the delay in geeting my answer back.

Thanks!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

Welcome back!  My apologies for taking so long to write part two of this series; it's been a long time coming!  As I promised in Part 1, this article will focus on how to locate those elusive AD properties that you are searching for.  Why is this us…
When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 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