Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
elovelace256
Starting Member
12 Posts |
 Posted - 2004-07-06 : 09:23:36
|
| One of my tables has an open_date and close_date that are stored in unix time. I added two other colums z_opendate and z_closedate and I now would like to convert open_date and close_date from unix time and populate the new colums I added. Can anyone give me some insite on how to do this?Thanks.Eric |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-06 : 09:29:57
|
| Unix time??? Is that like BC? I'm assuming you're talking about Julian, correct? If so, just search the forums for Julian date and you should find some conversion scripts.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
elovelace256
Starting Member
12 Posts |
Posted - 2004-07-06 : 09:55:20
|
| Julian date I am not familiar with. Unix time counts the number of seconds from 1/1/1970. So 1/27/2003 4:59pm would be 1043704789 in unix time. Here is the function I have created to convert it but cannot find a way to impliment in sqlFunction CUnixToDatetime(ByVal plngSeconds As Long) As String If plngSeconds > 0 Then Dim ldteRetVal As Date = New Date(1970, 1, 1).AddSeconds(plngSeconds - 18000) Dim lstrRetVal As String = CType(ldteRetVal, String) Dim lintLength As Integer = lstrRetVal.Length Dim lstrAMorPM As String = lstrRetVal.Substring(lintLength - 2, 2) Dim lintLastColon As Integer = lstrRetVal.LastIndexOf(":") If lintLastColon = -1 Then Return lstrRetVal Else Return lstrRetVal.Substring(0, lintLastColon) & " " & lstrAMorPM End If Else Return "" End IfEnd Function |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-07-06 : 10:08:08
|
| [code]select dateadd(ss,1043704789 ,'1/1/1970')[/code] |
|
|
|
|
|
|
Help!!!! Convert from unix time