SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 to_date funtion YYYY-DD-MM
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

vibhav
Starting Member

28 Posts

Posted - 01/30/2008 :  12:38:19  Show Profile  Reply with Quote
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 or
2. To have an appropriate conversion in my code.( I am not feeling good about the suffix AM/PM in the date column!)

Thanks.

Edited by - spirit1 on 01/30/2008 15:11:04

jdaman
Constraint Violating Yak Guru

USA
354 Posts

Posted - 01/30/2008 :  12:44:44  Show Profile  Reply with Quote
Store the date as datetime in your database and have your front end handle the display formating.
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 01/30/2008 :  12:48:57  Show Profile  Reply with Quote
Nobody uses YYYY-DD-MM.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 01/30/2008 :  15:10:52  Show Profile  Visit spirit1's Homepage  Reply with Quote
except him

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 01/30/2008 :  15:11:14  Show Profile  Visit spirit1's Homepage  Reply with Quote
moved from script library

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out
Go to Top of Page

vibhav
Starting Member

28 Posts

Posted - 01/30/2008 :  15:50:36  Show Profile  Reply with Quote
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
Go to Top of Page

vibhav
Starting Member

28 Posts

Posted - 01/30/2008 :  15:54:56  Show Profile  Reply with Quote
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.
Go to Top of Page

jdaman
Constraint Violating Yak Guru

USA
354 Posts

Posted - 01/30/2008 :  16:13:55  Show Profile  Reply with Quote
Do you just want it stored as a date?
CAST('1/28/2008 1:00:00 PM' AS DATETIME)
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 01/30/2008 :  16:27:04  Show Profile  Reply with Quote
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:
http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx
Go to Top of Page

vibhav
Starting Member

28 Posts

Posted - 01/30/2008 :  17:39:09  Show Profile  Reply with Quote
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?
Go to Top of Page

vibhav
Starting Member

28 Posts

Posted - 01/30/2008 :  17:40:23  Show Profile  Reply with Quote
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:
http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx



Thank You. Let me look into this.
Go to Top of Page

jdaman
Constraint Violating Yak Guru

USA
354 Posts

Posted - 01/30/2008 :  17:55:52  Show Profile  Reply with Quote
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?

Go to Top of Page

vibhav
Starting Member

28 Posts

Posted - 01/30/2008 :  18:07:10  Show Profile  Reply with Quote
datetime
Go to Top of Page

jdaman
Constraint Violating Yak Guru

USA
354 Posts

Posted - 01/30/2008 :  18:35:10  Show Profile  Reply with Quote
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.
Go to Top of Page

vibhav
Starting Member

28 Posts

Posted - 01/31/2008 :  11:05:13  Show Profile  Reply with Quote
Now it makes sense.

Thanks all of you for your help.
Go to Top of Page

vibhav
Starting Member

28 Posts

Posted - 01/31/2008 :  14:55:41  Show Profile  Reply with Quote
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.
Go to Top of Page

vibhav
Starting Member

28 Posts

Posted - 01/31/2008 :  15:05:46  Show Profile  Reply with Quote
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!

Go to Top of Page

jdaman
Constraint Violating Yak Guru

USA
354 Posts

Posted - 01/31/2008 :  15:18:47  Show Profile  Reply with Quote
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 DATETIME
SET @date = '2008-01-31 09:00:00.0'
SELECT CONVERT( VARCHAR, @date, 9 )
Go to Top of Page

vibhav
Starting Member

28 Posts

Posted - 02/01/2008 :  11:11:45  Show Profile  Reply with Quote
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 DATETIME
SET @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.
Go to Top of Page

vibhav
Starting Member

28 Posts

Posted - 02/05/2008 :  11:41:25  Show Profile  Reply with Quote
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.


Go to Top of Page

jdaman
Constraint Violating Yak Guru

USA
354 Posts

Posted - 02/05/2008 :  11:49:38  Show Profile  Reply with Quote
quote:
Originally posted by vibhav
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.



Im not quite sure what you are asking here.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000