Date fields swaping - 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

SQL Server Development (2000)

Date fields swaping

Author

Topic

shashiondwh
Starting Member

4 Posts

Posted - 2010-04-06 : 09:12:43

Hi All ,

Please help me here .

I have a requirement where I need to swap the date ,month values of the date is the datatype where the date value is less than 13 .For example , if the value is '2000-12-10(yyyy-mm-dd) , then it should be changed to '2000-0-12 .
I am using SQL server 2000 .

We can use either a query procedure .

Could you please let me know the query to do this task .

Thank you very much in Advance .

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-06 : 09:21:45

Why do you want to do this?

Always send the date in YYYYMMDD format. You wont have any more problems

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-06 : 09:22:32

what is the data type of the column ?

quote:
if the value is '2000-12-10(yyyy-mm-dd) , then it should be changed to '2000-10-12 .

swap the month and the day ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-06 : 11:52:07

quote:
Originally posted by shashiondwh

Hi All ,

Please help me here .

I have a requirement where I need to swap the date ,month values of the date is the datatype where the date value is less than 13 .For example , if the value is '2000-12-10(yyyy-mm-dd) , then it should be changed to '2000-0-12 .
I am using SQL server 2000 .

We can use either a query procedure .

Could you please let me know the query to do this task .

Thank you very much in Advance .

i think this has do with interpretation of date values being passed onto the system. Can i ask whats the language setting for your server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shashiondwh
Starting Member

4 Posts

Posted - 2010-04-07 : 06:07:41

I have to do because, when data is loading using the DTS pacakge, the date field values are loading in different format . The requirement is have data in yyyy-mm-dd format but it is loading as yyyy-dd-mm for only few values for which the date value is lesser than 13 . Column datatype is smalldatetime .

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-07 : 07:19:14

What does the following code return when you run it in QA?

PRINT MONTH(CAST('2010-01-02' AS SMALLDATETIME))

As far as I know, this should return 1 regardless of your regional settings. If it does, then it suggests the problem is the format of the source data

Can you give us more specific details on what the DTS package is actually doing? If it's loading data for an external source, then what format is the source date data in? I'm thinking it's in 'dd-mm-yyyy' format. If so, then you can format it in 2 ways to solve the problem. 'yyyymmdd' (or 'yyyy-mm-dd') OR 'dd mmm yyyy'. Either way will force SQL Server to interpret the date correctly.

You should also check your regional setting to make sure they're correct. Specifically the short date settings. However, I do have a vague memory of dates being incorrectly interpreted regardless of regional settings, but I can't remember if it was SQL 2000 or Access 2000. I may have been both. In any case, the work around was to use one of the above 2 suggested date formats for the source data.

There are 10 types of people in the world, those that understand binary, and those that don't.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-07 : 09:13:31

<<
'yyyymmdd' (or 'yyyy-mm-dd') OR 'dd mmm yyyy'. Either way will force SQL Server to interpret the date correctly.
>>

Note that there are two unambigious formats

1 YYYYMMDD (with or without Time)
2 YYYY-MM-DDTHH:MM:SS (Note the time seperator T)

Other than these all formats are dependent on server's date format or language

dd mmm yyyy format wont wotk in non-English server

Madhivanan

Failing to plan is Planning to fail

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-07 : 09:28:35

quote:
Originally posted by madhivanan
Note that there are two unambigious formats

1 YYYYMMDD (with or without Time)
2 YYYY-MM-DDTHH:MM:SS (Note the time seperator T)

The YYYY-MM-DD format will still work without a time component.

quote:
dd mmm yyyy format wont wotk in non-English server

I wasn't aware of that. Thanx.

There are 10 types of people in the world, those that understand binary, and those that don't.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-07 : 09:40:23

<<
The YYYY-MM-DD format will still work without a time component.
>>

Can you run this?

set dateformat ydm
select cast('2009-12-19' as datetime)

Madhivanan

Failing to plan is Planning to fail

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-07 : 09:59:20

quote:
Originally posted by madhivanan
Can you run this?

set dateformat ydm
select cast('2009-12-19' as datetime)

No, but I can run this:

set dateformat ymd
select cast('2009-12-19' as datetime)

There are 10 types of people in the world, those that understand binary, and those that don't.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-07 : 10:02:15

quote:
Originally posted by DBA in the making

quote:
Originally posted by madhivanan
Can you run this?

set dateformat ydm
select cast('2009-12-19' as datetime)

No, but I can run this:

set dateformat ymd
select cast('2009-12-19' as datetime)

There are 10 types of people in the world, those that understand binary, and those that don't.

So as my points. YYYY-MM-DD without time is dependent on date settings

Madhivanan

Failing to plan is Planning to fail

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-07 : 10:20:47

Sorry, from you post above, it appears you were saying the time component was mandatory.

There are 10 types of people in the world, those that understand binary, and those that don't.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-07 : 10:25:58

quote:
Originally posted by DBA in the making

Sorry, from you post above, it appears you were saying the time component was mandatory.

There are 10 types of people in the world, those that understand binary, and those that don't.

The time component is mandatory if you use YYYY-MM-DD format
For YYYYMMDD it is optional

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-07 : 10:29:07

To be more clear, run these

set dateformat ydm
select cast('2009-12-19' as datetime)

set dateformat ydm
select cast('2009-12-19T00:00:00' as datetime)

set dateformat ydm
select cast('20091219' as datetime)

set dateformat ydm
select cast('20091219 00:00:00' as datetime)

You get error for first one and rest all will work fine

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 13:51:01

quote:
Originally posted by shashiondwh

I have to do because, when data is loading using the DTS pacakge, the date field values are loading in different format . The requirement is have data in yyyy-mm-dd format but it is loading as yyyy-dd-mm for only few values for which the date value is lesser than 13 . Column datatype is smalldatetime .

are you loading date values directly from file or excel?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shashiondwh
Starting Member

4 Posts

Posted - 2010-04-08 : 03:02:08

Thanks for all of you . There was a problem with SQL server locale. It was pointing US and now changed to UK locale . Its giving correct date format for all values .
My sincere apologies for taken your valuable time .

Once gain Huge thanks to you all .

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-08 : 05:07:27

quote:
Originally posted by shashiondwh

Thanks for all of you . There was a problem with SQL server locale. It was pointing US and now changed to UK locale . Its giving correct date format for all values .
My sincere apologies for taken your valuable time .

Once gain Huge thanks to you all .

Have you seen my first reply?
You dont need to change anything if you follow it

Madhivanan

Failing to plan is Planning to fail

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-08 : 05:22:21

quote:
Originally posted by madhivanan
Have you seen my first reply?
You dont need to change anything if you follow it

While I agree that for a ground up solution, a correctly formatted date will save a lot of headaches, I would also agree that to solve a simple problem in an existing system, this isn't necessarily the case. If the entire system has been tested and found to operate correctly, but begins to have a problem out of the blue such as this one, simply because someone changed a single setting, then the best solution would be to change the setting back. Why? Because a solution that involves re-coding the entire system also involves re-testing the entire system. A system that involves re-configuring the data also involves testing the system will still operate correctly with that data format. But a solution that involves reversing a setting change involves minimal testing at best. It's often the simplest solution with the least chance of things going pear shaped.

There are 10 types of people in the world, those that understand binary, and those that don't.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-08 : 06:05:36

quote:
Originally posted by DBA in the making

quote:
Originally posted by madhivanan
Have you seen my first reply?
You dont need to change anything if you follow it

While I agree that for a ground up solution, a correctly formatted date will save a lot of headaches, I would also agree that to solve a simple problem in an existing system, this isn't necessarily the case. If the entire system has been tested and found to operate correctly, but begins to have a problem out of the blue such as this one, simply because someone changed a single setting, then the best solution would be to change the setting back. Why? Because a solution that involves re-coding the entire system also involves re-testing the entire system. A system that involves re-configuring the data also involves testing the system will still operate correctly with that data format. But a solution that involves reversing a setting change involves minimal testing at best. It's often the simplest solution with the least chance of things going pear shaped.

There are 10 types of people in the world, those that understand binary, and those that don't.

Most of the time, sql developers dont seem to understand how datetime works in SQL Server. So I am suggesting to follow my advice hereafter.
I understand that this may cuase confusion to already developed projects. However, problem may araise if anyone changes the date settings.

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 07:52:31

I also think its much better to follow a standard format to pass date values as Madhi suggested rather than tweaking the date settings of server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-08 : 08:17:39

quote:
Originally posted by visakh16

I also think its much better to follow a standard format to pass date values as Madhi suggested rather than tweaking the date settings of server

Like I said, it depends on the situation. If it's a new system being developed, then doing it the right way from the start is obviously better. But if it's an existing system that's been broken because someone changed a setting, then changing the setting back is sometimes a more logical solution than redeveloping the system to be independent of the setting change. Especially in situation where the system is highly complex, or was developed by a 3rd party, or both.

There are 10 types of people in the world, those that understand binary, and those that don't.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources