logic needed for date

Question

logic needed for date

Chinna

SSCertifiable

Points: 5408
More actions
February 27, 2008 at 9:51 am

#187148

select getdate() as todaydate, cast(getdate() as varchar) "diff format" ,cast(getdate()+1 as int) as intformat--- as convert
todaydate diff format intformat
----------------------- ------------------------------ -----------
2008-02-27 11:35:37.750 Feb 27 2008 11:35AM 39504
(1 row(s) affected)
select cast (39504 as datetime) as abc ,getdate() todaydate
abc todaydate
----------------------- -----------------------
2008-02-28 00:00:00.000 2008-02-27 11:50:28.123
(1 row(s) affected)
so i coudlnt understand the logic for the int converting to data and the date to 5 digits int plz let me know the logic over there....plz thkz in advance
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman

Viewing 15 posts - 1 through 15 (of 20 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

when converting a number to datetime, the date portion is left of the decimal place, and the time portion is to the right of the decimal point.

I think it really is stored as a decimal, and formatted for display based on settings or convert() applied to it.

the definition is the number of days since 01/01/1900....approximately 365 x 108 years...

[font="Courier New"]

select

getdate() as TheDate,

convert(decimal(10,4),getdate()) As decimaldate

TheDate decimaldate

----------------------- ---------------------------------------

2008-02-27 11:54:39.630 39503.4963

[/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

Actually, it's stored as a "fixed place float"...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Adam Haines SSC-Insane Points: 23217 More actions · Answer 3

datetime fields are stored as float values. You can verify this by converting a datetime to a float, as shown below.

declare @dt datetime

set @dt = '2008-02-28 00:00:00.000'

select cast (@dt as float)

My blog: http://jahaines.blogspot.com

Adam Haines SSC-Insane Points: 23217 More actions · Answer 4

Well #77 beat me too it 😛

My blog: http://jahaines.blogspot.com

Chinna SSCertifiable Points: 5408 More actions · Answer 5

thkz but did it works how abt time is it the same way is it in second of the day or what format is that ???

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman

Chinna SSCertifiable Points: 5408 More actions · Answer 6

but whats the logic is it the number of days from 1990 yr is that the one i jus want to know that logic ....

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman

Adam Haines SSC-Insane Points: 23217 More actions · Answer 7

Maybe this example will help clarify. You will note the date portion is always to the left of the period. The hours/min/sec etc are on the right. Use the code below to change the current date and time into a float value the cast it back the other way.

select cast(getdate() as float) [datetime w hr_min_sec], cast(39503 as datetime) [date w 0s]

As far how the decimal represents the time, I really have no idea that's why I convert it. Maybe someone else has that answer.

My blog: http://jahaines.blogspot.com

Adam Haines SSC-Insane Points: 23217 More actions · Answer 8

It starts from 1900 and moves forward. The result from below is 0. Anything below '1900-01-1 00:00:00.000' returns a negative number.

declare @dt datetime

set @dt = '1900-01-1 00:00:00.000'

select cast(@dt as float)

My blog: http://jahaines.blogspot.com

Lowell SSC Guru Points: 323518 More actions · Answer 9

kurraraghu (2/27/2008)
but whats the logic is it the number of days from 1990 yr is that the one i jus want to know that logic ....

yes, the logic is the number of days since 01/01/1900. 39503 days so far...and the right of the decimal is the portion of a day... so .5000 would be exactly have a day/12 hours, so it

39503.5000 would be 12 noon for today...

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Chinna SSCertifiable Points: 5408 More actions · Answer 10

declare @dt datetime

set @dt = '1900-01-1 00:00:00.000'

select cast(@dt as float)

----------------------

0.105555555555556

(1 row(s) affected)

here how is the time calculate is it seconds of the day or milliseconds smthng like could plz let me knoe i have got the date logic thkz to all u guys ....

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman

Chinna SSCertifiable Points: 5408 More actions · Answer 11

thkz lowell gotch u ....n thkz to all of them...

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman

Chinna SSCertifiable Points: 5408 More actions · Answer 12

but lowell i havea ques

select cast (39504.5023 as datetime) as abc ,getdate() todaydate

abc todaydate

----------------------- -----------------------

2008-02-28 12:03:18.720 2008-02-27 12:20:13.577

(1 row(s) affected)

so how did that 5023 converted to 12:03:18:720 ....

select cast (39504.5000 as datetime) as abc ,getdate() todaydate

abc todaydate

----------------------- -----------------------

2008-02-28 12:00:00.000 2008-02-27 12:22:14.373

(1 row(s) affected)

where as 5000 is 12:00:00:000 how is that converted

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 13

kurraraghu (2/27/2008)
declare @dt datetime
set @dt = '1900-01-1 00:00:00.000'
select cast(@dt as float)
----------------------
0.105555555555556
(1 row(s) affected)
here how is the time calculate is it seconds of the day or milliseconds smthng like could plz let me knoe i have got the date logic thkz to all u guys ....

No... that's not right...

declare @dt datetime

set @dt = '1900-01-1 00:00:00.000'

select cast(@dt as float)

-----------------------------------------------------

0.0

(1 row(s) affected)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 14

Kurraghu - it's stored in increments of a day. so it's .5023 of a day.

So 1day = 24 hour=24*60 minutes = 24*60*60 secs,

therefore .5023*24*60*60=43398.72 sec = 723 minutes 18.72 secs = 12h3 minutes 18.72 seconds.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?