By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 1,012 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

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

P: n/a
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


Jul 14 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Jul 14 '08 #2

P: n/a
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



Jul 14 '08 #3

P: n/a
Tim
On 14 Jul, 12:59, "Dan Guzman" <guzma...@nospam-online.sbcglobal.net>
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" <tim_roger...@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- Hide quoted text -

- Show quoted text -

Conundrum solved, my thanks to both of you. Tim
Jul 15 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.