Hi all,
I have a problem converting datetime to integer (and than back to
datetime).
Depending whether the time is AM or PM, same date is converted to two
different integer representations , which holds as true on reversal
back to datetime.
AM Example:
declare @DI integer; declare @DD datetime
set @DI = cast(cast('3/12/2003 11:34:02 AM' as datetime) as integer)
set @DD = cast (@DI as datetime)
print @DI; print @DD
Result:
37690
Mar 12 2003 12:00AM
PM Example:
declare @DI integer; declare @DD datetime
set @DI = cast(cast('3/12/2003 11:34:02 PM' as datetime) as integer)
set @DD = cast (@DI as datetime)
print @DI; print @DD
Result:
37691
Mar 13 2003 12:00AM
Now, this is not a big problem if I knew that this is how it is
supposed to work. Is this how SQL Server is supposed to work? 3 118837
Nikola (ni*****@hotmai l.com) writes: AM Example:
declare @DI integer; declare @DD datetime set @DI = cast(cast('3/12/2003 11:34:02 AM' as datetime) as integer) set @DD = cast (@DI as datetime) print @DI; print @DD
Result: 37690 Mar 12 2003 12:00AM
PM Example:
declare @DI integer; declare @DD datetime set @DI = cast(cast('3/12/2003 11:34:02 PM' as datetime) as integer) set @DD = cast (@DI as datetime) print @DI; print @DD
Result: 37691 Mar 13 2003 12:00AM
Now, this is not a big problem if I knew that this is how it is supposed to work. Is this how SQL Server is supposed to work?
Apparently, SQL Server rounds to the nearest wholest int. I wouldn't
say this makes much sense to me.
Then again, I have to admit that I don't really see the point with
converting datetime values to integer.
In any case, the workaround should be simple, first chop of the
time portion.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
"Erland Sommarskog" <so****@algonet .se> wrote: Nikola (ni*****@hotmai l.com) writes: AM Example:
declare @DI integer; declare @DD datetime set @DI = cast(cast('3/12/2003 11:34:02 AM' as datetime) as integer) set @DD = cast (@DI as datetime) print @DI; print @DD
Result: 37690 Mar 12 2003 12:00AM
PM Example:
declare @DI integer; declare @DD datetime set @DI = cast(cast('3/12/2003 11:34:02 PM' as datetime) as integer) set @DD = cast (@DI as datetime) print @DI; print @DD
Result: 37691 Mar 13 2003 12:00AM
Now, this is not a big problem if I knew that this is how it is supposed to work. Is this how SQL Server is supposed to work?
Apparently, SQL Server rounds to the nearest wholest int. I wouldn't say this makes much sense to me.
Then again, I have to admit that I don't really see the point with converting datetime values to integer.
In any case, the workaround should be simple, first chop of the time portion.
VB6 will allow a similar translation and it has the same problem: a real
number is returned where the fractional (i.e. right of the decimal point)
part represents the time. So, converting from datetime to an int carries a
hidden conversion that rounds to get the integer. Check this out (I used
money, although I assume float or real would suffice).
declare @d datetime
declare @n money
set @d = '3/12/2003'
set @n = convert(money, @d)
print convert(varchar , @n) + ' - ' + convert(varchar , @d)
set @d = '3/12/2003 11:34 AM'
set @n = convert(money, @d)
print convert(varchar , @n) + ' - ' + convert(varchar , @d)
set @d = '3/12/2003 11:34 PM'
set @n = convert(money, @d)
print convert(varchar , @n) + ' - ' + convert(varchar , @d)
set @d = '3/13/2003'
set @n = convert(money, @d)
print convert(varchar , @n) + ' - ' + convert(varchar , @d)
Craig
Erland Sommarskog (so****@algonet .se) writes: Apparently, SQL Server rounds to the nearest wholest int. I wouldn't say this makes much sense to me.
Then again, I have to admit that I don't really see the point with converting datetime values to integer.
In any case, the workaround should be simple, first chop of the time portion.
Actually there is an even simpler workaround:
declare @d datetime
declare @i int
SELECT @d = '20020202 11:59:00'
SELECT @i = convert(float, @d)
SELECT @i
SELECT @d = '20020202 12:01:00'
SELECT @i = convert(float, @d)
SELECT @i
This works, because when convering from float to int, truncation occurs...
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Justin Wong |
last post by:
CREATE PROCEDURE dbo.Synchronization_GetNewRecords
(
@item varchar(50),
@last datetime
)
AS
SET NOCOUNT ON
|
by: js |
last post by:
I have tables with columns that stores datetime data in int format on
SQL server 2000. For example, the datetime for '4/5/2004
00:00:00.000am' is stored as 1081180800. "4/4/2004 11:59:59.000pm' is
1081180799. I need to generate reports that display datetime columns
in "mm/dd/yyyy hh:mn:ss" format with am or pm at the end. Bellow is
my query statment.
select iorg_name as org, ref_num as , c_first_name as
, c_last_name as , sym as...
|
by: John Dann |
last post by:
I need to convert some datetime data currently stored in .Net DateTime
type to the older double style (for compatibility with a legacy
control). Is there an intrinsic function to do this or, if not, could
someone please enlighten me as to the algorithm that would be needed
to code the conversion explicitly.
TIA
John Dann
|
by: Rich Robinson |
last post by:
Hi,
I have a web service method which takes a DateTime type as a parameter.
The service is UK based, and the dates are passed in to the service in the
UK format dd/MM/yyyy.
On a recent install, the web service method will not accept the UK date
format, erroring with a System.Argument exception, but would take US
formatted dates.
|
by: =?Utf-8?B?bWF2cmlja18xMDE=?= |
last post by:
Hi,
I have time in the following format...
Tue, 8 May 2007 13:38:00 EDT
How can I convert it DateTime? Convert.ToDateTime works fine if the Time
Zone is GMT like
Tue, 8 May 2007 13:38:00 GMT
| |
by: joyjignesh |
last post by:
hi
i have make a report with mshflexgrid. the report between two date. when i have written query
.open"select * from tablename where t_date>=convert(datetime,' " & text1.text &"') and t2_date>=convert(datetime,' " & text2.text &"'),cn,,,adcmdtext
but there is a syntex error to converting datetime format into character string
plz help me
|
by: Matt |
last post by:
Hi all,
So a lot of digging on doing this and still not a fabulous solution:
import time
# this takes the last_modified_date naive datetime, converts it to a
# UTC timetuple, converts that to a timestamp (seconds since the
# epoch), subtracts the timezone offset (in seconds), and then
converts
|
by: tragic54 |
last post by:
Alright so i've done this in some of my recent programs with option strict on and for some reason When i debug and select the option from the combo box, it crashes and gives me a 'Input String was not in the correct format' error. Ive tried declaring the variables as strings and converting the textbox to integer from there and still get the same thing. I'm just trying to get a value to store it in that variable so i can use it for later...
|
by: SnehaAgrawal |
last post by:
Hi I get the error "Syntax error converting datetime from character string "for
the following sql statement in Stored proc.
SET @dynamicSQL =N'SELECT CUSTID,SHCodeLink FROM AccountMaster where CONVERT(DATETIME,OPDATE,105) < = '''+CONVERT(DATETIME,@ACCYEAR,105) +''' And GLCode = '+@DIVGLCODE
EXEC(@dynamicsql)
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |