Hi,
I have used an access table to create a chart report to show how many calls
were handled in a month. How do i conver the Access sql to MS sql server
view. Here is the access sql:
SELECT (Format([ComplaintDate],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count]
FROM [Complaints]
GROUP BY (Format([ComplaintDate],"mmm"" '""yy")),
(Year([ComplaintDate])*12+Month([Complai
ntDate])-1)
ORDER BY (Year([ComplaintDate])*12+Month([Complai
ntDate])-1);
In the MS Sql, the word FORMAT is not recognised. I have tried several ways
like this w/o any luck. Can anyone tell me the correct syntax for the above
sql querry.
Thanks
SenthilkumarCheck out CONVERT() in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Senthilkumar" <kesk32@.yahoo.co.in> wrote in message
news:%234%232fJmJGHA.1388@.TK2MSFTNGP11.phx.gbl...
Hi,
I have used an access table to create a chart report to show how many calls
were handled in a month. How do i conver the Access sql to MS sql server
view. Here is the access sql:
SELECT (Format([ComplaintDate],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count]
FROM [Complaints]
GROUP BY (Format([ComplaintDate],"mmm"" '""yy")),
(Year([ComplaintDate])*12+Month([Complai
ntDate])-1)
ORDER BY (Year([ComplaintDate])*12+Month([Complai
ntDate])-1);
In the MS Sql, the word FORMAT is not recognised. I have tried several ways
like this w/o any luck. Can anyone tell me the correct syntax for the above
sql querry.
Thanks
Senthilkumar|||To add to Tom's reply, you might want to consider leaving off the
formatting in the view and just return the raw data. Let the client
format the result set. Any time you format the data on the server, the
formatting functions you use have to operate on every single row, one
at a time.
--Mary
On Tue, 31 Jan 2006 18:09:30 +0530, "Senthilkumar"
<kesk32@.yahoo.co.in> wrote:
>Hi,
>I have used an access table to create a chart report to show how many calls
>were handled in a month. How do i conver the Access sql to MS sql server
>view. Here is the access sql:
>SELECT (Format([ComplaintDate],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count]
>FROM [Complaints]
>GROUP BY (Format([ComplaintDate],"mmm"" '""yy")),
> (Year([ComplaintDate])*12+Month([Complai
ntDate])-1)
>ORDER BY (Year([ComplaintDate])*12+Month([Complai
ntDate])-1);
>In the MS Sql, the word FORMAT is not recognised. I have tried several ways
>like this w/o any luck. Can anyone tell me the correct syntax for the above
>sql querry.
>Thanks
>Senthilkumar
>
Showing posts with label conver. Show all posts
Showing posts with label conver. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
convert
can someone give me the command to conver varchar to date please.
In my table I have varchar(50) Tue Nov 21 00:00:06 EST 2006 for the column
but need to update the colum with 2006-11-21 00:00:06.
Thanks,
Stoneystoney wrote:
> can someone give me the command to conver varchar to date please.
> In my table I have varchar(50) Tue Nov 21 00:00:06 EST 2006 for the column
> but need to update the colum with 2006-11-21 00:00:06.
> Thanks,
> Stoney
Try to look up the CONVERT command in BOL. Here you can see the various
styles you can use. I think in your case you can use 20/120.
Regards
Steen Schlüter Persson
Database Administrator / System Administratorsqlsql
In my table I have varchar(50) Tue Nov 21 00:00:06 EST 2006 for the column
but need to update the colum with 2006-11-21 00:00:06.
Thanks,
Stoneystoney wrote:
> can someone give me the command to conver varchar to date please.
> In my table I have varchar(50) Tue Nov 21 00:00:06 EST 2006 for the column
> but need to update the colum with 2006-11-21 00:00:06.
> Thanks,
> Stoney
Try to look up the CONVERT command in BOL. Here you can see the various
styles you can use. I think in your case you can use 20/120.
Regards
Steen Schlüter Persson
Database Administrator / System Administratorsqlsql
Tuesday, March 20, 2012
Conver String to Date
Hi all,
I am trying to convert the string value of '12.01.50' to a propert date
value of 12/01/1950, I have tried various things but cant seem to get it int
o
the right format although I can convert it to a date type. Can anyone help?
Thanks PhilYou either have to do the conversion going directly from string to string. O
r you have to go from
string to datetime and then string again. You can't just go from string to d
atetime, since datetime
doesn't have any format (the client application does the formatting). So, so
mething like:
CONVERT(varchar(zz), CONVERT(datetime, '12.01.50' , xxx), yyy)
Where xxx and yyy are the appropriate formatting codes (documented in Books
Online, CONVERT). I
prefer to do formatting in the client app, though. Also see
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:24C094A5-2B67-49B3-A40D-BECF53AB5530@.microsoft.com...
> Hi all,
> I am trying to convert the string value of '12.01.50' to a propert date
> value of 12/01/1950, I have tried various things but cant seem to get it i
nto
> the right format although I can convert it to a date type. Can anyone hel
p?
> Thanks Phil
I am trying to convert the string value of '12.01.50' to a propert date
value of 12/01/1950, I have tried various things but cant seem to get it int
o
the right format although I can convert it to a date type. Can anyone help?
Thanks PhilYou either have to do the conversion going directly from string to string. O
r you have to go from
string to datetime and then string again. You can't just go from string to d
atetime, since datetime
doesn't have any format (the client application does the formatting). So, so
mething like:
CONVERT(varchar(zz), CONVERT(datetime, '12.01.50' , xxx), yyy)
Where xxx and yyy are the appropriate formatting codes (documented in Books
Online, CONVERT). I
prefer to do formatting in the client app, though. Also see
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:24C094A5-2B67-49B3-A40D-BECF53AB5530@.microsoft.com...
> Hi all,
> I am trying to convert the string value of '12.01.50' to a propert date
> value of 12/01/1950, I have tried various things but cant seem to get it i
nto
> the right format although I can convert it to a date type. Can anyone hel
p?
> Thanks Phil
conver datetime to age... new
using the following query..
SELECT userid as UserID, Age=datediff(year,Birthdate,getdate())
FROM UserProfile
WHERE UserProfile.Birthdate IS NOT NULL AND
datediff(year,Birthdate,getdate())>=0
order by UserID
the converts the table
userid birthdate
2 1985-03-08 00:00:00.000
9 1998-06-27 00:00:00.000
to the output of:
UserID Age
2 20
9 7
The problem is it doesn't take into consideration for the current day,
rather it looks only at the year. So the age of UserID 9 is actually 6.
-Thanks HP/Thomas on the other thread.http://groups-beta.google.com/group...mming&lr=&hl=en
"chad" <chad@.discussions.microsoft.com> wrote in message
news:1667E4FD-855D-4D1A-BFB9-2CFB0D456005@.microsoft.com...
> using the following query..
> SELECT userid as UserID, Age=datediff(year,Birthdate,getdate())
> FROM UserProfile
> WHERE UserProfile.Birthdate IS NOT NULL AND
> datediff(year,Birthdate,getdate())>=0
> order by UserID
> the converts the table
> userid birthdate
> 2 1985-03-08 00:00:00.000
> 9 1998-06-27 00:00:00.000
>
> to the output of:
> UserID Age
> 2 20
> 9 7
> The problem is it doesn't take into consideration for the current day,
> rather it looks only at the year. So the age of UserID 9 is actually 6.
> -Thanks HP/Thomas on the other thread.
>|||See if this helps:
http://www.tech-archive.net/Archive...04-02/2296.html
AMB
"chad" wrote:
> using the following query..
> SELECT userid as UserID, Age=datediff(year,Birthdate,getdate())
> FROM UserProfile
> WHERE UserProfile.Birthdate IS NOT NULL AND
> datediff(year,Birthdate,getdate())>=0
> order by UserID
> the converts the table
> userid birthdate
> 2 1985-03-08 00:00:00.000
> 9 1998-06-27 00:00:00.000
>
> to the output of:
> UserID Age
> 2 20
> 9 7
> The problem is it doesn't take into consideration for the current day,
> rather it looks only at the year. So the age of UserID 9 is actually 6.
> -Thanks HP/Thomas on the other thread.
>|||I guess this is the easiest solution:
SELECT userid as UserID, Age = datediff(dd,Birthdate,getdate())/365
FROM UserProfile
WHERE UserProfile.Birthdate IS NOT NULL AND
datediff(year,Birthdate,getdate())>=0
order by UserID
"chad" wrote:
> using the following query..
> SELECT userid as UserID, Age=datediff(year,Birthdate,getdate())
> FROM UserProfile
> WHERE UserProfile.Birthdate IS NOT NULL AND
> datediff(year,Birthdate,getdate())>=0
> order by UserID
> the converts the table
> userid birthdate
> 2 1985-03-08 00:00:00.000
> 9 1998-06-27 00:00:00.000
>
> to the output of:
> UserID Age
> 2 20
> 9 7
> The problem is it doesn't take into consideration for the current day,
> rather it looks only at the year. So the age of UserID 9 is actually 6.
> -Thanks HP/Thomas on the other thread.
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Here's a formula I use:
Year(getdate())
- Year(birthdate)
+ case when datepart(dy, birthdate) - datepart(dy, getdate()) < 0
then 0 else -1 end
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQlLtXoechKqOuFEgEQJ93gCgsWvUYstK/25OJhhdk7mUMSsdKqsAmwYF
rv4IxkY87JMffYj7v+i/Y31/
=AH6W
--END PGP SIGNATURE--
chad wrote:
> using the following query..
> SELECT userid as UserID, Age=datediff(year,Birthdate,getdate())
> FROM UserProfile
> WHERE UserProfile.Birthdate IS NOT NULL AND
> datediff(year,Birthdate,getdate())>=0
> order by UserID
> the converts the table
> userid birthdate
> 2 1985-03-08 00:00:00.000
> 9 1998-06-27 00:00:00.000
>
> to the output of:
> UserID Age
> 2 20
> 9 7
> The problem is it doesn't take into consideration for the current day,
> rather it looks only at the year. So the age of UserID 9 is actually 6.
> -Thanks HP/Thomas on the other thread.
>|||Yet another one:
datename(yy,getdate()-vt.birthday) - 1900
SELECT userid as UserID, Age=datediff(year,Birthdate,getdate())
FROM UserProfile
WHERE UserProfile.Birthdate IS NOT NULL AND
datediff(year,Birthdate,getdate())>=0
order by UserID
the converts the table
userid birthdate
2 1985-03-08 00:00:00.000
9 1998-06-27 00:00:00.000
to the output of:
UserID Age
2 20
9 7
The problem is it doesn't take into consideration for the current day,
rather it looks only at the year. So the age of UserID 9 is actually 6.
-Thanks HP/Thomas on the other thread.http://groups-beta.google.com/group...mming&lr=&hl=en
"chad" <chad@.discussions.microsoft.com> wrote in message
news:1667E4FD-855D-4D1A-BFB9-2CFB0D456005@.microsoft.com...
> using the following query..
> SELECT userid as UserID, Age=datediff(year,Birthdate,getdate())
> FROM UserProfile
> WHERE UserProfile.Birthdate IS NOT NULL AND
> datediff(year,Birthdate,getdate())>=0
> order by UserID
> the converts the table
> userid birthdate
> 2 1985-03-08 00:00:00.000
> 9 1998-06-27 00:00:00.000
>
> to the output of:
> UserID Age
> 2 20
> 9 7
> The problem is it doesn't take into consideration for the current day,
> rather it looks only at the year. So the age of UserID 9 is actually 6.
> -Thanks HP/Thomas on the other thread.
>|||See if this helps:
http://www.tech-archive.net/Archive...04-02/2296.html
AMB
"chad" wrote:
> using the following query..
> SELECT userid as UserID, Age=datediff(year,Birthdate,getdate())
> FROM UserProfile
> WHERE UserProfile.Birthdate IS NOT NULL AND
> datediff(year,Birthdate,getdate())>=0
> order by UserID
> the converts the table
> userid birthdate
> 2 1985-03-08 00:00:00.000
> 9 1998-06-27 00:00:00.000
>
> to the output of:
> UserID Age
> 2 20
> 9 7
> The problem is it doesn't take into consideration for the current day,
> rather it looks only at the year. So the age of UserID 9 is actually 6.
> -Thanks HP/Thomas on the other thread.
>|||I guess this is the easiest solution:
SELECT userid as UserID, Age = datediff(dd,Birthdate,getdate())/365
FROM UserProfile
WHERE UserProfile.Birthdate IS NOT NULL AND
datediff(year,Birthdate,getdate())>=0
order by UserID
"chad" wrote:
> using the following query..
> SELECT userid as UserID, Age=datediff(year,Birthdate,getdate())
> FROM UserProfile
> WHERE UserProfile.Birthdate IS NOT NULL AND
> datediff(year,Birthdate,getdate())>=0
> order by UserID
> the converts the table
> userid birthdate
> 2 1985-03-08 00:00:00.000
> 9 1998-06-27 00:00:00.000
>
> to the output of:
> UserID Age
> 2 20
> 9 7
> The problem is it doesn't take into consideration for the current day,
> rather it looks only at the year. So the age of UserID 9 is actually 6.
> -Thanks HP/Thomas on the other thread.
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Here's a formula I use:
Year(getdate())
- Year(birthdate)
+ case when datepart(dy, birthdate) - datepart(dy, getdate()) < 0
then 0 else -1 end
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQlLtXoechKqOuFEgEQJ93gCgsWvUYstK/25OJhhdk7mUMSsdKqsAmwYF
rv4IxkY87JMffYj7v+i/Y31/
=AH6W
--END PGP SIGNATURE--
chad wrote:
> using the following query..
> SELECT userid as UserID, Age=datediff(year,Birthdate,getdate())
> FROM UserProfile
> WHERE UserProfile.Birthdate IS NOT NULL AND
> datediff(year,Birthdate,getdate())>=0
> order by UserID
> the converts the table
> userid birthdate
> 2 1985-03-08 00:00:00.000
> 9 1998-06-27 00:00:00.000
>
> to the output of:
> UserID Age
> 2 20
> 9 7
> The problem is it doesn't take into consideration for the current day,
> rather it looks only at the year. So the age of UserID 9 is actually 6.
> -Thanks HP/Thomas on the other thread.
>|||Yet another one:
datename(yy,getdate()-vt.birthday) - 1900
Sunday, February 19, 2012
Consuming secured webservice in SSRS
Hi All,
We have a webservice, which we are able to consume in the SSRS report.
Due to some requirement, we need to conver this webservice in to the secured webservice using the SSL https extension.
We did this and just changed the connection string from http to https.
But when we try to run the report, we get the following error message. Copied from BIDS.
TITLE: Microsoft Report Designer
An error occurred while executing the query.
Failed to prepare web request for the specified URL.
ADDITIONAL INFORMATION:
Failed to prepare web request for the specified URL. (Microsoft.ReportingServices.DataExtensions)
The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. (System)
The remote certificate is invalid according to the validation procedure. (System)
BUTTONS:
OK
Can anybody help in this.
Thanks in advance.
Virendra
Can anybody help to guide on the changes required for achieving this?
Virendra
|||Hi ,
Has anybody used the secured webservice successfull as data source in the SSRS?
Any pointers to the above problem?
Virendra
|||Hi All (looking at this post),
The problem is resolved. It was a trivial mistake done on my side. The "Windows Authentication" was enabled on the web service and the properties of the data source were set to No credentials in SSRS. After resolving this, it started working.
Virendra
Consuming secured webservice in SSRS
Hi All,
We have a webservice, which we are able to consume in the SSRS report.
Due to some requirement, we need to conver this webservice in to the secured webservice using the SSL https extension.
We did this and just changed the connection string from http to https.
But when we try to run the report, we get the following error message. Copied from BIDS.
TITLE: Microsoft Report Designer
An error occurred while executing the query.
Failed to prepare web request for the specified URL.
ADDITIONAL INFORMATION:
Failed to prepare web request for the specified URL. (Microsoft.ReportingServices.DataExtensions)
The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. (System)
The remote certificate is invalid according to the validation procedure. (System)
BUTTONS:
OK
Can anybody help in this.
Thanks in advance.
Virendra
Can anybody help to guide on the changes required for achieving this?
Virendra
|||Hi ,
Has anybody used the secured webservice successfull as data source in the SSRS?
Any pointers to the above problem?
Virendra
|||Hi All (looking at this post),
The problem is resolved. It was a trivial mistake done on my side. The "Windows Authentication" was enabled on the web service and the properties of the data source were set to No credentials in SSRS. After resolving this, it started working.
Virendra
Consuming secured webservice in SSRS
Hi All,
We have a webservice, which we are able to consume in the SSRS report.
Due to some requirement, we need to conver this webservice in to the secured webservice using the SSL https extension.
We did this and just changed the connection string from http to https.
But when we try to run the report, we get the following error message. Copied from BIDS.
TITLE: Microsoft Report Designer
An error occurred while executing the query.
Failed to prepare web request for the specified URL.
ADDITIONAL INFORMATION:
Failed to prepare web request for the specified URL. (Microsoft.ReportingServices.DataExtensions)
The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. (System)
The remote certificate is invalid according to the validation procedure. (System)
BUTTONS:
OK
Can anybody help in this.
Thanks in advance.
Virendra
Can anybody help to guide on the changes required for achieving this?
Virendra
|||Hi ,
Has anybody used the secured webservice successfull as data source in the SSRS?
Any pointers to the above problem?
Virendra
|||Hi All (looking at this post),
The problem is resolved. It was a trivial mistake done on my side. The "Windows Authentication" was enabled on the web service and the properties of the data source were set to No credentials in SSRS. After resolving this, it started working.
Virendra
Subscribe to:
Posts (Atom)