Link to home
Start Free TrialLog in
Avatar of ube100
ube100Flag for United Kingdom of Great Britain and Northern Ireland

asked on

date Format in vb script

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!!

Avatar of hongjun
hongjun
Flag of Singapore image

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
Post your code.
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...
I would prefer to use getdate() in the sql statement itself and let sql server do the job.

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


hongjun
Avatar of RobSampson
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.
Avatar of ube100

ASKER

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'

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.
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
Avatar of ube100

ASKER

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)
Looks like you are doing fine with the posted code. The datetime field "DateCreated" is using getdate() as its value. Any problems?

hongjun
Avatar of ube100

ASKER


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
I don't see a datetime field from the parameter list.
Avatar of ube100

ASKER

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
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
Change back to date datatype.
Avatar of ube100

ASKER

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


       
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
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.
ASKER CERTIFIED SOLUTION
Avatar of RobSampson
RobSampson
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ube100

ASKER

Sorry for the delay in geeting my answer back.

Thanks!