Convert to Date

I have a variable that I am passing to a stored procedure from a
propritery software application. The variable it passes is the number
of days that have passed since 1/1/1900. I cannot seem to locate a
convert statement that is applicable. Any guidance would be greatly
appreciated.

ThanksDATEADD(DAY,@.var,'19000101')

or

DATEADD(DAY,@.var,'18991231')

depending on whether 1900-01-01 is represented by 0 or 1.

--
David Portas
SQL Server MVP
--

Convert Int to Time

I have a DB that gets telephone call information from a telephone switch. We use 3rd part software and a db to capture all of this. However for some reason most of the fields are stored as int DateTime is stored as an int. I am able to convert most of the date field from int by doing select convert(datetime,convert(varchar(8), localday)) that works fine the int are in the db as 20061029 for 10-29-2006.
How ever I can't convert the columns starttime and endtime. They are also stored as int but
They don't seem to store a date.
For instance for the date 9/29/2006 the start time of one particular call is
1159533056 and the endtime is 1159533264 All i need to get from this is the time since I already have the date from another column. I can't figure out what this data represents.
How can I convert this to datetime?

Thanks!

Quote:

"The datetime and smalldatetime data types are stored internally as integers. The datetime data type is stored as a pair of four-bytes integers, which together represent the number of milliseconds since midnight of January 1, 1753. The first four bytes store the date, and the second four bytes store the time. The smalldatetime data type is stored as a pair of two-byte integers, which together represent the number of minutes since midnight on January 1, 1900. The first two bytes store the date, and the second two bytes store the time."

Also, check out this: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp (see the first table on that page).

So, if your columns' data types are datetime or smalldatetime (but not "int", which would be bizarre), you don't need to concern yourself with converting it manually and/or trying to figure out what exactly this integer means. Just call the appropriate version of CONVERT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp).

|||

It looks to me like your integers are storing number of seconds since the epoch, or 1 January 1970. This is pretty common on UNIX systems. (Cf. http://en.wikipedia.org/wiki/Unix_time)

Try:

declare @.d1 datetime
declare @.d2 datetime
set @.d1 = dateadd(second, 1159533056, '1 January 1970')
set @.d2 = dateadd(second, 1159533264, '1 January 1970')
print @.d1
print @.d2

Result:

Sep 29 2006 12:30PM
Sep 29 2006 12:34PM

Cheers,
-Isaac

|||

Great that did it. yes they are stored as integers. I hate this db everything that is not text is stored as INT I mean Everything.
I have had to create several calculated fields for them. It's 3rd party db if i modify existing colums it won't function.

What would be the best way to create a calculated field for this data?
Thanks again

|||nevermind i got it by doing this...

alter TABLE [CDRMAin] add
[starttimetimecalc] AS dateadd(second, endtime, '1 January 1970')|||I just realized that this code is giving me the proper date from my date that is stored as an int, but it is not giving me the proper time
for this datetime as an int 1163517315
i get 2006-11-14 15:15:15.000, the correct time should be 10:16 am
the code i used to create my table is
alter TABLE [CDRMAin] add
[starttimetimecalc] AS dateadd(second, endtime, '1 January 1970')

how can i correct this.
thanks

Convert Int to Time

I have a DB that gets telephone call information from a telephone switch. We use 3rd part software and a db to capture all of this. However for some reason most of the fields are stored as int DateTime is stored as an int. I am able to convert most of the date field from int by doing select convert(datetime,convert(varchar(8), localday)) that works fine the int are in the db as 20061029 for 10-29-2006.
How ever I can't convert the columns starttime and endtime. They are also stored as int but
They don't seem to store a date.
For instance for the date 9/29/2006 the start time of one particular call is
1159533056 and the endtime is 1159533264 All i need to get from this is the time since I already have the date from another column. I can't figure out what this data represents.
How can I convert this to datetime?

Thanks!

Quote:

"The datetime and smalldatetime data types are stored internally as integers. The datetime data type is stored as a pair of four-bytes integers, which together represent the number of milliseconds since midnight of January 1, 1753. The first four bytes store the date, and the second four bytes store the time. The smalldatetime data type is stored as a pair of two-byte integers, which together represent the number of minutes since midnight on January 1, 1900. The first two bytes store the date, and the second two bytes store the time."

Also, check out this: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp (see the first table on that page).

So, if your columns' data types are datetime or smalldatetime (but not "int", which would be bizarre), you don't need to concern yourself with converting it manually and/or trying to figure out what exactly this integer means. Just call the appropriate version of CONVERT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp).

|||

It looks to me like your integers are storing number of seconds since the epoch, or 1 January 1970. This is pretty common on UNIX systems. (Cf. http://en.wikipedia.org/wiki/Unix_time)

Try:

declare @.d1 datetime
declare @.d2 datetime
set @.d1 = dateadd(second, 1159533056, '1 January 1970')
set @.d2 = dateadd(second, 1159533264, '1 January 1970')
print @.d1
print @.d2

Result:

Sep 29 2006 12:30PM
Sep 29 2006 12:34PM

Cheers,
-Isaac

|||

Great that did it. yes they are stored as integers. I hate this db everything that is not text is stored as INT I mean Everything.
I have had to create several calculated fields for them. It's 3rd party db if i modify existing colums it won't function.

What would be the best way to create a calculated field for this data?
Thanks again

|||nevermind i got it by doing this...

alter TABLE [CDRMAin] add
[starttimetimecalc] AS dateadd(second, endtime, '1 January 1970')|||I just realized that this code is giving me the proper date from my date that is stored as an int, but it is not giving me the proper time
for this datetime as an int 1163517315
i get 2006-11-14 15:15:15.000, the correct time should be 10:16 am
the code i used to create my table is
alter TABLE [CDRMAin] add
[starttimetimecalc] AS dateadd(second, endtime, '1 January 1970')

how can i correct this.
thanks

Convert from Access DB to SQL DB....

Dear All,

I would like to convert from Access To SQL DB undervisual Studio.Net 2005...

How can I do it easily, or if there any software to do this automaticlly, please your help..

Awaiting your valuable reply.

Many thanks in advance for your cooperation and continuous support...

What version of MS Access are you using?

Most of the Access versions like XP/2003 all have an upgrade wizard that allows you to convert your existing database to SQL Server.

In Access 2003 I believe you can use the Upsizing Wizard which can be accessed from Tools -> Database Utilities -> Upsizing wizard

Alternatively you can always import all the tables/data/procedures into SQL Server using Enterprise Manager/Management Studio.

|||

Microsoft guide you to good support for this:

The easiest way to convert an Access database to SQL Server is to use the Upsizing Wizard. The Upsizing Wizard:

?Preserves database structure, including data, indexes, anddefault settings.?Automatically converts Access validation rules and defaultsettings to the appropriate SQL Server equivalents.?Maintains table relationships and referential integrityafter you upsize.

To run the Upsizing Wizard in Access 2000, on theTools menu, point toDatabase Utilities, and then clickUpsizing Wizard.

You will got details information from his link

http://support.microsoft.com/kb/307598

Ahsan

|||

Thanks for your replying...

I've got Access 2007, and I've done this way.

However, it asks me to choose the Sql server and it gives me connection error when I use local !!!, and I'm using the Visual Studio.Net 2005 which has Sql Express Edition..

So, I would like to migrate the Access DB to my project in Visual Studio 2005 ( ASP.net -> C# language ).

I'd like you to know also that I haven't installed SQL Server 2005 or 2003. Do u think that I need it to do this?

Many thanks...

|||

ali Manshow:

However, it asks me to choose the Sql server and it gives me connection error when I use local !!!, and I'm using the Visual Studio.Net 2005 which has Sql Express Edition..

what is your SQLEXPRESS instance called as this should be the name you should be typing into access

ali Manshow:

I'd like you to know also that I haven't installed SQL Server 2005 or 2003. Do u think that I need it to do this?

SQLExpress is enough and you would not need to install SQL Server 2000/2005

|||

You should not install the SQL server 2005 because visual studio 2005 has built in Sql Server 2005 Express. So you can easily use this.For connect with sql express you simply use ./SQLExpress in server field.

Ahsan

|||

Thanks I have tried this, but it still gives me connection error because of wrong Sql Server or Access Denied..??

Do u think that I have to change the settings for SQL login:

There are 3 options:

1) Local Service.

2) Local System.

3) Network Service.

Which options shall I choose.

Or do I need to change any settings in Access 2007 DB.

Thanks so much...