Formatting datetime when including in a PRINT command etc...

I'm trying to format a print string so that it reports progress whilst
processing a looping structure with a date time stamp appended to the
end of the string. This started out life as a simple need to create a
display that show progress to users when updating large tables in a
data warehouse in real time.
>
I have subsequently address that need through a different method,
however I am challenged by the seeming impossibility to do this in
SQLServer, I'm sure it's my lack of familiarity with the product so am
asking for guidance of the more wise among the community.

As getdate() returns a datetime I am forced to cast or convert to make
the result compatible with the rest of the string.
>
select getdate()
Returns: 2008-07-14 11:06:05.460, (exactly what I want but
cannot get to print)

Folks,
>
I'm trying to format a print string so that it reports progress whilst
processing a looping structure with a date time stamp appended to the
end of the string. This started out life as a simple need to create a
display that show progress to users when updating large tables in a
data warehouse in real time.
>
I have subsequently address that need through a different method,
however I am challenged by the seeming impossibility to do this in
SQLServer, I'm sure it's my lack of familiarity with the product so am
asking for guidance of the more wise among the community.
>
>
print 'Loop :' + cast (@loop as char(5)) +
': Range :' + cast(@start_range as char(14)) + ': To :' +
cast(@end_range as char(14)) +
': Value :' + rtrim(cast(@upd_value as char(14))) + ': ' +
cast(getdate() as char(25))
>
>
I want the datetime to print out down to the fraction of a second.
As getdate() returns a datetime I am forced to cast or convert to make
the result compatible with the rest of the string.
>
select getdate()
Returns: 2008-07-14 11:06:05.460, (exactly what I want but
cannot get to print)
>
print 'Date & Time :' + getdate() + ':'
Returns: Conversion failed when converting datetime from character
string.
>
print 'Value :' + cast(getdate() as char(25))+ ':'
Returns: Value :Jul 14 2008 11:10AM :
>
print Value :' + convert(char, getdate())+ ':'
Returns: Value :Jul 14 2008 11:14AM :
>
print getdate()
Returns: Jul 14 2008 11:08AM
>
This last one suggests to me that the formatting is being controlled
at a higher level which I don't know how to override. I've seen the
'format' command that can be appled to the data section but it does
not appear to apply to the time section, or am I wrong?
>
I wondered if this could be tied in with 'collations'?
>
help, guidance, ideas please,
>
TIA, Tim
>
>
>
>
>
>
>
>

>>
One method is to build the entire message in a string:
>
DECLARE @Message varchar(250)
SET @Message = 'Loop :' + cast (@loop as char(5)) +
* * * *': *Range :' + cast(@start_range as char(14)) + ': *To:' +
*cast(@end_range as char(14)) +
* * * *': *Value :' + rtrim(cast(@upd_value as char(14))) + ': *' +
*convert(char(23), getdate(), 121)
PRINT @Message
>
Another approach is to store the formatted datetitme in a variable and use
RAISERROR like the example below. *Note the WITH NOWAIT option will prevent
buffering so the message can be displayed immediately. *See the Books Online
for details.
>
DECLARE @formatted_datetime char(23)
SET @formatted_date = CONVERT(char(23), GETDATE(), 121)
RAISERROR('Loop :%d : Range :%d : To :%d : Value :%d: *%s', 0, 1,
* * @loop, @start_range, @end_range, @upd_value, @formatted_datetime)
* * WITH NOWAIT
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVPhttp://weblogs.sqlteam.com/dang/
>
"Tim" <tim_roger...@hotmail.comwrote in message
>
news:ac4cdb48-e5b1-4add-bbe2-c9f6eea64793@y21g2000hsf.googlegroups.com...
>
>
>>>>>>>>>>>>>>>
- Show quoted text -