Good day

I have a populated dateTime field. The value stored is conventional Aussie date format:

24/08/2006 10:25:13 PM

My objective was to change the data type to a varchar. The reason for this is to store the update date in the form of a commer delimited string (there is a good reason for this). Is there a way to preserve the date value above while converting the data type to a varchar? When I do this the values change to:

8 August 2006...

Lucky I had a back up to restore the original date formats. To achieve my objective I had to:
>get the unique ID and the date value into temporary session variables (ASP)
>Using EM, change the data type to varchar
>Then loop through my session variables updating the field with the original values

Objective achieved however there must be a quicker way?

Wind is your friend
Matt

__________________
Wind is your friend
Matt

Ya there is a much easier way, if you are using the US format it would be:

convert (varchar (10), [datetimefield], 101) as [field]

For you, you are going to want to use the British/French standard to maintain dd/mm/yyyy:

convert (varchar (10), [datetimefield], 103) as [field]

Just remember that this will only pull your date information, the time information will not be returned from this.

hth

"The one language all programmers understand is profanity."

Thank you for your reply. So no matter what format I am using, there is no way to preserve the my original syntax which of course includes the time?

Wind is your friend
Matt

Hi Matt,

You can also use a messy combination of CONVERT and DatePart to build up a string of all the individual parts:

SELECT Convert(char(4), Year(GetDate())) + '/' + Convert(varchar(2),
Month(GetDate())) + '/' + Convert(varchar(2), Day(GetDate())) + ' '
+ Convert(varchar(2), DatePart(hh, GetDate())) + ':' + Convert
(varchar(2), DatePart(mi, GetDate()))

Run this in the query analyzer and you get something like this:

2006/8/26 9:48

Instead of Year, Month and Day you can also use DatePart if you like....

There might be cleaner solutions, but at least this one gives you full control over the output. If you put this in a user defined function, it's easier and cleaner to reuse.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

You can do this if you want the date and time:

SELECT
    convert (varchar (10), [datetimefield], 103) + ' ' + convert (varchar (10), [datetimefield], 108) as [field]
FROM Table

This will return dd/mm/yyyy hh:mm:ss

hth

"The one language all programmers understand is profanity."

Two very useful replies. Thank you very much, I learnt several things from both responses. I am now thinking I under utalize the capabilities of SQL.

This query :
update testDateConversion set newField=(SELECT convert (varchar (10), updated, 103) + ' ' + convert (varchar (10), updated, 108));

Takes the '20/08/2006 1:29:06 AM' value out of the dateTime field (updated) and inserts it into the new varchar (newField) field nicely however its missing the AM/PM. It inserts '20/08/2006 01:29:06' I am trying to alter it to capture the AM PM should this be possible?

TYIA

Wind is your friend
Matt

You can use CASE and then look at the hour:

SELECT CASE WHEN
DatePart(hh, GetDate()) < 12 THEN ' AM' ELSE ' PM' END

Just as with my previous messy query there may be more efficient ways to do this though...

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

SQL maintains a 24 hour clock, but, I am pretty sure when you write the time out to the page the AM/PM will be appended to the time when its displayed. (In my experience, when binding to a datagrid, this was the behaviour I experienced) If you want the AM/PM appended in the table your inserting to, I think you are going to have to adapt Imar's statement because there is not a specific convert option that will strictly convert your time AND append AM/PM.

There are options that will do this but it applies a format to the entire field, e.g. mon dd yyyy hh:mm:ss:mmm am/pm (convert option 109).

hth.

"The one language all programmers understand is profanity."

Much thanks...

Wind is your friend
Matt