Author |
Topic |
vibhav
Starting Member
28 Posts |
Posted - 2008-01-30 : 12:38:19
|
Hi All:We have a database date column in this format: 1/27/2008 1:20:00 AM and need a date or time function to cast local JVM time into the above format.Suggest me the best options.1. To go for a more comfortable date format in the database itself or2. To have an appropriate conversion in my code.( I am not feeling good about the suffix AM/PM in the date column!)Thanks. |
|
jdaman
Constraint Violating Yak Guru
354 Posts |
Posted - 2008-01-30 : 12:44:44
|
Store the date as datetime in your database and have your front end handle the display formating. |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2008-01-30 : 12:48:57
|
Nobody uses YYYY-DD-MM. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-01-30 : 15:10:52
|
except him _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-01-30 : 15:11:14
|
moved from script library_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
|
|
vibhav
Starting Member
28 Posts |
Posted - 2008-01-30 : 15:50:36
|
Thanks for your replies.Now can anybody help me in casting it using appropriate to_date function.I am not sure whether to use this function or to_timestamp function.is this ok?:TO_DATE('$(lastmod)','MM/DD/YYYY HH.MI.SS AM/PM')How do SQl Server 2005 handle this suffix AM/PM?Thanks |
|
|
vibhav
Starting Member
28 Posts |
Posted - 2008-01-30 : 15:54:56
|
In our SQL 2005 db the date column formatted as 1/28/2008 1:00:00 PM.Any insert as '2008-01-28 13:00' is converted to the above format.Any insert as '2008-01-28 10:00' is converted as 1/28/2008 10.00.00 AM.I am not sure how to handle this AM/PM. |
|
|
jdaman
Constraint Violating Yak Guru
354 Posts |
Posted - 2008-01-30 : 16:13:55
|
Do you just want it stored as a date?CAST('1/28/2008 1:00:00 PM' AS DATETIME) |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-01-30 : 16:27:04
|
quote: Originally posted by vibhav In our SQL 2005 db the date column formatted as 1/28/2008 1:00:00 PM.Any insert as '2008-01-28 13:00' is converted to the above format.Any insert as '2008-01-28 10:00' is converted as 1/28/2008 10.00.00 AM.I am not sure how to handle this AM/PM.
You might want to take a look at this article:[url]http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx[/url] |
|
|
vibhav
Starting Member
28 Posts |
Posted - 2008-01-30 : 17:39:09
|
quote: Originally posted by jdaman Do you just want it stored as a date?CAST('1/28/2008 1:00:00 PM' AS DATETIME)
This statment should be executed only once. So what about AM? |
|
|
vibhav
Starting Member
28 Posts |
Posted - 2008-01-30 : 17:40:23
|
quote: Originally posted by Lamprey
quote: Originally posted by vibhav In our SQL 2005 db the date column formatted as 1/28/2008 1:00:00 PM.Any insert as '2008-01-28 13:00' is converted to the above format.Any insert as '2008-01-28 10:00' is converted as 1/28/2008 10.00.00 AM.I am not sure how to handle this AM/PM.
You might want to take a look at this article:[url]http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx[/url]
Thank You. Let me look into this. |
|
|
jdaman
Constraint Violating Yak Guru
354 Posts |
Posted - 2008-01-30 : 17:55:52
|
quote: Originally posted by vibhav Hi All:We have a database date column in this format: 1/27/2008 1:20:00 AM and need a date or time function to cast local JVM time into the above format.
What is the datatype for your date column? |
|
|
vibhav
Starting Member
28 Posts |
Posted - 2008-01-30 : 18:07:10
|
datetime |
|
|
jdaman
Constraint Violating Yak Guru
354 Posts |
Posted - 2008-01-30 : 18:35:10
|
Then it is not storing it as '1/28/2008 10.00.00 AM', it is only displaying it in that way. You will see that in the article Lamprey gave you. |
|
|
vibhav
Starting Member
28 Posts |
Posted - 2008-01-31 : 11:05:13
|
Now it makes sense.Thanks all of you for your help. |
|
|
vibhav
Starting Member
28 Posts |
Posted - 2008-01-31 : 14:55:41
|
I came back to the same function again.Can anybody tell me what function should I use instead of to_date and to_timestamp function to convert 2008-01-31 09:00:00.0 to MM/DD/YYYY HH:MI:SS AM/PM ?Can anybody help me in casting this using functions. |
|
|
vibhav
Starting Member
28 Posts |
Posted - 2008-01-31 : 15:05:46
|
quote: Originally posted by vibhav I came back to the same function again.Can anybody tell me what function should I use instead of to_date and to_timestamp function to convert 2008-01-31 09:00:00.0 to MM/DD/YYYY HH:MI:SS AM/PM ?Can anybody help me in casting this using functions.
The last sentence of this(http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx) article says:hat's it. I hope, you've seen that the DATETIME datatype is far from being mysterious. SQL Server provides a rich arsenal of build-in functions to deal efficiently with DATETIMEs. Use it! |
|
|
jdaman
Constraint Violating Yak Guru
354 Posts |
Posted - 2008-01-31 : 15:18:47
|
quote: Originally posted by vibhav I came back to the same function again.Can anybody tell me what function should I use instead of to_date and to_timestamp function to convert 2008-01-31 09:00:00.0 to MM/DD/YYYY HH:MI:SS AM/PM ?Can anybody help me in casting this using functions.
This is as close as youre going to get in t-sql:DECLARE @date DATETIMESET @date = '2008-01-31 09:00:00.0'SELECT CONVERT( VARCHAR, @date, 9 ) |
|
|
vibhav
Starting Member
28 Posts |
Posted - 2008-02-01 : 11:11:45
|
quote: Originally posted by jdaman
quote: Originally posted by vibhav I came back to the same function again.Can anybody tell me what function should I use instead of to_date and to_timestamp function to convert 2008-01-31 09:00:00.0 to MM/DD/YYYY HH:MI:SS AM/PM ?Can anybody help me in casting this using functions.
This is as close as youre going to get in t-sql:DECLARE @date DATETIMESET @date = '2008-01-31 09:00:00.0'SELECT CONVERT( VARCHAR, @date, 9 )
This was really awesome.Worked like a charm.Thank you jdaman for the statement.I thank others too for their great inputs. |
|
|
vibhav
Starting Member
28 Posts |
Posted - 2008-02-05 : 11:41:25
|
SELECT CONVERT( VARCHAR, @date, 9 )I have come back again with one more query.The datetime column contains effective date of tomorrow and will be entered today.Can I use any other clause to this t-sql statement to force to use only the effective date of the row, or does it taken my the statement itself.Will ORDER BY clause fulfill my requirement?Thanks. |
|
|
jdaman
Constraint Violating Yak Guru
354 Posts |
Posted - 2008-02-05 : 11:49:38
|
quote: Originally posted by vibhavCan I use any other clause to this t-sql statement to force to use only the effective date of the row, or does it taken my the statement itself.Will ORDER BY clause fulfill my requirement?Thanks.
Im not quite sure what you are asking here. |
|
|
Next Page
|