473,386 Members | 1,766 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,386 software developers and data experts.

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


Jul 14 '08 #1
3 30750
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: James | last post by:
Hello, The PRINT command works fine on Query Analyzer. However, when I used it with other Database Client, eg: Aqua Data Studio, nothing got printed out. Is there a way to make it work? ...
1
by: sunil | last post by:
Hi, I am trying to print a variable port using print command , the port variable is coming as function arguement.See the snippet below. 680 printf("The port number is %d\n",port);...
1
by: mmatchyn | last post by:
Is there a way to pass an object when including a class? For example... namespace testclass { public System.Windows.Forms.TextBox textbox; public class Write { public Write(string msg)
1
by: esateesh | last post by:
how to make a print button invisible in the printed page when a print is given
0
by: esateesh | last post by:
HI i have a .aspx page with a print button and am calling java script function i.e onclick="window.print()" when a print is given am gettng print button in the pirnted page, but it should not be...
0
by: neeraj | last post by:
Hi all I have developed on desktop application in Visual Basic for Hotel Management and my client requires multiple printing options here I have multiple printers first default printer which...
3
Mohan Krishna
by: Mohan Krishna | last post by:
Hi EveryOne! Please any one help in the following Q's. I am using to print a restaurant bill using PRINT command in the code. 1. How can I change the FONT of the text to be printed? 2. How can I...
3
by: zayyaz | last post by:
I am using an excel sheet to keep track of the finances of my afterschool program. Each week I am creating an invoice for the families that have a different balance, outputing from the excel file to...
9
by: Dave-o | last post by:
New learner. For the print command and concatenation, the period is difficult for my afflicted eyes to see, but the "+" seems to work. Is this an accepted PHP convention? Thank you, David
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.