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

[Tim]

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

[Erland Sommarskog]

Tim (ti**********@hotmail.com) writes:

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.

Do it this way:

SELECT @str = convert(varchar(19), getdate(), 121)
RAISERROR('The time is now %s', 0, 1, @str) WITH NOWAIT

121 is a format code, there are more of them in Books Online.

You should probably RAISERROR WITH NOWAIT, rather than PRINT, as
PRINT messages are buffered. Don't worry about the error part,
the 0 is the severity level, which means "informational".

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Dan Guzman]

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)

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 MVP
http://weblogs.sqlteam.com/dang/

"Tim" <ti**********@hotmail.comwrote in message
news:ac**********************************@y21g2000 hsf.googlegroups.com...

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

[Tim]

On 14 Jul, 12:59, "Dan Guzman" <[e-mail]>
wrote:

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)

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" <[e-mail] in message

news:ac**********************************@y21g2000 hsf.googlegroups.com...

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- Hide quoted text -

- Show quoted text -

Conundrum solved, my thanks to both of you. Tim