im trying to use getdate to just return me the date rather than date time
declare @.todaysdate smalldatetime
select @.todaysdate= getdate()
im just after "13/07/2005"
cheers
markMark,
have a look at this:
select convert(varchar(8),getdate(),3)
Rgds,
Paul Ibison, SQL Server MVP|||Mark,
You really need to understand that SQL Server does not have a DATE or a TIME
datatype. It only has DATETIME or SMALLDATETIME. It either case it always
includes the time portion. Even if you declare a DATETIME and only specify
the date portion it will automatically add the time of midnight. The only
want to display just the date portion (without using a gui that formats it
for you) is to convert it into a string. In your case you are trying to
stuff it back into a smalldatetime datatype which will simply add the time
portion back on again. Change the datatype of the variable to varchar and
you will make life a lot easier.
--
Andrew J. Kelly SQL MVP
"mark" <mark@.remove.com> wrote in message
news:1121254148.64416.0@.despina.uk.clara.net...
> "mark" <mark@.remove.com> wrote in message
> news:1121253845.64331.0@.despina.uk.clara.net...
>> im trying to use getdate to just return me the date rather than date time
>> declare @.todaysdate smalldatetime
>> select @.todaysdate= getdate()
>> im just after "13/07/2005"
>> cheers
>> mark
> i fixed it with this crazy procedure, surely theres an easier way
> declare @.todaysdate smalldatetime
> select @.todaysdate= getdate()
> declare @.month varchar(10)
> select @.month =datepart(mm,@.todaysdate)
> declare @.day varchar(10)
> select @.day =datepart(dd,@.todaysdate)
> declare @.year varchar(10)
> select @.year =datepart(yyyy,@.todaysdate)
> select @.todaysdate = @.day +'/' + @.month + '/' + @.year
> cheers
> mark
>|||You should believe us that there IS NO WAY getting only the date from the
getdate() function, SQL Server has no idea about only a date, thats not now
as a datetime type, the only thing would be to insert something using the
convert function like CONVERT(varchar(10), Getdate(),120) or something like
that, instead of using that you can change to IDW 3 on SQL Server 2005 where
actually was a understanding of TIME OR DATE, but they changed it in further
development, but summarized, there is now way for doing that.
HTH, Jens Suessmeyer.
"mark" wrote:
> i might not have explained it well enough,
> im trying to put the currentdate into a column in a database on an insert
> using getdate()
> currently using getdate() and getting current date and time - which is not
> what i need, i only need to record the date not the time
>
>|||"Jens Süßmeyer" <JensSmeyer@.discussions.microsoft.com> wrote in message
news:9DC85C5C-2C28-46A1-B51A-D6176AB0C7B8@.microsoft.com...
> You should believe us that there IS NO WAY getting only the date from the
> getdate() function, SQL Server has no idea about only a date, thats not
now
> as a datetime type, the only thing would be to insert something using the
> convert function like CONVERT(varchar(10), Getdate(),120) or something
like
> that, instead of using that you can change to IDW 3 on SQL Server 2005
where
> actually was a understanding of TIME OR DATE, but they changed it in
further
> development, but summarized, there is now way for doing that.
> HTH, Jens Suessmeyer.
>
so you would recommend passing the date from an app to the stored procedure
instead ?
(might be easier)
cheers
mark|||Mark:
Even passing the date to a stored procedure will not work. The database
will STORE your date as a datetime type which means if you pass '13/07/2005'
it will store it as '13/07/2005 00:00:00.000'. You can use an app to only
display and edit the date, but the date will always store as a datetime type
(which will add a MIDNIGHT time). You can also use the convert function to
display your datetime as just a "date string" using CONVERT(VARCHAR(10),
GETDATE(), 103) but as you can see, this actually converts your date into a
string and is treated as a string from then on (sorting is string based
then). Now if you actually want it strip out the time element of GETDATE()
you can use CAST(CONVERT(VARCHAR(10), GETDATE(), 102) AS DATETIME) which
will give you today's date with a midnight time. This will match any where
statement where you just specify just a date e.g. DateField = '2005-07-13'
because this will be converted automatically to '2005-07-13 00:00:00.000'
The question is "Why do you care so much that the database ONLY store the
date?" After all, the database never stores '13/07/2005' in that exact
format anyway. It stores it as a floating value that is calculated from a
set point in time. If you store something in a datetime field, I can get it
out in any format I desire (see the table listing under the "CAST and
CONVERT" topic in BOL). Which is the way it should be to allow for
international usage. In the UK you can display it in UK style and in the US
you can display in the US style. Same date, just displayed differently.
Scott
"mark" <mark@.remove.com> wrote in message
news:1121266783.5639.0@.lotis.uk.clara.net...
> "Jens Süßmeyer" <JensSmeyer@.discussions.microsoft.com> wrote in message
> news:9DC85C5C-2C28-46A1-B51A-D6176AB0C7B8@.microsoft.com...
>> You should believe us that there IS NO WAY getting only the date from the
>> getdate() function, SQL Server has no idea about only a date, thats not
> now
>> as a datetime type, the only thing would be to insert something using the
>> convert function like CONVERT(varchar(10), Getdate(),120) or something
> like
>> that, instead of using that you can change to IDW 3 on SQL Server 2005
> where
>> actually was a understanding of TIME OR DATE, but they changed it in
> further
>> development, but summarized, there is now way for doing that.
>> HTH, Jens Suessmeyer.
> so you would recommend passing the date from an app to the stored
> procedure
> instead ?
> (might be easier)
>
> cheers
> mark
>
>sql
Showing posts with label declare. Show all posts
Showing posts with label declare. Show all posts
Wednesday, March 21, 2012
getdate()
Nothing I do changes the format of the date.
declare @.PubDate datetime
set @.PubDate = convert(varchar,getdate(),111)
SELECT @.PubDate,*
FROM OPENquery(MySQL, 'SELECT * FROM articles WHERE Weight = 3 AND
DateInserted >= DATE_SUB(@.PubDate,INTERVAL 15 DAY) ')
I need it to show the date like yyyy/mm/ddYou can't change the display if it is the variable is declared as a datetime
datatype with convert. Make it a varchar instead.
> declare @.PubDate VARCHAR(24)
> set @.PubDate = convert(varchar(24),getdate(),111)
Andrew J. Kelly SQL MVP
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:A05FC7F0-DADD-4678-B354-E7B2FA8E8A78@.microsoft.com...
> Nothing I do changes the format of the date.
> declare @.PubDate datetime
> set @.PubDate = convert(varchar,getdate(),111)
> SELECT @.PubDate,*
> FROM OPENquery(MySQL, 'SELECT * FROM articles WHERE Weight = 3 AND
> DateInserted >= DATE_SUB(@.PubDate,INTERVAL 15 DAY) ')
> I need it to show the date like yyyy/mm/dd
>|||Thank you. Your answer fixed the formatting issue, but my query doesn't
return any results when it should. It returns results if I hard code the dat
e
in y/m/d format in place of the @.PubDate in my query. I tried
convert(datetime, getdate(), 111), but that, did not solve my problem. Any
other sugestions?
"Andrew J. Kelly" wrote:
> You can't change the display if it is the variable is declared as a dateti
me
> datatype with convert. Make it a varchar instead.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:A05FC7F0-DADD-4678-B354-E7B2FA8E8A78@.microsoft.com...
>
>|||Well I have no idea what your function DATE_SUB() is doing but you should
have a look at these:
http://www.karaszi.com/SQLServer/info_datetime.asp
Guide to Datetimes
http://www.sqlservercentral.com/col...sqldatetime.asp
Datetimes
http://www.murach.com/books/sqls/article.htm
Datetime Searching
Andrew J. Kelly SQL MVP
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:6930C3C9-2AD7-4259-A7E9-2D4A575C169D@.microsoft.com...
> Thank you. Your answer fixed the formatting issue, but my query doesn't
> return any results when it should. It returns results if I hard code the
> date
> in y/m/d format in place of the @.PubDate in my query. I tried
> convert(datetime, getdate(), 111), but that, did not solve my problem.
> Any
> other sugestions?
> "Andrew J. Kelly" wrote:
>|||Curtis,
I'm surprised this doesn't throw an error, because @.PubDate cannot
be used within the OPENQUERY statement. In addition, since you
have declared @.PubDate as datetime, it does not have a format, and
when used where a string is expected, it will be converted using the
default string format.
You have two options, unless you've hidden some secret about
how @.PubDate is working in the query:
If you are using SQL Server 2005, you can do this:
EXECUTE(
N'SELECT ?, * FROM articles
WHERE Weight = 3 AND DateInserted >= DATE_SUB(?,INTERVAL 15 DAY)',
@.PubDate, @.PubDate) at MySQL
You may or may not have to declare @.PubDate as a string and pre-convert
it--I don't know what your DATE_SUB function expects.
Alternatively, you can create the entire openquery string dynamically:
DECLARE @.sql nvarchar(1000)
DECLARE @.PubDate datetime
SET @.sql = N'SELECT ''?'', * FROM articles
WHERE Weight = 3 AND DateInserted >= DATE_SUB(''?'',INTERVAL 15 DAY)'
SET @.sql = REPLACE(@.sql,'?',CONVERT(varchar,getdate(),111)
EXEC(@.sql)
Be absolutely certain that ? is replaced by something you constructed
yourself from a datetime. Do not let the user provide the substitution
string, or you risk SQL Injection from a maliciously-formed replacement
string.
Steve Kass
Drew University
Curtis wrote:
>Nothing I do changes the format of the date.
>declare @.PubDate datetime
>set @.PubDate = convert(varchar,getdate(),111)
> SELECT @.PubDate,*
>FROM OPENquery(MySQL, 'SELECT * FROM articles WHERE Weight = 3 AND
>DateInserted >= DATE_SUB(@.PubDate,INTERVAL 15 DAY) ')
>I need it to show the date like yyyy/mm/dd
>
>
declare @.PubDate datetime
set @.PubDate = convert(varchar,getdate(),111)
SELECT @.PubDate,*
FROM OPENquery(MySQL, 'SELECT * FROM articles WHERE Weight = 3 AND
DateInserted >= DATE_SUB(@.PubDate,INTERVAL 15 DAY) ')
I need it to show the date like yyyy/mm/ddYou can't change the display if it is the variable is declared as a datetime
datatype with convert. Make it a varchar instead.
> declare @.PubDate VARCHAR(24)
> set @.PubDate = convert(varchar(24),getdate(),111)
Andrew J. Kelly SQL MVP
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:A05FC7F0-DADD-4678-B354-E7B2FA8E8A78@.microsoft.com...
> Nothing I do changes the format of the date.
> declare @.PubDate datetime
> set @.PubDate = convert(varchar,getdate(),111)
> SELECT @.PubDate,*
> FROM OPENquery(MySQL, 'SELECT * FROM articles WHERE Weight = 3 AND
> DateInserted >= DATE_SUB(@.PubDate,INTERVAL 15 DAY) ')
> I need it to show the date like yyyy/mm/dd
>|||Thank you. Your answer fixed the formatting issue, but my query doesn't
return any results when it should. It returns results if I hard code the dat
e
in y/m/d format in place of the @.PubDate in my query. I tried
convert(datetime, getdate(), 111), but that, did not solve my problem. Any
other sugestions?
"Andrew J. Kelly" wrote:
> You can't change the display if it is the variable is declared as a dateti
me
> datatype with convert. Make it a varchar instead.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:A05FC7F0-DADD-4678-B354-E7B2FA8E8A78@.microsoft.com...
>
>|||Well I have no idea what your function DATE_SUB() is doing but you should
have a look at these:
http://www.karaszi.com/SQLServer/info_datetime.asp
Guide to Datetimes
http://www.sqlservercentral.com/col...sqldatetime.asp
Datetimes
http://www.murach.com/books/sqls/article.htm
Datetime Searching
Andrew J. Kelly SQL MVP
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:6930C3C9-2AD7-4259-A7E9-2D4A575C169D@.microsoft.com...
> Thank you. Your answer fixed the formatting issue, but my query doesn't
> return any results when it should. It returns results if I hard code the
> date
> in y/m/d format in place of the @.PubDate in my query. I tried
> convert(datetime, getdate(), 111), but that, did not solve my problem.
> Any
> other sugestions?
> "Andrew J. Kelly" wrote:
>|||Curtis,
I'm surprised this doesn't throw an error, because @.PubDate cannot
be used within the OPENQUERY statement. In addition, since you
have declared @.PubDate as datetime, it does not have a format, and
when used where a string is expected, it will be converted using the
default string format.
You have two options, unless you've hidden some secret about
how @.PubDate is working in the query:
If you are using SQL Server 2005, you can do this:
EXECUTE(
N'SELECT ?, * FROM articles
WHERE Weight = 3 AND DateInserted >= DATE_SUB(?,INTERVAL 15 DAY)',
@.PubDate, @.PubDate) at MySQL
You may or may not have to declare @.PubDate as a string and pre-convert
it--I don't know what your DATE_SUB function expects.
Alternatively, you can create the entire openquery string dynamically:
DECLARE @.sql nvarchar(1000)
DECLARE @.PubDate datetime
SET @.sql = N'SELECT ''?'', * FROM articles
WHERE Weight = 3 AND DateInserted >= DATE_SUB(''?'',INTERVAL 15 DAY)'
SET @.sql = REPLACE(@.sql,'?',CONVERT(varchar,getdate(),111)
EXEC(@.sql)
Be absolutely certain that ? is replaced by something you constructed
yourself from a datetime. Do not let the user provide the substitution
string, or you risk SQL Injection from a maliciously-formed replacement
string.
Steve Kass
Drew University
Curtis wrote:
>Nothing I do changes the format of the date.
>declare @.PubDate datetime
>set @.PubDate = convert(varchar,getdate(),111)
> SELECT @.PubDate,*
>FROM OPENquery(MySQL, 'SELECT * FROM articles WHERE Weight = 3 AND
>DateInserted >= DATE_SUB(@.PubDate,INTERVAL 15 DAY) ')
>I need it to show the date like yyyy/mm/dd
>
>
Getdate Function
How do I modify the statement listed below to give me the date with the time
of 12:00 AM.
declare @.Yester_day smalldatetime
set @.Yester_day = (select getdate()-1)
print @.Yester_day '
Output
2005-08-30 10:48:12.127Select DATEADD(hh,-12,CONVERT(varchar(50),getdate(),112))
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Joe K." wrote:
> How do I modify the statement listed below to give me the date with the ti
me
> of 12:00 AM.
>
> declare @.Yester_day smalldatetime
> set @.Yester_day = (select getdate()-1)
> print @.Yester_day '
> Output
> 2005-08-30 10:48:12.127
>
>|||DECLARE @.Yesterday SMALLDATETIME -- why the underbar?
SET @.Yesterday = DATEDIFF(DAY,1,GETDATE())
SELECT @.Yesterday
Or more elaborately:
DECLARE @.Yesterday SMALLDATETIME
SET @.Yesterday = DATEADD(DAY, -1, DATEDIFF(DAY, 0, GETDATE()))
SELECT @.Yesterday
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:7F1A26A7-E861-420C-B318-4F57A6C31425@.microsoft.com...
> How do I modify the statement listed below to give me the date with the
> time
> of 12:00 AM.
>
> declare @.Yester_day smalldatetime
> set @.Yester_day = (select getdate()-1)
> print @.Yester_day '
> Output
> 2005-08-30 10:48:12.127
>
>|||declare @.Yester_day smalldatetime
set @.Yester_day = (select cast (floor(cast (getdate()-1 as float))as
datetime))
print @.Yester_day
"Joe K." wrote:
> How do I modify the statement listed below to give me the date with the ti
me
> of 12:00 AM.
>
> declare @.Yester_day smalldatetime
> set @.Yester_day = (select getdate()-1)
> print @.Yester_day '
> Output
> 2005-08-30 10:48:12.127
>
>
of 12:00 AM.
declare @.Yester_day smalldatetime
set @.Yester_day = (select getdate()-1)
print @.Yester_day '
Output
2005-08-30 10:48:12.127Select DATEADD(hh,-12,CONVERT(varchar(50),getdate(),112))
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Joe K." wrote:
> How do I modify the statement listed below to give me the date with the ti
me
> of 12:00 AM.
>
> declare @.Yester_day smalldatetime
> set @.Yester_day = (select getdate()-1)
> print @.Yester_day '
> Output
> 2005-08-30 10:48:12.127
>
>|||DECLARE @.Yesterday SMALLDATETIME -- why the underbar?
SET @.Yesterday = DATEDIFF(DAY,1,GETDATE())
SELECT @.Yesterday
Or more elaborately:
DECLARE @.Yesterday SMALLDATETIME
SET @.Yesterday = DATEADD(DAY, -1, DATEDIFF(DAY, 0, GETDATE()))
SELECT @.Yesterday
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:7F1A26A7-E861-420C-B318-4F57A6C31425@.microsoft.com...
> How do I modify the statement listed below to give me the date with the
> time
> of 12:00 AM.
>
> declare @.Yester_day smalldatetime
> set @.Yester_day = (select getdate()-1)
> print @.Yester_day '
> Output
> 2005-08-30 10:48:12.127
>
>|||declare @.Yester_day smalldatetime
set @.Yester_day = (select cast (floor(cast (getdate()-1 as float))as
datetime))
print @.Yester_day
"Joe K." wrote:
> How do I modify the statement listed below to give me the date with the ti
me
> of 12:00 AM.
>
> declare @.Yester_day smalldatetime
> set @.Yester_day = (select getdate()-1)
> print @.Yester_day '
> Output
> 2005-08-30 10:48:12.127
>
>
Monday, March 19, 2012
Get XML root name in Sql Server
declare @.x xml
set @.x =
'<Chicago>
<Area>A1</Area>
<Group>5</Group>
<Question>Q1</Question>
</Chicago>'
How to get the XML root name 'Chicago' in Sql Server 2005?
Since you are using the XQuery or XPath query you should know the exact path of the xml. Otherwise you can't utilize this new feature in SQL Server...
There is no functions available in XQuery to fetch the XML Root element name, but you can utilize the Stringmanipulation to find what is your root..
Code Snippet
declare @.x xml
set @.x =
'<Chicago>
<Area>A1</Area>
<Group>5</Group>
<Question>Q1</Question>
</Chicago>'
Select
Substring(XML,Charindex('<', XML)+1, Charindex('>', XML) - Charindex('<', XML)-1)
From
(
Select
Cast(@.X as Varchar(max)) as XML
) as Data
Monday, March 12, 2012
get time in SQL server
Hi ,
i am trying to get the time as well after the
conversion to datetime data type but could not do it.
is it possible to do so ?
declare @.date1 as string
set date1 = '12/5/2004 23:59:59'
declare @.date2 as datetime
set @.date2 = convert(@.date1) but it always return '12-05-
2004 00:00:00:000' which i want the time to be 23:59:59
thks & rdgs
max
SELECT CONVERT(CHAR(10),GETDATE(),108)
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:04c201c493f3$38c447a0$a401280a@.phx.gbl...
> Hi ,
> i am trying to get the time as well after the
> conversion to datetime data type but could not do it.
> is it possible to do so ?
> declare @.date1 as string
> set date1 = '12/5/2004 23:59:59'
> declare @.date2 as datetime
> set @.date2 = convert(@.date1) but it always return '12-05-
> 2004 00:00:00:000' which i want the time to be 23:59:59
> thks & rdgs
|||On Mon, 6 Sep 2004 02:23:51 -0700, maxzsim wrote:
>Hi ,
> i am trying to get the time as well after the
>conversion to datetime data type but could not do it.
> is it possible to do so ?
>declare @.date1 as string
>set date1 = '12/5/2004 23:59:59'
>declare @.date2 as datetime
>set @.date2 = convert(@.date1) but it always return '12-05-
>2004 00:00:00:000' which i want the time to be 23:59:59
>thks & rdgs
Hi Maxzsim,
Is this is SQL Server question or an Access question? You posted in a SQL
Server group, but your statements has some syntax elements that raise
syntax errors on SQL Server (and that look familiar from an Access point
of view): "as" in a declare statement, datatype "string" and "convert"
with only one argument are all illegal in SQL Server.
When I fix the syntax for SQL Server, I get either one of the following.
All of them leave the time part unchanged (ie 23:59:59, as requested).
(1)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = cast (@.date1 as datetime)
select @.date1, @.date2
(2)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = convert (datetime, @.date1)
select @.date1, @.date2
(3)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = @.date1-- implicit conversion
select @.date1, @.date2
Last but not least: the format of your date/time constant is ambiguous. Is
the date part formatted as mm/dd/yyyy or dd/mm/yyyy? Both readings can be
valid. If you want to be sure that SQL Server recognises your date and
time as you intended them, use one of these formats:
* yyyymmdd (for date only; time part will be set to midnight)
* yyyy-mm-ddThh:mm:ss (date plus time; the uppercase T is a constant)
* yyyy-mm-ddThh:mm:ss.mmm (as above, but including milliseconds)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
i am trying to get the time as well after the
conversion to datetime data type but could not do it.
is it possible to do so ?
declare @.date1 as string
set date1 = '12/5/2004 23:59:59'
declare @.date2 as datetime
set @.date2 = convert(@.date1) but it always return '12-05-
2004 00:00:00:000' which i want the time to be 23:59:59
thks & rdgs
max
SELECT CONVERT(CHAR(10),GETDATE(),108)
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:04c201c493f3$38c447a0$a401280a@.phx.gbl...
> Hi ,
> i am trying to get the time as well after the
> conversion to datetime data type but could not do it.
> is it possible to do so ?
> declare @.date1 as string
> set date1 = '12/5/2004 23:59:59'
> declare @.date2 as datetime
> set @.date2 = convert(@.date1) but it always return '12-05-
> 2004 00:00:00:000' which i want the time to be 23:59:59
> thks & rdgs
|||On Mon, 6 Sep 2004 02:23:51 -0700, maxzsim wrote:
>Hi ,
> i am trying to get the time as well after the
>conversion to datetime data type but could not do it.
> is it possible to do so ?
>declare @.date1 as string
>set date1 = '12/5/2004 23:59:59'
>declare @.date2 as datetime
>set @.date2 = convert(@.date1) but it always return '12-05-
>2004 00:00:00:000' which i want the time to be 23:59:59
>thks & rdgs
Hi Maxzsim,
Is this is SQL Server question or an Access question? You posted in a SQL
Server group, but your statements has some syntax elements that raise
syntax errors on SQL Server (and that look familiar from an Access point
of view): "as" in a declare statement, datatype "string" and "convert"
with only one argument are all illegal in SQL Server.
When I fix the syntax for SQL Server, I get either one of the following.
All of them leave the time part unchanged (ie 23:59:59, as requested).
(1)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = cast (@.date1 as datetime)
select @.date1, @.date2
(2)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = convert (datetime, @.date1)
select @.date1, @.date2
(3)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = @.date1-- implicit conversion
select @.date1, @.date2
Last but not least: the format of your date/time constant is ambiguous. Is
the date part formatted as mm/dd/yyyy or dd/mm/yyyy? Both readings can be
valid. If you want to be sure that SQL Server recognises your date and
time as you intended them, use one of these formats:
* yyyymmdd (for date only; time part will be set to midnight)
* yyyy-mm-ddThh:mm:ss (date plus time; the uppercase T is a constant)
* yyyy-mm-ddThh:mm:ss.mmm (as above, but including milliseconds)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
get time in SQL server
Hi ,
i am trying to get the time as well after the
conversion to datetime data type but could not do it.
is it possible to do so ?
declare @.date1 as string
set date1 = '12/5/2004 23:59:59'
declare @.date2 as datetime
set @.date2 = convert(@.date1) but it always return '12-05-
2004 00:00:00:000' which i want the time to be 23:59:59
thks & rdgsmax
SELECT CONVERT(CHAR(10),GETDATE(),108)
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:04c201c493f3$38c447a0$a401280a@.phx.gbl...
> Hi ,
> i am trying to get the time as well after the
> conversion to datetime data type but could not do it.
> is it possible to do so ?
> declare @.date1 as string
> set date1 = '12/5/2004 23:59:59'
> declare @.date2 as datetime
> set @.date2 = convert(@.date1) but it always return '12-05-
> 2004 00:00:00:000' which i want the time to be 23:59:59
> thks & rdgs|||On Mon, 6 Sep 2004 02:23:51 -0700, maxzsim wrote:
>Hi ,
> i am trying to get the time as well after the
>conversion to datetime data type but could not do it.
> is it possible to do so ?
>declare @.date1 as string
>set date1 = '12/5/2004 23:59:59'
>declare @.date2 as datetime
>set @.date2 = convert(@.date1) but it always return '12-05-
>2004 00:00:00:000' which i want the time to be 23:59:59
>thks & rdgs
Hi Maxzsim,
Is this is SQL Server question or an Access question? You posted in a SQL
Server group, but your statements has some syntax elements that raise
syntax errors on SQL Server (and that look familiar from an Access point
of view): "as" in a declare statement, datatype "string" and "convert"
with only one argument are all illegal in SQL Server.
When I fix the syntax for SQL Server, I get either one of the following.
All of them leave the time part unchanged (ie 23:59:59, as requested).
(1)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = cast (@.date1 as datetime)
select @.date1, @.date2
(2)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = convert (datetime, @.date1)
select @.date1, @.date2
(3)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = @.date1 -- implicit conversion
select @.date1, @.date2
Last but not least: the format of your date/time constant is ambiguous. Is
the date part formatted as mm/dd/yyyy or dd/mm/yyyy? Both readings can be
valid. If you want to be sure that SQL Server recognises your date and
time as you intended them, use one of these formats:
* yyyymmdd (for date only; time part will be set to midnight)
* yyyy-mm-ddThh:mm:ss (date plus time; the uppercase T is a constant)
* yyyy-mm-ddThh:mm:ss.mmm (as above, but including milliseconds)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>--Original Message--
>Hi ,
> i am trying to get the time as well after the
>conversion to datetime data type but could not do it.
> is it possible to do so ?
>declare @.date1 as string
>set date1 = '12/5/2004 23:59:59'
>declare @.date2 as datetime
>set @.date2 = convert(@.date1) but it always return '12-05-
>2004 00:00:00:000' which i want the time to be 23:59:59
>thks & rdgs
>.
>
i am trying to get the time as well after the
conversion to datetime data type but could not do it.
is it possible to do so ?
declare @.date1 as string
set date1 = '12/5/2004 23:59:59'
declare @.date2 as datetime
set @.date2 = convert(@.date1) but it always return '12-05-
2004 00:00:00:000' which i want the time to be 23:59:59
thks & rdgsmax
SELECT CONVERT(CHAR(10),GETDATE(),108)
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:04c201c493f3$38c447a0$a401280a@.phx.gbl...
> Hi ,
> i am trying to get the time as well after the
> conversion to datetime data type but could not do it.
> is it possible to do so ?
> declare @.date1 as string
> set date1 = '12/5/2004 23:59:59'
> declare @.date2 as datetime
> set @.date2 = convert(@.date1) but it always return '12-05-
> 2004 00:00:00:000' which i want the time to be 23:59:59
> thks & rdgs|||On Mon, 6 Sep 2004 02:23:51 -0700, maxzsim wrote:
>Hi ,
> i am trying to get the time as well after the
>conversion to datetime data type but could not do it.
> is it possible to do so ?
>declare @.date1 as string
>set date1 = '12/5/2004 23:59:59'
>declare @.date2 as datetime
>set @.date2 = convert(@.date1) but it always return '12-05-
>2004 00:00:00:000' which i want the time to be 23:59:59
>thks & rdgs
Hi Maxzsim,
Is this is SQL Server question or an Access question? You posted in a SQL
Server group, but your statements has some syntax elements that raise
syntax errors on SQL Server (and that look familiar from an Access point
of view): "as" in a declare statement, datatype "string" and "convert"
with only one argument are all illegal in SQL Server.
When I fix the syntax for SQL Server, I get either one of the following.
All of them leave the time part unchanged (ie 23:59:59, as requested).
(1)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = cast (@.date1 as datetime)
select @.date1, @.date2
(2)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = convert (datetime, @.date1)
select @.date1, @.date2
(3)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = @.date1 -- implicit conversion
select @.date1, @.date2
Last but not least: the format of your date/time constant is ambiguous. Is
the date part formatted as mm/dd/yyyy or dd/mm/yyyy? Both readings can be
valid. If you want to be sure that SQL Server recognises your date and
time as you intended them, use one of these formats:
* yyyymmdd (for date only; time part will be set to midnight)
* yyyy-mm-ddThh:mm:ss (date plus time; the uppercase T is a constant)
* yyyy-mm-ddThh:mm:ss.mmm (as above, but including milliseconds)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>--Original Message--
>Hi ,
> i am trying to get the time as well after the
>conversion to datetime data type but could not do it.
> is it possible to do so ?
>declare @.date1 as string
>set date1 = '12/5/2004 23:59:59'
>declare @.date2 as datetime
>set @.date2 = convert(@.date1) but it always return '12-05-
>2004 00:00:00:000' which i want the time to be 23:59:59
>thks & rdgs
>.
>
Friday, March 9, 2012
get the number of rows exported using bcp
Hi,
I need to get the number of rows exported through bcp. Is there a
simple way to do that?
The current code is as follows:
DECLARE @.sql varchar(8000)
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t,'
EXEC master..xp_cmdshell @.sql
Help is greatly appreciated
Thanks
KRWhy not redirect bcp's output to another text file? There you will find
information yo need.
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, -o Drive:\path\row_count.txt'
or
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, >> Drive:\path\row_count.txt'
--
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik <kraman@.bastyr.edu> napisal w wiadomosci
news:1185830093.770576.123200@.e9g2000prf.googlegroups.com...
> Hi,
> I need to get the number of rows exported through bcp. Is there a
> simple way to do that?
> The current code is as follows:
> DECLARE @.sql varchar(8000)
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t,'
> EXEC master..xp_cmdshell @.sql
>
> Help is greatly appreciated
>
> Thanks
> KR
>|||I ended up doing it using the echo command the output the number of
rows. I used variables to hold the number of rows and then output it
to another text file using the cmd_shell.
Thanks
On Jul 31, 3:02 am, "Pawel Potasinski" <pawel.potasin...@.gmail.com>
wrote:
> Why not redirect bcp's output to another text file? There you will find
> information yo need.
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, -o Drive:\path\row_count.txt'
> or
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, >> Drive:\path\row_count.txt'
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
> Uzytkownik <kra...@.bastyr.edu> napisal w wiadomoscinews:1185830093.770576.123200@.e9g2000prf.googlegroups.com...
>
> > Hi,
> > I need to get the number of rows exported through bcp. Is there a
> > simple way to do that?
> > The current code is as follows:
> > DECLARE @.sql varchar(8000)
> > SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> > \output.csv -T -c -t,'
> > EXEC master..xp_cmdshell @.sql
> > Help is greatly appreciated
> > Thanks
> > KR- Hide quoted text -
> - Show quoted text -
I need to get the number of rows exported through bcp. Is there a
simple way to do that?
The current code is as follows:
DECLARE @.sql varchar(8000)
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t,'
EXEC master..xp_cmdshell @.sql
Help is greatly appreciated
Thanks
KRWhy not redirect bcp's output to another text file? There you will find
information yo need.
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, -o Drive:\path\row_count.txt'
or
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, >> Drive:\path\row_count.txt'
--
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik <kraman@.bastyr.edu> napisal w wiadomosci
news:1185830093.770576.123200@.e9g2000prf.googlegroups.com...
> Hi,
> I need to get the number of rows exported through bcp. Is there a
> simple way to do that?
> The current code is as follows:
> DECLARE @.sql varchar(8000)
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t,'
> EXEC master..xp_cmdshell @.sql
>
> Help is greatly appreciated
>
> Thanks
> KR
>|||I ended up doing it using the echo command the output the number of
rows. I used variables to hold the number of rows and then output it
to another text file using the cmd_shell.
Thanks
On Jul 31, 3:02 am, "Pawel Potasinski" <pawel.potasin...@.gmail.com>
wrote:
> Why not redirect bcp's output to another text file? There you will find
> information yo need.
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, -o Drive:\path\row_count.txt'
> or
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, >> Drive:\path\row_count.txt'
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
> Uzytkownik <kra...@.bastyr.edu> napisal w wiadomoscinews:1185830093.770576.123200@.e9g2000prf.googlegroups.com...
>
> > Hi,
> > I need to get the number of rows exported through bcp. Is there a
> > simple way to do that?
> > The current code is as follows:
> > DECLARE @.sql varchar(8000)
> > SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> > \output.csv -T -c -t,'
> > EXEC master..xp_cmdshell @.sql
> > Help is greatly appreciated
> > Thanks
> > KR- Hide quoted text -
> - Show quoted text -
Friday, February 24, 2012
Get Processor Id
Hi All
How Can I Convert This Code
Dim cimv2, PInfo, PItem ' no idea what to declare these as
Dim PubStrComputer As String
PubStrComputer = "."
Set cimv2 = GetObject("winmgmts:\\" & PubStrComputer & "\root\cimv2")
Set PInfo = cimv2.ExecQuery("Select * From Win32_Processor")
For Each PItem In PInfo
MsgBox ("Processor: " & PItem. Name & vbCrLf & "Id: " &
PItem.ProcessorId)
Next PItem
From Sql query Analyzer To Get Processor Id
ThanksHi
"Taha" wrote:
> Hi All
> How Can I Convert This Code
> Dim cimv2, PInfo, PItem ' no idea what to declare these as
> Dim PubStrComputer As String
> PubStrComputer = "."
> Set cimv2 = GetObject("winmgmts:\\" & PubStrComputer & "\root\cimv2")
> Set PInfo = cimv2.ExecQuery("Select * From Win32_Processor")
> For Each PItem In PInfo
> MsgBox ("Processor: " & PItem. Name & vbCrLf & "Id: " &
> PItem.ProcessorId)
> Next PItem
> From Sql query Analyzer To Get Processor Id
> Thanks
Check out http://www.sqldbatips.com/displaycode.asp?ID=6 and
http://www.sqlservercentral.com/columnists/aloera/sqlserverscriptingandwmi.asp on how to run WMI scripts.
John
How Can I Convert This Code
Dim cimv2, PInfo, PItem ' no idea what to declare these as
Dim PubStrComputer As String
PubStrComputer = "."
Set cimv2 = GetObject("winmgmts:\\" & PubStrComputer & "\root\cimv2")
Set PInfo = cimv2.ExecQuery("Select * From Win32_Processor")
For Each PItem In PInfo
MsgBox ("Processor: " & PItem. Name & vbCrLf & "Id: " &
PItem.ProcessorId)
Next PItem
From Sql query Analyzer To Get Processor Id
ThanksHi
"Taha" wrote:
> Hi All
> How Can I Convert This Code
> Dim cimv2, PInfo, PItem ' no idea what to declare these as
> Dim PubStrComputer As String
> PubStrComputer = "."
> Set cimv2 = GetObject("winmgmts:\\" & PubStrComputer & "\root\cimv2")
> Set PInfo = cimv2.ExecQuery("Select * From Win32_Processor")
> For Each PItem In PInfo
> MsgBox ("Processor: " & PItem. Name & vbCrLf & "Id: " &
> PItem.ProcessorId)
> Next PItem
> From Sql query Analyzer To Get Processor Id
> Thanks
Check out http://www.sqldbatips.com/displaycode.asp?ID=6 and
http://www.sqlservercentral.com/columnists/aloera/sqlserverscriptingandwmi.asp on how to run WMI scripts.
John
Get Processor Id
Hi All
How Can I Convert This Code
Dim cimv2, PInfo, PItem ' no idea what to declare these as
Dim PubStrComputer As String
PubStrComputer = "."
Set cimv2 = GetObject("winmgmts:\\" & PubStrComputer & "\root\cimv2")
Set PInfo = cimv2.ExecQuery("Select * From Win32_Processor")
For Each PItem In PInfo
MsgBox ("Processor: " & PItem. Name & vbCrLf & "Id: " &
PItem.ProcessorId)
Next PItem
From Sql query Analyzer To Get Processor Id
Thanks
Hi
"Taha" wrote:
> Hi All
> How Can I Convert This Code
> Dim cimv2, PInfo, PItem ' no idea what to declare these as
> Dim PubStrComputer As String
> PubStrComputer = "."
> Set cimv2 = GetObject("winmgmts:\\" & PubStrComputer & "\root\cimv2")
> Set PInfo = cimv2.ExecQuery("Select * From Win32_Processor")
> For Each PItem In PInfo
> MsgBox ("Processor: " & PItem. Name & vbCrLf & "Id: " &
> PItem.ProcessorId)
> Next PItem
> From Sql query Analyzer To Get Processor Id
> Thanks
Check out http://www.sqldbatips.com/displaycode.asp?ID=6 and
http://www.sqlservercentral.com/columnists/aloera/sqlserverscriptingandwmi.asp on how to run WMI scripts.
John
How Can I Convert This Code
Dim cimv2, PInfo, PItem ' no idea what to declare these as
Dim PubStrComputer As String
PubStrComputer = "."
Set cimv2 = GetObject("winmgmts:\\" & PubStrComputer & "\root\cimv2")
Set PInfo = cimv2.ExecQuery("Select * From Win32_Processor")
For Each PItem In PInfo
MsgBox ("Processor: " & PItem. Name & vbCrLf & "Id: " &
PItem.ProcessorId)
Next PItem
From Sql query Analyzer To Get Processor Id
Thanks
Hi
"Taha" wrote:
> Hi All
> How Can I Convert This Code
> Dim cimv2, PInfo, PItem ' no idea what to declare these as
> Dim PubStrComputer As String
> PubStrComputer = "."
> Set cimv2 = GetObject("winmgmts:\\" & PubStrComputer & "\root\cimv2")
> Set PInfo = cimv2.ExecQuery("Select * From Win32_Processor")
> For Each PItem In PInfo
> MsgBox ("Processor: " & PItem. Name & vbCrLf & "Id: " &
> PItem.ProcessorId)
> Next PItem
> From Sql query Analyzer To Get Processor Id
> Thanks
Check out http://www.sqldbatips.com/displaycode.asp?ID=6 and
http://www.sqlservercentral.com/columnists/aloera/sqlserverscriptingandwmi.asp on how to run WMI scripts.
John
Get Processor Id
Hi All
How Can I Convert This Code
Dim cimv2, PInfo, PItem ' no idea what to declare these as
Dim PubStrComputer As String
PubStrComputer = "."
Set cimv2 = GetObject("winmgmts:\\" & PubStrComputer & "\root\cimv2")
Set PInfo = cimv2.ExecQuery("Select * From Win32_Processor")
For Each PItem In PInfo
MsgBox ("Processor: " & PItem. Name & vbCrLf & "Id: " &
PItem.ProcessorId)
Next PItem
From Sql query Analyzer To Get Processor Id
ThanksHi
"Taha" wrote:
> Hi All
> How Can I Convert This Code
> Dim cimv2, PInfo, PItem ' no idea what to declare these as
> Dim PubStrComputer As String
> PubStrComputer = "."
> Set cimv2 = GetObject("winmgmts:\\" & PubStrComputer & "\root\cimv2")
> Set PInfo = cimv2.ExecQuery("Select * From Win32_Processor")
> For Each PItem In PInfo
> MsgBox ("Processor: " & PItem. Name & vbCrLf & "Id: " &
> PItem.ProcessorId)
> Next PItem
> From Sql query Analyzer To Get Processor Id
> Thanks
Check out http://www.sqldbatips.com/displaycode.asp?ID=6 and
[url]http://www.sqlservercentral.com/columnists/aloera/sqlserverscriptingandwmi.asp[/ur
l] on how to run WMI scripts.
John
How Can I Convert This Code
Dim cimv2, PInfo, PItem ' no idea what to declare these as
Dim PubStrComputer As String
PubStrComputer = "."
Set cimv2 = GetObject("winmgmts:\\" & PubStrComputer & "\root\cimv2")
Set PInfo = cimv2.ExecQuery("Select * From Win32_Processor")
For Each PItem In PInfo
MsgBox ("Processor: " & PItem. Name & vbCrLf & "Id: " &
PItem.ProcessorId)
Next PItem
From Sql query Analyzer To Get Processor Id
ThanksHi
"Taha" wrote:
> Hi All
> How Can I Convert This Code
> Dim cimv2, PInfo, PItem ' no idea what to declare these as
> Dim PubStrComputer As String
> PubStrComputer = "."
> Set cimv2 = GetObject("winmgmts:\\" & PubStrComputer & "\root\cimv2")
> Set PInfo = cimv2.ExecQuery("Select * From Win32_Processor")
> For Each PItem In PInfo
> MsgBox ("Processor: " & PItem. Name & vbCrLf & "Id: " &
> PItem.ProcessorId)
> Next PItem
> From Sql query Analyzer To Get Processor Id
> Thanks
Check out http://www.sqldbatips.com/displaycode.asp?ID=6 and
[url]http://www.sqlservercentral.com/columnists/aloera/sqlserverscriptingandwmi.asp[/ur
l] on how to run WMI scripts.
John
Subscribe to:
Posts (Atom)