string to date conversion 1

[rds747]

how do I insert the string = '06092508262' (date and time) to a field (RODTCREATE) in sql server 2000 with datatype datetime?

What I've tried so far:
dim dtEntered as Date
...
dtEntered = mid(strLine, 68, 11)
...
Insert Into RORDER(RODTCREATE) Values('" & dtEntere & "')"

I suppose the conversion of char to date has to be done in VB rather than the SQL query as a convert/cast cannot be done in an Insert statement.

Please let me know if I should post this question in the SQL Server programming forum.

Thanks!

 

[rds747]

Actually dtEntered = mid(strLine, 68, 11) gives an error so i tried:
dtEntered = format(mid(strLine, 68, 11), yymmdd & hhmmss)
but that doesn't work either.

 

[gmmastros]

What date/time does this represent?

06092508262

That is not a format that I am familiar with. If I know what the format is, I can probably help.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

There are international issues when dealing with date/time's in sql server. To prevent problems, you should always use the ISO Unseperated Date Format (yyyymmdd hh:mm:ss). So, (now) would be...

'20061006 13:42:21'

If you can format your data to look like this, then you should be able to easily insert the data in to a sql server database.

Take a look at the mid function. Something like this...

Dim cTest As String
    
cTest = "06092508262"
    
Debug.Print "20" & Left(cTest, 6) & " " & Mid(cTest, 7, 2) & ":" & Mid(cTest, 9, 2) & ":" & Mid(cTest, 11, 2)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rds747]

Sorry George. I typed the numbers wrong.

060925082626 is YYMMDDHHMMSS

Y - Year
M - Month
D - Day
H - Hour
M - Minute
S - Second

 

[gmmastros]

Gotcha. I get it now. See my previous post.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rds747]

cTest = "06092508262"
    
Debug.Print "20" & Left(cTest, 6) & " " & Mid(cTest, 7, 2) & ":" & Mid(cTest, 9, 2) & ":" & Mid(cTest, 11, 2)

If I do it that way would I be able to insert that value into RODTCREATE which has datatype of DT?

 

[gmmastros]

Yes. That should work just fine. For a little light reading regarding international issues with Date/Time's, please see this thread: thread183-1240616



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Correction: Since you are 'hardcoding' 20 to the front of the string, this will only work until the year 2100 (or approximately 94 more years).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rds747]

Thank you, George. It worked perfectly. Will take a long at the thread for heavy reading.

In Ts yyyy-mm-dd hh:mm:ss[.fff] what does the f stand for?

 

[gmmastros]

I suppose the .fff represents fractional seconds. Since there are square brackets around it, it should be optional to include the fractional seconds.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rds747]

Didn't have to add the 20 in:

strdtEntered = Mid(strdtEntered, 1, 6) & " " & Mid(strdtEntered, 7, 2) & ":" & Mid(strdtEntered, 9, 2) & ":" & Mid(strdtEntered, 11)

to have RODTCREATE show: 2006-09-25 08:26:26.000

 

[gmmastros]

I recommend adding it anyway. If you're not comfortable hardcoding the 20, the get the value based on the current year. You can get the first 2 digits of the current year with...

CStr(CInt(Year(Now) / 100))

so...

strdtEntered = [!]CStr(CInt(Year(Now) / 100)) & [/!]Mid(strdtEntered, 1, 6) & " " & Mid(strdtEntered, 7, 2) & ":" & Mid(strdtEntered, 9, 2) & ":" & Mid(strdtEntered, 11)

The reason I suggest this is that SQL Server has a builtin 2 digit year cutoff, so that 99 is interpretted as 1999 and 02 is interpretted as 2002. Instead of relying on the 2 digit cutoff, it would be better to specify it instead. It's not a big issue, but is something that would need to be addressed within the next 40 years or so.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom