September 27, 2008 at 4:03 am
Hi,
I need to convert a date object into its integer representation in SQL server 2005.
Can anybody throw some light on this ASAP ?
Thanks
Vijoy
September 27, 2008 at 5:58 am
Hello,
Try: Convert(Char(8), GetDate(), 112)
Is that is the output that you need?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
September 27, 2008 at 6:16 am
or is it what you are looking for?
select cast(getdate() as int)
/*
39717
*/
select cast(39717 as datetime)
/*
2008-09-28 00:00:00.000
*/
.
September 28, 2008 at 9:33 pm
Guys,Thanks for the replies.
What i need is an integer representation of a date say 1/9/2008 converted into seconds to be inserted into the database.
The output of a date value (1/9/2008) being inserted into the DB should be somewhat like 1220......
Awaiting your reply.
September 28, 2008 at 9:43 pm
Could you explain what exactly you are trying to achieve? Probably you may not need to do what you are trying to do now.
SQL Server stores a DATETIME value internally as two 4 byte integers. The first integer stores the number of days since 1900-01-01 and the second integer stores the number of milliseconds since midnight.
So, you may not need to do an additional conversion by yourself.What are you trying to achieve?
.
September 28, 2008 at 9:55 pm
Sorry for the confusion.
I need to compare a date value that is stored in the DB via Java like this
--> "" + (cal.getTime().getTime()/1000)
So for comparing at run time i need to convert my date object (1/9/2008) as a millisecond value so that i can compare it with the DB value.
Are you able to understand my problem now ?
September 28, 2008 at 10:12 pm
So what is the exact output you are expecting from this date value?
.
September 28, 2008 at 11:05 pm
I need the output as 1215714600 which is an int value of the date converted into milliseconds
September 29, 2008 at 12:02 am
What is the formula you used to get this number?
.
September 29, 2008 at 12:15 am
For such value, u need a Cut OFF date, from onwards, you can calculate the miiseconds till the required date, for example,
FirstDate = getdate() - 1
Seconddate = getdate()
Now, you can calculate teh milliseconds based on the difference of two dates as,
lets say, if the difference is 1, then 24 hours = 24*60 Minutes = 24*60*60 seconds = 24*60*60*1000 milliseconds
Atif Sheikh
September 29, 2008 at 12:33 am
I continue without understand what you need exactly.
Anyway, to obtain the number of miliseconds from a datetime value, you can use something like this:
declare @Date DATETIME
SET @Date = convert(datetime, '2009-09-01 09:30:15.927')
SELECT convert(BIGINT, @Date)* 86400000
SELECT DATEPART(hh, @Date) * 3600000
SELECT DATEPART(mi, @Date) * 60000
SELECT DATEPART(ss, @Date) * 1000
SELECT DATEPART(ms, @Date)
SELECT (convert(BIGINT, @Date)* 86400000 ) + (DATEPART(hh, @Date) * 3600000) + (DATEPART(mi, @Date) * 60000) + (DATEPART(ss, @Date) * 1000) + DATEPART(ms, @Date)
And, the final result is:
Datetime: 2009-09-01 09:30:15.925
Number of milisenconds: 3460786215927
I hope this can help you.
September 29, 2008 at 12:46 am
If it is a matter of finding the difference between two dates (in milliseconds) you can even run the following query.
DECLARE @d1 DATETIME, @d2 DATETIME
SELECT @d1 = '1/9/2008 11:30:24.123', @d2 = '1/10/2008'
SELECT DATEDIFF(millisecond, @d1, @d2) AS Diff
/*
Diff
-----------
44975877
*/
Or you can extract specific information from the date value using the following query and apply your required calculations.
DECLARE @d1 DATETIME, @d2 DATETIME
SELECT @d1 = '1/9/2008 11:30:24.123'
SELECT
DATEPART(year, @d1) AS Years,
DATEPART(month, @d1) AS Months,
DATEPART(day, @d1) AS Days,
DATEPART(hour, @d1) AS Hours,
DATEPART(minute, @d1) AS Minutes,
DATEPART(second, @d1) AS Seconds,
DATEPART(Millisecond, @d1) AS Milliseconds
/*
Years Months Days Hours Minutes Seconds Milliseconds
----------- ----------- ----------- ----------- ----------- ----------- ------------
2008 1 9 11 30 24 123
*/
.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply