Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

convert date object into integer Expand / Collapse
Author
Message
Posted Saturday, September 27, 2008 4:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 9, 2008 4:07 AM
Points: 17, Visits: 60
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
Post #577286
Posted Saturday, September 27, 2008 5:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 6:56 AM
Points: 1,330, Visits: 1,434
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
Post #577298
Posted Saturday, September 27, 2008 6:16 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
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
*/



.
Post #577301
Posted Sunday, September 28, 2008 9:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 9, 2008 4:07 AM
Points: 17, Visits: 60
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.
Post #577511
Posted Sunday, September 28, 2008 9:43 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
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?




.
Post #577513
Posted Sunday, September 28, 2008 9:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 9, 2008 4:07 AM
Points: 17, Visits: 60
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 ?
Post #577514
Posted Sunday, September 28, 2008 10:12 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
So what is the exact output you are expecting from this date value?

.
Post #577516
Posted Sunday, September 28, 2008 11:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 9, 2008 4:07 AM
Points: 17, Visits: 60
I need the output as 1215714600 which is an int value of the date converted into milliseconds
Post #577523
Posted Monday, September 29, 2008 12:02 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
What is the formula you used to get this number?



.
Post #577531
Posted Monday, September 29, 2008 12:15 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:02 AM
Points: 3,241, Visits: 4,996
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


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

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #577538
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse