Formatting syntax

Hi,

I am trying to convert an MS Access query to SQL script.The query builds a 16 digit field by combining 3 different columns.

access query

Select

"SP"& Format([CusNum],"000")& " "& Format$([InVoiceNum],"00000000") & "-" & Format$(,"00") AS RefNo,

From PurchaseOrder

RefNo

SP053 00000001-00

SP053 00000010-00

SP05314556895-00

SQL query to replace access query

Select

'SP'+Isnull(Cast(CusNumasvarchar(3)),'000')+''+Isnull(Cast(InVoiceNumasvarchar(8)),'00000000')+'-'+Isnull(Cast(PIasvarchar(2)),'00')

From dbo.PurchaseOrder

SP0531-0

SP05310-0

SP05314556895-00

I need some help in putting together a query that will give me the same results as produced by the access query.If InvoiceNum is less than 8 digits long I don’t know how to pad the field with 0000 to get it to be desired length.

Thanks for your help.

Nats

Nats:

Maybe something like this:

declare @.aNumber integer set @.aNumber = 715

select @.aNumber as [Input Number],
right ('0000000' + convert(varchar(8), @.aNumber), 8) as [formatted Number]

Input Number formatted Number
-
715 00000715

( Bemoaning my rustiness at Access; I wrote part of Access 97 Unleashed 2nd Ed, but I haven't used Access really since the previous millenium -- SHEESH! )

|||

Try the example below.

Chris

SELECT 'SP'

+ RIGHT('000' + ISNULL(CAST([CusNum] AS VARCHAR(3)), ''), 3)

+ ' '

+ RIGHT('00000000' + ISNULL(CAST([InVoiceNum] AS VARCHAR(8)), ''), 8)

+ '-'

+ RIGHT('00' + ISNULL(CAST(PI AS VARCHAR(2)), ''), 2)

FROM dbo.PurchaseOrder

--Note that PI should be in square brackets, however for some reason when I do this in the forum I end up with a piece of pizza, like so:

|||

Awesome works great...

Thanks a bunch!

Formatting Question

I have an old DB2 app that has date values in 4 fields (i.e. Date1_MO, Date1_DA, Date1_CN, Date1_YR). I have several MSAccess queries that I convert this to a date by doing the following:

CDate(Date1_MO & "/" & Date1_DA & "/" & Date1_CN & Format(Date1_YR,"00"))

Piece of cake...however I am struggling with this in MSSQL.

Mostly I am fighting formatting the Year. As you can see, If I were to concatinate the above values i would come up with something like 3/9/204 for a date of March 9, 2004. (Each field is a numeric value).

I have gotten this far...

select CAST(Date1_MO as varchar(2))+ '/' + CAST(Date1_DA as varchar(2))
+ '/' + CAST(Date1_CN as varchar(2))+ CAST(Date1_YR as varchar(2)) as Date1
From tPrices

I still need to convert the whole string to a date, but more importantly, I cannot figure out how to get the last element (Year) to format as '04' instead of '4'. I can't concatinate a 0 in front of it for obvious reasons. (Athough I was tempted, just joking)

I looked through a lot of the T-SQL docs but have come up dry.

Anyway HELP!!!!!!Try this for the last 2 digits of the year:

right('0'+CAST(Date1_YR as varchar(2)), 2)|||Came to the same conclusion about the same time you replied...

Just playing with the conversion now.

Thanks for your response...

formatting numbers

Hi,
I was wondering whether there is a way to format numbers in sql as below.
Meaning I was to convert 1 to 0001 (with maximum of 4 as the length)
So that 100 = 0100, 0999, 0022, 1222, etc...
Does anyone know how to do this?
Thanks
DhruvTry:
declare @.x int
set @.x = 121
select right('0000' + cast(@.x as varchar(4)), 4) num
set @.x = 100
select right('0000' + cast(@.x as varchar(4)), 4) num
set @.x = 999
select right('0000' + cast(@.x as varchar(4)), 4) num
- Vishal|||Try this:
declare @.i smallint
set @.i = 23 -- or ...
select RIGHT('000' + CAST(@.i AS varchar(4)), 4)
HTH
Vern
>--Original Message--
>Hi,
>I was wondering whether there is a way to format numbers
in sql as below.
>Meaning I was to convert 1 to 0001 (with maximum of 4 as
the length)
>So that 100 = 0100, 0999, 0022, 1222, etc...
>Does anyone know how to do this?
>Thanks
>Dhruv
>.
>|||You could do soemthing like this in T-SQL:
declare @.i int
set @.i = 122
select right('0000' + cast(@.i as varchar(4)), 4)
But I'd question why you'd want to do this on SQL Server
side? This is best done at the clietn side in whatever
language you may be using. Most languages have good
support for this type of string manipulation.
Linchi
>--Original Message--
>Hi,
>I was wondering whether there is a way to format numbers
in sql as below.
>Meaning I was to convert 1 to 0001 (with maximum of 4 as
the length)
>So that 100 = 0100, 0999, 0022, 1222, etc...
>Does anyone know how to do this?
>Thanks
>Dhruv
>.
>|||SELECT RIGHT('0000'+RTRIM(1), 4)
However, I agree with Linchi. Do your "prettifying" of the data where it
belongs, in the presentation tier.
> I was wondering whether there is a way to format numbers in sql as below.
> Meaning I was to convert 1 to 0001 (with maximum of 4 as the length)
> So that 100 = 0100, 0999, 0022, 1222, etc...
> Does anyone know how to do this?
> Thanks
> Dhruv|||I don't know that I completely agree with that, though I see the point.
Consider the case where you use the same data 10 places, using the same
stored procedure. The formatting would be easier done in the procedure,
rather than the UI.
--
----
--
Louis Davidson (drsql@.hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23GXS0IrjDHA.2312@.TK2MSFTNGP12.phx.gbl...
> SELECT RIGHT('0000'+RTRIM(1), 4)
>
> However, I agree with Linchi. Do your "prettifying" of the data where it
> belongs, in the presentation tier.
>
> > I was wondering whether there is a way to format numbers in sql as
below.
> >
> > Meaning I was to convert 1 to 0001 (with maximum of 4 as the length)
> >
> > So that 100 = 0100, 0999, 0022, 1222, etc...
> >
> > Does anyone know how to do this?
> >
> > Thanks
> >
> > Dhruv
>|||> Consider the case where you use the same data 10 places, using the same
> stored procedure.
In most client applications, you can have a common formatting routine.|||Awesome
Thanks
"Vishal Parkar" <_vgparkar@.yahoo.co.in> wrote in message news:<#LU2$ArjDHA.744@.tk2msftngp13.phx.gbl>...
> Try:
> declare @.x int
> set @.x = 121
> select right('0000' + cast(@.x as varchar(4)), 4) num
> set @.x = 100
> select right('0000' + cast(@.x as varchar(4)), 4) num
> set @.x = 999
> select right('0000' + cast(@.x as varchar(4)), 4) num

Formatting number in sql

How do you convert the following Access query to SQL Server ?
Format([tblA].[PRICE],"0.0000") AS Price
Thanks.SELECT Price = CONVERT(DECIMAL(10,4), tblA.Price)
..
Or, you could let the client application format for you.
"fniles" <fniles@.pfmail.com> wrote in message
news:%23hU7bAp%23FHA.2420@.TK2MSFTNGP12.phx.gbl...
> How do you convert the following Access query to SQL Server ?
> Format([tblA].[PRICE],"0.0000") AS Price
> Thanks.
>|||fniles wrote:

> How do you convert the following Access query to SQL Server ?
> Format([tblA].[PRICE],"0.0000") AS Price
> Thanks.
Access is an application development environment as well as a database.
SQL Server isn't. The client application is what controls how your
numeric values are formatted, not SQL Server. You need to consult the
documentation for whatever client environment you are running.
David Portas
SQL Server MVP
--

Formatting Dates

Why would this not format a date into the dd/mm/yy format ?
CONVERT(datetime, Actioned,103) As Actioned
Actioned is a column of type datetime.
Thanks in advance...Because you're converting it to datetime, not a string. ;)
"McHenry" wrote:

> Why would this not format a date into the dd/mm/yy format ?
> CONVERT(datetime, Actioned,103) As Actioned
>
> Actioned is a column of type datetime.
>
> Thanks in advance...
>
>|||Probably because the ONLY -- repeat for those who never learned
Standard SQL-- the **ONLY** format is based on ISO-8601 (should explain
ISO?).
Duh!
It is also the only one in the rest of the ISO Standards. But you did
your research, before you posted, right?
You are one of the kids I want to hit with a stick!! You think that
your local "hillbilly dialect" is law of the universe.
Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just SQL
and RDBMS.
Violate ISO standard in your applications and not in the database. And
comment your errors, so that a better programmer can find a correct
after you are fired.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145329781.514897.34950@.v46g2000cwv.googlegroups.com...
> Probably because the ONLY -- repeat for those who never learned
> Standard SQL-- the **ONLY** format is based on ISO-8601 (should explain
> ISO?).
>
Joe it's been a while since I've had the pleasure of one of your opinionated
condescending responses, great to hear from you again and thanks for the
input. Last time I had the pleasure of your whine was when you helped with
nested sets, an excellent solution I might add.

> Duh!
> It is also the only one in the rest of the ISO Standards. But you did
> your research, before you posted, right?
> You are one of the kids I want to hit with a stick!! You think that
> your local "hillbilly dialect" is law of the universe.
Joe if hitting kids with sticks is a fantasy I am sure there are
professionals that can help, were you spanked roughly as a child ?

> Why are you formatting data in the back end? The basic principle of a
> tiered architecture is that display is done in the front end and never
> in the back end. This a more basic programming principle than just SQL
> and RDBMS.
I would have done my formatting in the frond end application however the
rows returned are being displayed in an MS Access list box that does not
allow for formatting and therefor I must format the information at the
server end, not to confuse you with the facts Joe as it would interrupt the
constant flap of your jaw

> Violate ISO standard in your applications and not in the database. And
> comment your errors, so that a better programmer can find a correct
> after you are fired.
>
Personal experience ?|||"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:8C104A08-E6B9-4B10-BC20-805041F19702@.microsoft.com...
> Because you're converting it to datetime, not a string. ;)
> "McHenry" wrote:
>
Thanks Rob, interestingly I have two scenarios where I have made the same
mistake, one formatted the dates as desired however this one didn't...|||"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:8C104A08-E6B9-4B10-BC20-805041F19702@.microsoft.com...
> Because you're converting it to datetime, not a string. ;)
> "McHenry" wrote:
>
Rob, so formatting to 103 should be to type char(8) ?|||>> Joe it's been a while since I've had the pleasure of one of your opiniona
ted
condescending responses, great to hear from you again and thanks for
the
input. Last time I had the pleasure of your whine was when you helped
with
nested sets, an excellent solution I might add. <<
Thank you, Grasshopper :) Oh, my wife should become a Zen Monk this
year. Then she can beat you with a stick :)
Out of the "zen mode"; have you found a good enumerated path set model
to Nested Sets model algorithm? All I have is a enumerated path to
adjacency list to nested sets program. The overhead is bad.|||"McHenry" <mchenry@.mchenry.com> wrote in message
news:44445f25$0$16677$5a62ac22@.per-qv1-newsreader-01.iinet.net.au...
> "Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
> news:8C104A08-E6B9-4B10-BC20-805041F19702@.microsoft.com...
> Rob, so formatting to 103 should be to type char(8) ?
>
Oops meant CHAR(10)|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145331802.606758.250160@.u72g2000cwu.googlegroups.com...
> condescending responses, great to hear from you again and thanks for
> the
> input. Last time I had the pleasure of your whine was when you helped
> with
> nested sets, an excellent solution I might add. <<
> Thank you, Grasshopper :) Oh, my wife should become a Zen Monk this
> year. Then she can beat you with a stick :)
Reading between the lines Joe I think the beating with sticks thing goes
back long before you even started reasearching Zen...

> Out of the "zen mode"; have you found a good enumerated path set model
> to Nested Sets model algorithm? All I have is a enumerated path to
> adjacency list to nested sets program. The overhead is bad.
>
It was quite a while ago and the scenario was a component breakdown into
parts and sub parts, your solution worked perfectly and is very impressive,
once I got my head around it. I did try at a later time to adapt it to a
genealogy application using two parallel nested sets however it simply got
too complicated and as it was a private project I put it to bed...
Thanks again for your help and I often think of your opinionated tones and
smile when I'm photocopying sections from one of your books :)|||CONVERT(carchar(10), Actioned,103) As Actioned
Is it right?
"McHenry"?? ??? ??:

> Why would this not format a date into the dd/mm/yy format ?
> CONVERT(datetime, Actioned,103) As Actioned
>
> Actioned is a column of type datetime.
>
> Thanks in advance...
>
>

Formatting a float in varchar but NOT in scientific notation

I'm trying to find a way to format a FLOAT variable into a varchar in
SQL Server 2000 but using CAST/CONVERT I can only get scientific
notation i.e. 1e+006 instead of 1000000 which isn't really what I
wanted.

Preferably the varchar would display the number to 2 decimal places
but I'd settle for integers only as this conversion isn't business
critical and is a nice to have for background information.

Casting to MONEY or NUMERIC before converting to a varchar works fine
for most cases but of course runs the risk of arithmetic overflow if
the FLOAT value is too precise for MONEY/NUMERIC to handle. If anyone
knows of an easy way to test whether overflow will occur and therefore
to know not to convert it then that would be an option.

I appreciate SQL Server isn't great at formatting and it would be far
easier in the client code but code this is being performed as a
description of a very simple calculation in a trigger, all stored to
the database on the server side so there's no opportunity for client
intervention.

Example code:

declare @.testFloat float
select @.testFloat = 1000000.12

select convert(varchar(100),@.testFloat) -- gives 1e+006
select cast(@.testFloat as varchar(100)) -- gives 1e+006
select convert(varchar(100),cast(@.testFloat as money)) -- gives
1000000.12

select @.testFloat = 12345678905345633453453624453453524.123

select convert(varchar(100),cast(@.testFloat as money)) -- gives
arithmetic overflow error
select convert(varchar(100),cast(@.testFloat as numeric)) -- gives
arithmetic overflow error

Any suggestions welcome...

Cheers
DaveTry specifying the desired precision and scale on your decimal/numeric
declaration:

SELECT CONVERT(varchar(100), CAST(@.testFloat AS decimal(38,2)))

--
Hope this helps.

Dan Guzman
SQL Server MVP

"David Sharp" <dave@.daveandcaz.freeserve.co.uk> wrote in message
news:ca434844.0312130327.4482a7a@.posting.google.co m...
> I'm trying to find a way to format a FLOAT variable into a varchar in
> SQL Server 2000 but using CAST/CONVERT I can only get scientific
> notation i.e. 1e+006 instead of 1000000 which isn't really what I
> wanted.
> Preferably the varchar would display the number to 2 decimal places
> but I'd settle for integers only as this conversion isn't business
> critical and is a nice to have for background information.
> Casting to MONEY or NUMERIC before converting to a varchar works fine
> for most cases but of course runs the risk of arithmetic overflow if
> the FLOAT value is too precise for MONEY/NUMERIC to handle. If anyone
> knows of an easy way to test whether overflow will occur and therefore
> to know not to convert it then that would be an option.
> I appreciate SQL Server isn't great at formatting and it would be far
> easier in the client code but code this is being performed as a
> description of a very simple calculation in a trigger, all stored to
> the database on the server side so there's no opportunity for client
> intervention.
> Example code:
> declare @.testFloat float
> select @.testFloat = 1000000.12
> select convert(varchar(100),@.testFloat) -- gives 1e+006
> select cast(@.testFloat as varchar(100)) -- gives 1e+006
> select convert(varchar(100),cast(@.testFloat as money)) -- gives
> 1000000.12
> select @.testFloat = 12345678905345633453453624453453524.123
> select convert(varchar(100),cast(@.testFloat as money)) -- gives
> arithmetic overflow error
> select convert(varchar(100),cast(@.testFloat as numeric)) -- gives
> arithmetic overflow error
> Any suggestions welcome...
> Cheers
> Dave|||STR() function might help you.

SELECT STR(123.45, 6, 1)

Check BOL.

"David Sharp" <dave@.daveandcaz.freeserve.co.uk> wrote in message
news:ca434844.0312130327.4482a7a@.posting.google.co m...
> I'm trying to find a way to format a FLOAT variable into a varchar in
> SQL Server 2000 but using CAST/CONVERT I can only get scientific
> notation i.e. 1e+006 instead of 1000000 which isn't really what I
> wanted.
> Preferably the varchar would display the number to 2 decimal places
> but I'd settle for integers only as this conversion isn't business
> critical and is a nice to have for background information.
> Casting to MONEY or NUMERIC before converting to a varchar works fine
> for most cases but of course runs the risk of arithmetic overflow if
> the FLOAT value is too precise for MONEY/NUMERIC to handle. If anyone
> knows of an easy way to test whether overflow will occur and therefore
> to know not to convert it then that would be an option.
> I appreciate SQL Server isn't great at formatting and it would be far
> easier in the client code but code this is being performed as a
> description of a very simple calculation in a trigger, all stored to
> the database on the server side so there's no opportunity for client
> intervention.
> Example code:
> declare @.testFloat float
> select @.testFloat = 1000000.12
> select convert(varchar(100),@.testFloat) -- gives 1e+006
> select cast(@.testFloat as varchar(100)) -- gives 1e+006
> select convert(varchar(100),cast(@.testFloat as money)) -- gives
> 1000000.12
> select @.testFloat = 12345678905345633453453624453453524.123
> select convert(varchar(100),cast(@.testFloat as money)) -- gives
> arithmetic overflow error
> select convert(varchar(100),cast(@.testFloat as numeric)) -- gives
> arithmetic overflow error
> Any suggestions welcome...
> Cheers
> Dave|||Dan and Igor, both examples worked great, thanks very much.

SELECT CONVERT(varchar(100), CAST(@.testFloat AS decimal(38,2)))
SELECT STR(@.testFloat, 38, 2)

Cheers
Dave

Formating Sql Results

In the following Query I would like the results to look like:

2003/03/03 10PM

Now it looks like:

2003/03/03 10

Is there any way to convert the 10 to a 10pm when its already part of an expression?

SELECT CONVERT(varchar(8), DATEPART(yyyy, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(mm, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(dd, Time_stamp)) + ' ' + CONVERT(varchar(8), DATEPART(hh, Time_stamp)) AS Expr1, count(*) FROM dbo.Transactions $WHERECLAUSE$ and type_id=74 GROUP BY CONVERT(varchar(8), DATEPART(yyyy, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(mm, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(dd, Time_stamp)) + ' ' + CONVERT(varchar(8), DATEPART(hh, Time_stamp))CONVERT(varchar(8), DATEPART(hh, Time_stamp),100)|||Originally posted by Satya
CONVERT(varchar(8), DATEPART(hh, Time_stamp),100)

changing:
CONVERT(varchar(8), DATEPART(hh, Time_stamp))

to:
CONVERT(varchar(8), DATEPART(hh, Time_stamp), 100)

Doesn't change the output at all?|||Hi,

Replace
CONVERT(varchar(8), DATEPART(hh, Time_stamp),100)
With
Select substring ( Replace(convert(varchar, getdate(), 100), substring(convert(varchar, getdate(), 100), charindex(':', getdate()), 3), ''), 11, len(convert(varchar, getdate(),100)))

i think give u the desire result.

Cheers,
Gola munjal

Originally posted by Will trever
In the following Query I would like the results to look like:

2003/03/03 10PM

Now it looks like:

2003/03/03 10

Is there any way to convert the 10 to a 10pm when its already part of an expression?

SELECT CONVERT(varchar(8), DATEPART(yyyy, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(mm, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(dd, Time_stamp)) + ' ' + CONVERT(varchar(8), DATEPART(hh, Time_stamp)) AS Expr1, count(*) FROM dbo.Transactions $WHERECLAUSE$ and type_id=74 GROUP BY CONVERT(varchar(8), DATEPART(yyyy, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(mm, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(dd, Time_stamp)) + ' ' + CONVERT(varchar(8), DATEPART(hh, Time_stamp))|||Thanks for fine tuning...Gola|||one more solution...

select convert(varchar,getdate(),111) +
' ' +
left(convert(varchar,getdate(),108),2) +
right(convert(varchar,getdate(),100),2)

Formating Reports To Print

How can I fixa report that displays correctly on the web page (Report
manager) but when I either print or convert to .pdf, some of the text boxes
shifts to a second and maybe a third ?
The report is actualy smaller than a regular letter page.
Any ideas ?You will have to build the report to render as a pdf. In doing so the
web formatting may look out of allignment but the pdf file will look
right. It is very difficult to get them both to look good.
Also, sometimes placing rectangles behind your report items will
prevent them from going outside the report boundries.
Another thing to check is to make sure the "report" page size and
"body" size properties are matching or similar (ie. set the report page
size to 11 x 8.5 for a Landscape print and set the body size to 10.75 x
8 to fit within the page boundries) and only work with in these
boundries. Take some time to try out different margin settings as
well. Work with it a little and usually you can come up with something
that works in the web and pdf worlds.
Let me know how it goes!

FORMAT/COVERT DATE 2004-06-22-10.21.36.897641

Hi,
I need to format my date exactly like this: "2004-06-22-10.21.36.897641".
The last 3 digits can be zero. So I need soemthing that can put convert a
DateTime-field to a Custom Format liek this: "yyyy-MM-dd-HH.mm.ss.ttt000".
Does anybody knos how to do this?
Thanks a lot in advance!
PieterDragu
select CAST({fn CURRENT_DATE()} AS VARCHAR(10))+'.'+
CAST({fn extract(hour from getdate())}AS VARCHAR(2))+'.'+
CAST({fn extract(minute from getdate())}AS VARCHAR(2))+'.'+
CAST({fn extract(second from getdate())}AS VARCHAR(2))+'.'+
CAST({fn extract(mi from getdate())}AS VARCHAR(2))+'000'
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:O4Tzg9CWEHA.3016@.tk2msftngp13.phx.gbl...
> Hi,
> I need to format my date exactly like this: "2004-06-22-10.21.36.897641".
> The last 3 digits can be zero. So I need soemthing that can put convert a
> DateTime-field to a Custom Format liek this: "yyyy-MM-dd-HH.mm.ss.ttt000".
> Does anybody knos how to do this?
> Thanks a lot in advance!
> Pieter
>|||It didn't gave me really what I wanted, but thanks anyway, it helped me to
find a solution.
I finally came with this:
CONVERT(CHAR(4), DATEPART(yyyy, C.Calldate)) + '-' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(mm, C.Calldate)), 2) + '-' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(dd, C.Calldate)), 2) + '-' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(hh, C.Calldate)), 2) + '.' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(mi, C.Calldate)), 2) + '.' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(ss, C.Calldate)), 2) + '.' +
LEFT(CONVERT(VARCHAR, DATEPART(ms, C.Calldate)) + '00000', 5)
as CallDate
Not really a great solution maybe, but it does what I wan't it to do.. :-)
for exemple:
2004-06-22-08.32.05.12300
2004-06-22-08.40.14.59000
2004-06-22-08.41.13.53000
2004-06-22-08.44.38.21700
2004-06-22-08.44.37.73300
2004-06-22-08.45.40.13000
2004-06-22-08.55.36.25000
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23hT25FDWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Dragu
> select CAST({fn CURRENT_DATE()} AS VARCHAR(10))+'.'+
> CAST({fn extract(hour from getdate())}AS VARCHAR(2))+'.'+
> CAST({fn extract(minute from getdate())}AS VARCHAR(2))+'.'+
> CAST({fn extract(second from getdate())}AS VARCHAR(2))+'.'+
> CAST({fn extract(mi from getdate())}AS VARCHAR(2))+'000'
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:O4Tzg9CWEHA.3016@.tk2msftngp13.phx.gbl...
"2004-06-22-10.21.36.897641".[vbcol=seagreen]
a[vbcol=seagreen]
"yyyy-MM-dd-HH.mm.ss.ttt000".[vbcol=seagreen]
>|||On Tue, 22 Jun 2004 12:51:31 +0200, DraguVaso wrote:

>It didn't gave me really what I wanted, but thanks anyway, it helped me to
>find a solution.
>I finally came with this:
>CONVERT(CHAR(4), DATEPART(yyyy, C.Calldate)) + '-' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(mm, C.Calldate)), 2) + '-' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(dd, C.Calldate)), 2) + '-' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(hh, C.Calldate)), 2) + '.' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(mi, C.Calldate)), 2) + '.' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(ss, C.Calldate)), 2) + '.' +
>LEFT(CONVERT(VARCHAR, DATEPART(ms, C.Calldate)) + '00000', 5)
>as CallDate
>Not really a great solution maybe, but it does what I wan't it to do.. :-)
>for exemple:
>2004-06-22-08.32.05.12300
>2004-06-22-08.40.14.59000
>2004-06-22-08.41.13.53000
>2004-06-22-08.44.38.21700
>2004-06-22-08.44.37.73300
>2004-06-22-08.45.40.13000
>2004-06-22-08.55.36.25000
Hi Pieter,
How about this one, then?
SELECT REPLACE(REPLACE(CONVERT(char(23), CURRENT_TIMESTAMP, 121),
' ','-'),':','.') + '000'
2004-06-23-00.02.59.763000
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks! Great solution! :-)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:93bhd0habsbdqi12qtkhpm24kjq8b6flge@.
4ax.com...
> On Tue, 22 Jun 2004 12:51:31 +0200, DraguVaso wrote:
>
to[vbcol=seagreen]
:-)[vbcol=seagreen]
> Hi Pieter,
> How about this one, then?
> SELECT REPLACE(REPLACE(CONVERT(char(23), CURRENT_TIMESTAMP, 121),
> ' ','-'),':','.') + '000'
> 2004-06-23-00.02.59.763000
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

FORMAT/COVERT DATE 2004-06-22-10.21.36.897641

Hi,
I need to format my date exactly like this: "2004-06-22-10.21.36.897641".
The last 3 digits can be zero. So I need soemthing that can put convert a
DateTime-field to a Custom Format liek this: "yyyy-MM-dd-HH.mm.ss.ttt000".
Does anybody knos how to do this?
Thanks a lot in advance!
Pieter
Dragu
select CAST({fn CURRENT_DATE()} AS VARCHAR(10))+'.'+
CAST({fn extract(hour from getdate())}AS VARCHAR(2))+'.'+
CAST({fn extract(minute from getdate())}AS VARCHAR(2))+'.'+
CAST({fn extract(second from getdate())}AS VARCHAR(2))+'.'+
CAST({fn extract(mi from getdate())}AS VARCHAR(2))+'000'
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:O4Tzg9CWEHA.3016@.tk2msftngp13.phx.gbl...
> Hi,
> I need to format my date exactly like this: "2004-06-22-10.21.36.897641".
> The last 3 digits can be zero. So I need soemthing that can put convert a
> DateTime-field to a Custom Format liek this: "yyyy-MM-dd-HH.mm.ss.ttt000".
> Does anybody knos how to do this?
> Thanks a lot in advance!
> Pieter
>
|||It didn't gave me really what I wanted, but thanks anyway, it helped me to
find a solution.
I finally came with this:
CONVERT(CHAR(4), DATEPART(yyyy, C.Calldate)) + '-' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(mm, C.Calldate)), 2) + '-' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(dd, C.Calldate)), 2) + '-' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(hh, C.Calldate)), 2) + '.' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(mi, C.Calldate)), 2) + '.' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(ss, C.Calldate)), 2) + '.' +
LEFT(CONVERT(VARCHAR, DATEPART(ms, C.Calldate)) + '00000', 5)
as CallDate
Not really a great solution maybe, but it does what I wan't it to do.. :-)
for exemple:
2004-06-22-08.32.05.12300
2004-06-22-08.40.14.59000
2004-06-22-08.41.13.53000
2004-06-22-08.44.38.21700
2004-06-22-08.44.37.73300
2004-06-22-08.45.40.13000
2004-06-22-08.55.36.25000
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23hT25FDWEHA.3664@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Dragu
> select CAST({fn CURRENT_DATE()} AS VARCHAR(10))+'.'+
> CAST({fn extract(hour from getdate())}AS VARCHAR(2))+'.'+
> CAST({fn extract(minute from getdate())}AS VARCHAR(2))+'.'+
> CAST({fn extract(second from getdate())}AS VARCHAR(2))+'.'+
> CAST({fn extract(mi from getdate())}AS VARCHAR(2))+'000'
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:O4Tzg9CWEHA.3016@.tk2msftngp13.phx.gbl...
"2004-06-22-10.21.36.897641".[vbcol=seagreen]
a[vbcol=seagreen]
"yyyy-MM-dd-HH.mm.ss.ttt000".
>
|||On Tue, 22 Jun 2004 12:51:31 +0200, DraguVaso wrote:

>It didn't gave me really what I wanted, but thanks anyway, it helped me to
>find a solution.
>I finally came with this:
>CONVERT(CHAR(4), DATEPART(yyyy, C.Calldate)) + '-' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(mm, C.Calldate)), 2) + '-' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(dd, C.Calldate)), 2) + '-' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(hh, C.Calldate)), 2) + '.' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(mi, C.Calldate)), 2) + '.' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(ss, C.Calldate)), 2) + '.' +
>LEFT(CONVERT(VARCHAR, DATEPART(ms, C.Calldate)) + '00000', 5)
>as CallDate
>Not really a great solution maybe, but it does what I wan't it to do.. :-)
>for exemple:
>2004-06-22-08.32.05.12300
>2004-06-22-08.40.14.59000
>2004-06-22-08.41.13.53000
>2004-06-22-08.44.38.21700
>2004-06-22-08.44.37.73300
>2004-06-22-08.45.40.13000
>2004-06-22-08.55.36.25000
Hi Pieter,
How about this one, then?
SELECT REPLACE(REPLACE(CONVERT(char(23), CURRENT_TIMESTAMP, 121),
' ','-'),':','.') + '000'
2004-06-23-00.02.59.763000
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks! Great solution! :-)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:93bhd0habsbdqi12qtkhpm24kjq8b6flge@.4ax.com... [vbcol=seagreen]
> On Tue, 22 Jun 2004 12:51:31 +0200, DraguVaso wrote:
to[vbcol=seagreen]
:-)
> Hi Pieter,
> How about this one, then?
> SELECT REPLACE(REPLACE(CONVERT(char(23), CURRENT_TIMESTAMP, 121),
> ' ','-'),':','.') + '000'
> 2004-06-23-00.02.59.763000
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

FORMAT/COVERT DATE 2004-06-22-10.21.36.897641

Hi,
I need to format my date exactly like this: "2004-06-22-10.21.36.897641".
The last 3 digits can be zero. So I need soemthing that can put convert a
DateTime-field to a Custom Format liek this: "yyyy-MM-dd-HH.mm.ss.ttt000".
Does anybody knos how to do this?
Thanks a lot in advance!
PieterDragu
select CAST({fn CURRENT_DATE()} AS VARCHAR(10))+'.'+
CAST({fn extract(hour from getdate())}AS VARCHAR(2))+'.'+
CAST({fn extract(minute from getdate())}AS VARCHAR(2))+'.'+
CAST({fn extract(second from getdate())}AS VARCHAR(2))+'.'+
CAST({fn extract(mi from getdate())}AS VARCHAR(2))+'000'
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:O4Tzg9CWEHA.3016@.tk2msftngp13.phx.gbl...
> Hi,
> I need to format my date exactly like this: "2004-06-22-10.21.36.897641".
> The last 3 digits can be zero. So I need soemthing that can put convert a
> DateTime-field to a Custom Format liek this: "yyyy-MM-dd-HH.mm.ss.ttt000".
> Does anybody knos how to do this?
> Thanks a lot in advance!
> Pieter
>|||It didn't gave me really what I wanted, but thanks anyway, it helped me to
find a solution.
I finally came with this:
CONVERT(CHAR(4), DATEPART(yyyy, C.Calldate)) + '-' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(mm, C.Calldate)), 2) + '-' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(dd, C.Calldate)), 2) + '-' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(hh, C.Calldate)), 2) + '.' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(mi, C.Calldate)), 2) + '.' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(ss, C.Calldate)), 2) + '.' +
LEFT(CONVERT(VARCHAR, DATEPART(ms, C.Calldate)) + '00000', 5)
as CallDate
Not really a great solution maybe, but it does what I wan't it to do.. :-)
for exemple:
2004-06-22-08.32.05.12300
2004-06-22-08.40.14.59000
2004-06-22-08.41.13.53000
2004-06-22-08.44.38.21700
2004-06-22-08.44.37.73300
2004-06-22-08.45.40.13000
2004-06-22-08.55.36.25000
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23hT25FDWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Dragu
> select CAST({fn CURRENT_DATE()} AS VARCHAR(10))+'.'+
> CAST({fn extract(hour from getdate())}AS VARCHAR(2))+'.'+
> CAST({fn extract(minute from getdate())}AS VARCHAR(2))+'.'+
> CAST({fn extract(second from getdate())}AS VARCHAR(2))+'.'+
> CAST({fn extract(mi from getdate())}AS VARCHAR(2))+'000'
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:O4Tzg9CWEHA.3016@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > I need to format my date exactly like this:
"2004-06-22-10.21.36.897641".
> > The last 3 digits can be zero. So I need soemthing that can put convert
a
> > DateTime-field to a Custom Format liek this:
"yyyy-MM-dd-HH.mm.ss.ttt000".
> >
> > Does anybody knos how to do this?
> >
> > Thanks a lot in advance!
> >
> > Pieter
> >
> >
>|||On Tue, 22 Jun 2004 12:51:31 +0200, DraguVaso wrote:
>It didn't gave me really what I wanted, but thanks anyway, it helped me to
>find a solution.
>I finally came with this:
>CONVERT(CHAR(4), DATEPART(yyyy, C.Calldate)) + '-' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(mm, C.Calldate)), 2) + '-' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(dd, C.Calldate)), 2) + '-' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(hh, C.Calldate)), 2) + '.' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(mi, C.Calldate)), 2) + '.' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(ss, C.Calldate)), 2) + '.' +
>LEFT(CONVERT(VARCHAR, DATEPART(ms, C.Calldate)) + '00000', 5)
>as CallDate
>Not really a great solution maybe, but it does what I wan't it to do.. :-)
>for exemple:
>2004-06-22-08.32.05.12300
>2004-06-22-08.40.14.59000
>2004-06-22-08.41.13.53000
>2004-06-22-08.44.38.21700
>2004-06-22-08.44.37.73300
>2004-06-22-08.45.40.13000
>2004-06-22-08.55.36.25000
Hi Pieter,
How about this one, then?
SELECT REPLACE(REPLACE(CONVERT(char(23), CURRENT_TIMESTAMP, 121),
' ','-'),':','.') + '000'
2004-06-23-00.02.59.763000
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks! Great solution! :-)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:93bhd0habsbdqi12qtkhpm24kjq8b6flge@.4ax.com...
> On Tue, 22 Jun 2004 12:51:31 +0200, DraguVaso wrote:
> >It didn't gave me really what I wanted, but thanks anyway, it helped me
to
> >find a solution.
> >
> >I finally came with this:
> >CONVERT(CHAR(4), DATEPART(yyyy, C.Calldate)) + '-' +
> >RIGHT('0' + CONVERT(VARCHAR, DATEPART(mm, C.Calldate)), 2) + '-' +
> >RIGHT('0' + CONVERT(VARCHAR, DATEPART(dd, C.Calldate)), 2) + '-' +
> >RIGHT('0' + CONVERT(VARCHAR, DATEPART(hh, C.Calldate)), 2) + '.' +
> >RIGHT('0' + CONVERT(VARCHAR, DATEPART(mi, C.Calldate)), 2) + '.' +
> >RIGHT('0' + CONVERT(VARCHAR, DATEPART(ss, C.Calldate)), 2) + '.' +
> >LEFT(CONVERT(VARCHAR, DATEPART(ms, C.Calldate)) + '00000', 5)
> >as CallDate
> >
> >Not really a great solution maybe, but it does what I wan't it to do..
:-)
> >for exemple:
> >2004-06-22-08.32.05.12300
> >2004-06-22-08.40.14.59000
> >2004-06-22-08.41.13.53000
> >2004-06-22-08.44.38.21700
> >2004-06-22-08.44.37.73300
> >2004-06-22-08.45.40.13000
> >2004-06-22-08.55.36.25000
> Hi Pieter,
> How about this one, then?
> SELECT REPLACE(REPLACE(CONVERT(char(23), CURRENT_TIMESTAMP, 121),
> ' ','-'),':','.') + '000'
> 2004-06-23-00.02.59.763000
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

format time to 00 for a date

hi,
How do I format time of a datecolumn to 00:00:00 ?
I have a table with date column. how do I get only date for comparision, and
exclude or convert time to 00:00:00 in sql query
regards
ypulTry this and you will see what you can do:
select dateadd(dd, 0, datediff(dd, 0, getdate()))
Perayu
"ypul" <ypul@.hotmail.com> wrote in message
news:OVD9DHGuFHA.1472@.TK2MSFTNGP15.phx.gbl...
> hi,
> How do I format time of a datecolumn to 00:00:00 ?
> I have a table with date column. how do I get only date for comparision,
> and
> exclude or convert time to 00:00:00 in sql query
> regards
> ypul
>
>
>|||> How do I format time of a datecolumn to 00:00:00 ?
Formatting is usually done at the client tier, but you can handle it using
CONVERT with a style parameter, if you must.
SELECT CONVERT(CHAR(8), GETDATE(), 24)
For a complete list of styles, please see http://www.aspfaq.com/2464

> I have a table with date column. how do I get only date for comparision,
> and
> exclude or convert time to 00:00:00 in sql query
If there is an index on the column, you should always a range query. (And
if you don't care about time, you might consider not storing it.)
For example, to get all the rows with a date of 2005-09-12 (regardless of
time), you can say:
WHERE date_column >= '20050912'
AND date_column < '20050913'
To do this dynamically (e.g. always yesterday),
DECLARE @.start SMALLDATETIME
SET @.start = DATEDIFF(DAY, 0, GETDATE()) - 1
SELECT
..
WHERE date_column >= @.start
AND date_column < @.start + 1
You may be tempted to use BETWEEN but please read the following article:
http://www.aspfaq.com/2280
You maybe tempted to use localized formats for dates (e.g. d/m/y or m/d/y)
but please read the following article and the links therein:
http://www.aspfaq.com/2023|||Basically, what you'd do is to format your data on the client, but if you
insist on doing it on the server, read more here:
http://msdn.microsoft.com/library/d...br />
2f3o.asp
ML|||Thanks a lot all
"Between" article was also useful...
gr8 help
ypul
"ypul" <ypul@.hotmail.com> wrote in message
news:OVD9DHGuFHA.1472@.TK2MSFTNGP15.phx.gbl...
> hi,
> How do I format time of a datecolumn to 00:00:00 ?
> I have a table with date column. how do I get only date for comparision,
and
> exclude or convert time to 00:00:00 in sql query
> regards
> ypul
>
>
>

format phone field

Hello,

I have a phone number field with the format (123)-456-7890 I need to convert this to 1234567890 formats while I am retrieving data from the table. How can I do this?

My first thought is to use SUBSTRING(...) in your SELECT query. For example...

SELECT (SUBSTRING(PHONE,2,3) + SUBSTRING(PHONE,7,3) + SUBSTRING(PHONE,11,4)) AS PHONE FROM YOURTABLE

This assumes that all your phone records are in the format you described.

|||

Thanks for the reply, will this fail if there is nothing in Phone, if yes, hat can I do to solev that problem?

|||

SELECT REPLACE(REPLACE(REPLACE(Phone,'(',''),')',''),'-','') AS Phone

Basically that just removes all ()- from the phone field before returning it to you.

|||Do whatmotleysuggested. I'm not sure, but youmayneed to use ISNULL(PHONE,'') in place of PHONE if your PHONE column is nullable.

SELECT REPLACE(REPLACE(REPLACE(ISNULL(Phone,''),'(',''),')',''),'-','') AS Phone

format in pdf

Hy,

I would like to convert my report in pdf. Do you have the code to convert it? I found a lot of code for crystal report but not for Reporting service.

thank you

oolon

In fact, I try to use this kind of code

ReportingService rs = new ReportingService();

rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

byte[] ResultStream;

string[] StreamIdentifiers;

string OptionalParam = null, filename = "NorthwindCustomers.pdf";

ParameterValue[] optionalParams = null;

Warning[] optionalWarnings = null;

ResultStream = rs.Render("/Northwind Customers", "PDF", null,

"<DeviceInfo><StreamRoot>/RSWebServiceXS/</StreamRoot></DeviceInfo>", null, null,

null, out OptionalParam, out OptionalParam, out optionalParams,

out optionalWarnings, out StreamIdentifiers);

// Creating a verbatim string.

FileStream stream = File.OpenWrite(@."C:\Articles\SQL Server Reporting

Services\SourceCode\RSWebServiceXS\NorthwindCustomers\" + filename);

stream.Write(ResultStream, 0, ResultStream.Length);

stream.Close();

But my code doesn't recognize The class Reporting Service. Why? i put the librairies :

using System;

using System.IO;

using System.Web;

using System.Web.Services;

using System.Web.Services.Protocols;

What librairy's missing?

Thank you

|||You need to create the proxy class, if you haven't already done this. Take a look at this page for instructions:

http://msdn2.microsoft.com/en-us/library/ms155134.aspx

If you use the Web Reference method, the proxy classes are generated in the default namespace of your project with the reference name you specified in the Web Reference tool.

format for Convert

Hello,

SELECT name + '- ' + CONVERT(varchar,amt) as ddlCap from myTable

How can I get amt be like "#0.00" format in ddlCap?

Hi,

Please run the below statement to see if it is what you want...

SELECT
amt,
CONVERT(varchar(20),amt,0),
CONVERT(varchar(20),amt,1)
FROM myTable

Eralper
http://www.kodyaz.com

format datetime without characters

I need to convert the date format with an output of yyyymmdd'
I have tried the following code but it doesn't produce the desired output
CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
Any suggestions?
Thanks in advanceThat should work if supp_creation_date is a DATETIME or SMALLDATETIME. What
result do you get? Can you post some code to reproduce it? What version of
SQL Server? Here's an example:
SELECT CONVERT(VARCHAR(8),CURRENT_TIMESTAMP,112
)
Result:
20050502
(1 row(s) affected)
If you get something else then I'd guess that in the context in which you
are using it the result is being implicitly cast to some other datatype.
David Portas
SQL Server MVP
--|||That seems to work for me:
Declare @.Date DateTime
Set @.Date = Current_TimeStamp
Select Convert(VarChar(8), @.Date, 112)
Produces:
20050502
What output are you getting?
Thomas
"Sherry" <Sherry@.discussions.microsoft.com> wrote in message
news:99360DE6-995D-45B2-80E0-744A800DE396@.microsoft.com...
>I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance|||Sherry,
What is the type of column [supp_creation_date]?. If it is not a datetime
then you have to convert it to, before using the statement you posted.
Example:
declare @.s varchar(10)
set @.s = '05/02/2005'
select convert(char(8), convert(datetime, @.s, 101), 112)
go
-- this works
select convert(char(8), getdate(), 112)
go
AMB
"Sherry" wrote:

> I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance|||Sherry, Your column in your table must not be a datetime... you'll have to
cast it to a datetime first.
Try this
Select CONVERT(VARCHAR(8), Cast(supp_creation_date As DateTime) , 112 )
From YourTable
"Sherry" wrote:

> I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance|||Does it produce the proper format in Query Analyzer? This is always the
first place to try stuff out, as QA is very good not to insert it's own
formatting to the output:
create table test
(
supp_creation_date datetime
)
insert into test
select getdate()
go
select CONVERT(VARCHAR(8),supp_creation_date , 112 )
from test
returns:
20050502
However, if this value gets put back into a datetime variable before the
client recieves it, or the client puts it inot a date container, the
formatting goes away. Formatting only works on textual values, not date
values.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Sherry" <Sherry@.discussions.microsoft.com> wrote in message
news:99360DE6-995D-45B2-80E0-744A800DE396@.microsoft.com...
>I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance

format datetime column

Hello,

I am wondering if someone could help me with formatting datatime column.

Goal - keep column in datatime instead of convert to varchar

Current - '2006-06-21 16:54:33.000'

Wants - '2006-06-21 16:54:33'

Any help is appreciated!

-Lawrence

Code Bits

declare @.time datetime

set @.time = '2006-06-21 16:54:33.000'

select @.time, convert(varchar(255), @.time, 20)

You usually do not want to format the output in the database. This would lead to the question why do you need to do this in T-SQL?

The front-end should be able to format the date to a proper format instead of relying on the database to do it. The database gives the data and the front-end presents it. Skipping the milliseconds are most likely the task of the front-end.

|||

I know what you are saying, but the reason is that the format from this t-sql goes into a transformation and oddly enough the transformation does not accept datetime format with millisecond. I thought SET DateFormat would be able to set any datetime format, but I guess that feature is very limited.

-Lawrence

|||declare @.time datetime
set @.time = '2006-06-21 16:54:33.000'
select @.time, convert(varchar(19), @.time, 121)|||What transformation are you referring to? Is it a DTS/SSIS package? If so that can format the data accordingly. Please elaborate on your problem. The datetime value is stored in native format on the server and if you want to format it as string you need to use CONVERT & other string functions to do it. Typically you will send the values as is to the client and format on the client side using richer mechanisms.

Format a string to time

Hi everybody,
I have a string like this : "290302" and I'd like to convert into
the format : "29:03:02" but I can't.
I tried this :
= Format(ds.FieldOra, "T") but it couldn't get the new format.
It displays always the same "290302".
Also I tried with = Format(ds.Field1, "hh:mm:ss")
but all remains the same.
Could you help me ?
Thanks in advance!
DomenicoDomenico,
You can't do that because dates aren't stored as strings internaly. They
are numbers.
You could use the regular string manipualtion functions to get the result
you want.
=Left(ds.Field1,2) & ":" & Mid(dsField1, 3,2) & ":" & Right(ds.Field1, 2)
should just about do it for you.
Regards,
Rob Labbé, MCP, MCAD, MCSD, MCT
Lead Architect/Trainer
Fidelis
Blog: http://spaces.msn.com/members/roblabbe
"Riddick" <Riddick@.discussions.microsoft.com> wrote in message
news:CF69816F-BEEA-497F-80B0-FE59BA0A1688@.microsoft.com...
> Hi everybody,
> I have a string like this : "290302" and I'd like to convert into
> the format : "29:03:02" but I can't.
> I tried this :
> = Format(ds.FieldOra, "T") but it couldn't get the new format.
> It displays always the same "290302".
> Also I tried with = Format(ds.Field1, "hh:mm:ss")
> but all remains the same.
> Could you help me ?
> Thanks in advance!
> Domenico|||Thank you Rob :)
"Rob Labbe (Lowney)" wrote:
> Domenico,
> You can't do that because dates aren't stored as strings internaly. They
> are numbers.
> You could use the regular string manipualtion functions to get the result
> you want.
> =Left(ds.Field1,2) & ":" & Mid(dsField1, 3,2) & ":" & Right(ds.Field1, 2)
> should just about do it for you.
> Regards,
>
> --
> Rob Labbé, MCP, MCAD, MCSD, MCT
> Lead Architect/Trainer
> Fidelis
> Blog: http://spaces.msn.com/members/roblabbe
> "Riddick" <Riddick@.discussions.microsoft.com> wrote in message
> news:CF69816F-BEEA-497F-80B0-FE59BA0A1688@.microsoft.com...
> > Hi everybody,
> >
> > I have a string like this : "290302" and I'd like to convert into
> > the format : "29:03:02" but I can't.
> >
> > I tried this :
> > = Format(ds.FieldOra, "T") but it couldn't get the new format.
> > It displays always the same "290302".
> >
> > Also I tried with = Format(ds.Field1, "hh:mm:ss")
> > but all remains the same.
> >
> > Could you help me ?
> > Thanks in advance!
> >
> > Domenico
>
>