ube100
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!!
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!!
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
sql = "insert into ... values (... , getdate())"
hongjun
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.
'=========
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.
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("DO B", adDBDate, adParamInput, 8, P(4))
Where P(4) = '13091965'
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("DO
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.
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
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
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,@ContactI D,@Activit yID, @Name, @PostCode,@DOB,@NINO,@Inco meSupport, @Jobseeker sAllowance ,@PensionC redit,@Cus tomerConse nt,@Consen tDate,@Ima geFile,@St atus, GetDate(), @AdditionalInfo, @Deleted, @Burn)
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,@ContactI
Looks like you are doing fine with the posted code. The datetime field "DateCreated" is using getdate() as its value. Any problems?
hongjun
hongjun
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.
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("DO B", adDBDate, adParamInput, , Now())
OR
objCmd.Parameters.Append objCmd.CreateParameter("DO B", adDBDate, adParamInput, , DateValue(Year(Now). Month(Now). Day(Now))
hongjun
There's no need to specify size for adDBDate.
objCmd.Parameters.Append objCmd.CreateParameter("DO
OR
objCmd.Parameters.Append objCmd.CreateParameter("DO
hongjun
Change back to date datatype.
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("DO B", 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
strTheDate = P(4)
strNewDate = Left(P(4), 2) & "/" & Mid(P(4), 3, 2) & "/" & Right(P(4), 4)
objCmd.Parameters.Append objCmd.CreateParameter("DO
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
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("DO B", 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.
'=============
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("DO
'===========
That way, when the date gets to SQL, it should know exactly which field to use as the month.
Regards,
Rob.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry for the delay in geeting my answer back.
Thanks!
Thanks!
If it is datetime, then you can simply use Now()
There's no need format at all.
hongjun