String manipulation

[opo]

Is there a sql command that will insert a character into an existing field such as 123001 to 12/30/01?

 

[tlbroadbent]

Use the STUFF function.

Select STUFF(STUFF(field,5,0,'/'),3,0,'/') As NewField
From Table Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[opo]

Thanks.
Is there a way to convert a field with a date and a field with a time to a timestamp?

 

[tlbroadbent]

If you mean to a datetime field, the answer is yes. Timestamp data type has special meaning in SQL Server and it doesn't really have anything to do with datetime.

If the date and time fields are in formats (or styles) recognized by SQL Server, you can simply concantenate them and then store the result in a datetime field.

Example 1:

Declare @dt datetime

Set @dt='08/17/01' + ' ' + '06:15:30'
Select @dt
---------
Example 2:

Update table Set datetimecol = datecol + ' ' + timecol

---------
Example 3: If you need to insert the date and time delimiters.

Update table
Set datetimecol =
STUFF(STUFF(datecol,5,0,'/'),3,0,'/') + ' ' +
STUFF(STUFF(timecol,5,0,':'),3,0,':') Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[opo]

Thanks again.
Last question
Is there a function to evaluate just the date part of a datetime field?

 

[tlbroadbent]

You can use the convert function.

Today, Select convert(char(10), getdate(), 103) returns 17/08/2001. 103 is the style code for British/French dates. 101 returns USA style of mm/dd/yyyy. Styles 1-8, 10-12 and 14 return the year only (yy) rather than the century (yyyy).

Additional style codes are listed under CAST and CONVERT in SQL BOL and can be found at this URL.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_2f3o.asp

Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.