Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 to_date funtion YYYY-DD-MM

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 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.

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.
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-01-30 : 12:48:57
Nobody uses YYYY-DD-MM.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-30 : 15:10:52
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

11752 Posts

Posted - 2008-01-30 : 15:11:14
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 - 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
Go to Top of Page

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.
Go to Top of Page

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)
Go to Top of Page

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]
Go to Top of Page

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?
Go to Top of Page

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.
Go to Top of Page

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?

Go to Top of Page

vibhav
Starting Member

28 Posts

Posted - 2008-01-30 : 18:07:10
datetime
Go to Top of Page

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.
Go to Top of Page

vibhav
Starting Member

28 Posts

Posted - 2008-01-31 : 11:05:13
Now it makes sense.

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

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.
Go to Top of Page

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!

Go to Top of Page

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 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 - 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 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 - 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.


Go to Top of Page

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-05 : 11:49:38
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
    Next Page

- Advertisement -