Swopping positions - 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)

Swopping positions

Author

Topic

doggi3
Yak Posting Veteran

62 Posts

Posted - 2004-09-15 : 22:18:52

I have a column with dates like Jun-24-2004.
However, it turns out to be 24-Jun-2004.
How can I swop back the positions?

UPDATE Table_1
SET [Missing Portion]
WHERE
columnName <> ''

I know I can use Charindex, but I do not know exactly how to use it.
Or is there another way?

Please help... Thx!

- HELP -

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-15 : 23:00:18

you can use convert to change the format of the datetime?
search for datetime formats in BOL... that should get you going...

doggi3
Yak Posting Veteran

62 Posts

Posted - 2004-09-15 : 23:54:54

If I change the column with entry like Jun-24-2004 to datetime format, what will the result turn out?

For e.g. 6-24-2004?

- HELP -

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-09-16 : 00:01:37

The column isn't already datetime format???? You need to ALWAYS store dates as datetime. You can use the following query to find out what format you want to display it in IF you are going to use SQL Server for that. There's also several "functionality features" available by using them.


SET NOCOUNT ON

DECLARE
	@min INT,
	@max INT,
	@date DATETIME

SELECT
	@min = 1,
	@max = 131,
	@date = GETDATE()

SELECT @date

WHILE @min <= @max
BEGIN

	IF @min BETWEEN 15 AND 19 
		OR @min  = 26 
		OR @min BETWEEN 27 AND 99
		OR @min BETWEEN 115 AND 119
		OR @min BETWEEN 122 AND 125
		OR @min BETWEEN 127 AND 129
		BEGIN
			GOTO NEXT_LOOP
		END

	SELECT @min, CONVERT(VARCHAR,@date,@min)

NEXT_LOOP:

SELECT @min = @min + 1
END

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-16 : 00:27:43

IMHO you should try experimenting, i believe experience is the best teacher... what you need is not the exact solution but a nudge towards a solution

you may find some pretty good solutions yourself

you can:
1. convert your string date to desired date format then to string
2. use a combination of substring and charindex

both are documented in BOL

doggi3
Yak Posting Veteran

62 Posts

Posted - 2004-09-16 : 01:15:26

I tried converting it over to datetime format but there are errors. Is SQL capable of converting 'June' over to '06'?

Should I convert 'June' over to '06' before attempting to convert to datetime format?

- HELP -

doggi3
Yak Posting Veteran

62 Posts

Posted - 2004-09-16 : 01:30:39

The datetime format will include the time behind (for e.g. 00.00.00) right?

I only want the date.

It may be simplier if I just swop the positions in the field mentioned in my first post of this thread.

If my date is as follows: 24-Jun-2004

LEFT(DATE_OF_BIRTH, CHARINDEX('-', DATE_OF_BIRTH) - 1) refers to the '24',

SUBSTRING(DATE_OF_BIRTH, CHARINDEX('-', DATE_OF_BIRTH) + 1, CHARINDEX('-', DATE_OF_BIRTH, CHARINDEX('-', DATE_OF_BIRTH) + 1) - CHARINDEX('-', DATE_OF_BIRTH) - 1) refers to 'Jun',

RIGHT(DATE_OF_BIRTH,CHARINDEX('-', REVERSE(DATE_OF_BIRTH)) - 1) refers to '2004'

How do I make use of this information to swop the day and month?

Now, I have dd/month/yyyy and I want the result to be month/dd/yyyy.

- HELP -

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-16 : 01:39:52

lolz..seems to me, you have a string field that accepts whatever date formats

first, try to edit your application that's saving onto this table to use a consistent date format.

then you may want to change the field data type to smalldatetime (only the date) on a new table, then just use dts after you've converted the date problem.

then, can you provide us of all formats you have on the table?
June-24-2004, Jun-24,2004, 24/06/2004... etc

and then we can provide you some options...

doggi3
Yak Posting Veteran

62 Posts

Posted - 2004-09-16 : 01:52:38

Currently the field is nvarchar. When I try to convert it over to smalldatetime, it gives me the below error:
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type smalldatetime.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

Currently, all my dates in the column has only one format:
24-Jun-2004
09-Apr-2004

I will like to change it to this format:
Jun-24-2004
Apr-09-2004

- HELP -

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources