insert YYYYMMDD into Date field

Question

insert YYYYMMDD into Date field

ericb1

SSCrazy

Points: 2354
More actions
February 5, 2008 at 10:25 am

#71307

Using SQL 2000 and vbs. I have a program called WatchDirectory which will monitor directories on my network, and log activity to a database.
The program executes a vbs script which adds records to my table. The problem is getting the date variable from the program, which comes across as: YYYYMMDD (20080205) into a date field in sql.
The vbs script looks like this:
objConnection.Open "DSN=;uid=;password="
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT wdReason,wdPath,wdFileName,wdFileExtension,wdFileName_Full,wdFileSize,wdCreationDate,wdCreationTime,wdModifiedDate,wdModifiedTime,wdConfig,wdCurRun,wdCurEvt,wdDate,wdTime FROM WatchDirectory" , objConnection, adOpenStatic, adLockOptimistic
objRecordSet.AddNew
objRecordSet("wdReason") = strReason
objRecordSet("wdPath") = strFileD
objRecordSet("wdFileName") = strFileB
objRecordSet("wdFileExtension") = strFileE
objRecordSet("wdFileName_Full") = strFileF
objRecordSet("wdFileSize") = strFileSize
objRecordSet("wdCreationDate") = strFileCrDate
objRecordSet("wdCreationTime") = strFileCrTime
objRecordSet("wdModifiedDate") = strFileModDate
objRecordSet("wdModifiedTime") = strFileModTime
objRecordSet("wdConfig") = strConfig
objRecordSet("wdCurRun") = strCurRun
objRecordSet("wdCurEvt") = strCurEvt
objRecordSet("wdDate") = strDate
objRecordSet("wdTime") = strTime
objRecordSet.Update
objRecordSet.Close
objConnection.Close
Is there a way to tell this script how to format the variable of 20080205 into something SQL date field will accept, such as '2008/02/05'
Any help is appreciated, thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

The format YYYYMMDD is the ideal way to format a date to insert into a SQL server datetime column, provided is is a string and not an integer.

ericb1 SSCrazy Points: 2354 More actions · Answer 2

Thanks for the reply. My date field is formated as "Datetime", and when I try to just type '20080205' into the date field, it gives an error.

When I try typing '2008/02/05' into the field, it likes it. So is there something I need to change in my db, or do I fist need to change my vbs script to format the string first? Thanks!

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

This works in all versions of SQL Server with all regional settings, all settings of language, and all settings of datefirst.

select convert(datetime,'20080208')

If you are getting an error, if must be coming either from your front end application, or you are not passing the data in the format you think you are.