Date format problem - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Date format problem

Author

Topic

Joozh
Posting Yak Master

145 Posts

Posted - 2005-03-10 : 04:09:32

I am using a view which brings purchase related data from my OLTP.

I am using the same view for the source of my production system (which has a mm/dd/yy format) and for my test machine (which has a dd-Mmm-yy format).

By formats I mean theformatsdefinedvia theRegional settings.

The problem is that on the test machine, the dates are giving problems and a date such as today's (i.e. 10, March, 2005) is coming in as 03-October, 2005) - notice that this is due to the data format difference i.e. 03/10/05 versus 10-Mar-05 (or 10/03/05).

Any ideas how I can use the same view but for my test machine I do some sort of conversion to correctly change the format.

Many TIA and I hope my post is clear.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-10 : 05:50:44

change all dates to iso standard of yyyymmdd. that way you wont have a problem.

Go with the flow & have fun! Else fight the flow

Joozh
Posting Yak Master

145 Posts

Posted - 2005-03-10 : 05:56:52

Thanks. Will give it a try and post back.

Thanks & Regards.

-J

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-03-10 : 06:18:30

Otherwise use Convert Function

Madhivanan

Failing to plan is Planning to fail

Joozh
Posting Yak Master

145 Posts

Posted - 2005-03-10 : 06:32:02

Thanks for the replies.

Tried using theISO format in CONVERT function but that totally messes up the data for the production system as well as the test machine.

Tried using 101 and 103 but both work for one machine but not the other :(

Maybe I should use identical copies of the same view but one using 101 in the CONVERT function (for the Productionsystem) and the other view using ??? in the CONVERT function for the test machine. Any clues what I should use in the CONVERT function for the test machine.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-10 : 06:37:35

select convert(varchar(10), yourDateColumn, 112), convert(datetime, convert(varchar(10), yourDateColumn, 112))

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-03-10 : 06:40:47

Use Convert Function with 113

Madhivanan

Failing to plan is Planning to fail

Joozh
Posting Yak Master

145 Posts

Posted - 2005-03-10 : 06:54:49

112 or 113 ???? :)

I guess the only choice is to try both.

Thanks.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-10 : 06:56:21

112 is iso format. look at Convert in BOL.

Go with the flow & have fun! Else fight the flow