does sql have this...

Hey all,
does sql server have a date.parse function?
take 20050101
and
convert
1/1/2005
thanks,
rodcharRodchar,
Yes. See the CONVERT function with the optionaly style argument at:
CAST and CONVERT
http://msdn.microsoft.com/library/d...br />
2f3o.asp
Also may want to view the DATEPART function
http://msdn.microsoft.com/library/d...br />
2mic.asp
HTH
Jerry
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:24EC8B21-F17F-4992-BF25-D6632A46B98F@.microsoft.com...
> Hey all,
> does sql server have a date.parse function?
> take 20050101
> and
> convert
> 1/1/2005
> thanks,
> rodchar|||It's called CONVERT.
http://www.aspfaq.com/2464
Do you have a copy of Books Online?
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:24EC8B21-F17F-4992-BF25-D6632A46B98F@.microsoft.com...
> Hey all,
> does sql server have a date.parse function?
> take 20050101
> and
> convert
> 1/1/2005
> thanks,
> rodchar|||Having some trouble...
select convert(datetime,20050101,101)
do i need to use a combinatinon of Cast and Convert or something?
"Jerry Spivey" wrote:

> Rodchar,
> Yes. See the CONVERT function with the optionaly style argument at:
> CAST and CONVERT
> http://msdn.microsoft.com/library/d... />
o_2f3o.asp
> Also may want to view the DATEPART function
> http://msdn.microsoft.com/library/d... />
b_2mic.asp
> HTH
> Jerry
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:24EC8B21-F17F-4992-BF25-D6632A46B98F@.microsoft.com...
>
>|||> select convert(datetime,20050101,101)
You are trying to select an INT. A date is treated like a string in SQL
Server.
select convert(datetime, '20050101')
And using the 101 style parameter makes no sense here, unless you are going
to convert to a string in m/d/y format after you convert to datetime.
Do you have a copy of Books Online?
A|||Rodchar,
Enclose the date in single quotes and see the prior CONVERT link for the
date style.
HTH
Jerry
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:5A716BEE-2F33-4398-86B9-98C01E6B3A39@.microsoft.com...
> Having some trouble...
> select convert(datetime,20050101,101)
> do i need to use a combinatinon of Cast and Convert or something?
> "Jerry Spivey" wrote:
>|||select convert(varchar(10), '20050101', 101)
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:5A716BEE-2F33-4398-86B9-98C01E6B3A39@.microsoft.com...
> Having some trouble...
> select convert(datetime,20050101,101)
> do i need to use a combinatinon of Cast and Convert or something?
> "Jerry Spivey" wrote:
>|||> select convert(varchar(10), '20050101', 101)
when i try this, i still get 20050101 instead of 1/1/2005
"Raymond D'Anjou" wrote:

> select convert(varchar(10), '20050101', 101)
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:5A716BEE-2F33-4398-86B9-98C01E6B3A39@.microsoft.com...
>
>|||Hi,
Your date is already in char format, so the conversion makes no sense.
What are you trying to do?
If you have a datetime value you can use convert to display it as you wish.
Try:
select convert(varchar(10), GetDate(), 101)
HTH
Dan Artuso
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:EF7DE317-07ED-474A-83CE-E8D9F86FFEAB@.microsoft.com...
> when i try this, i still get 20050101 instead of 1/1/2005
> "Raymond D'Anjou" wrote:
>|||>> select convert(varchar(10), '20050101', 101)
> when i try this, i still get 20050101 instead of 1/1/2005
EGADS. Have you read anything in this thread? Do you have a copy of Books
Online?
SELECT CONVERT(CHAR(10), CONVERT(SMALLDATETIME, '20050101'), 101)
In any case, this is a really stupid format to present to users, IMHO. Do
any British people use your application? How about Canadians? To them, do
you think 7/6/2005 is July 6th or June 7th? Do you think they would agree
with you?
We have standards for a reason. Please consider using them.sql

Does SET DATEFORMAT only set the format for the session?

Hello
Can anyone confirm that if I use SET DATEFORMAT that it
will only change the date format for the SQL session and
not permanently for the machine?
Thanks in advance
H
Hi -
The Transact-SQL programming language provides several SET statements that
alter the current session handling of specific information.
SET DATEFORMAT is one such SET Function which will chage the date format for
the SQL Session.
Thanks
-Surajit
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:19fbb01c44da7$b42e8000$a101280a@.phx.gbl...
> Hello
> Can anyone confirm that if I use SET DATEFORMAT that it
> will only change the date format for the SQL session and
> not permanently for the machine?
> Thanks in advance
> H
|||Yes, Only for that specific session( not for everyone). And it applies ONLY
until that session ends.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:19fbb01c44da7$b42e8000$a101280a@.phx.gbl...
> Hello
> Can anyone confirm that if I use SET DATEFORMAT that it
> will only change the date format for the SQL session and
> not permanently for the machine?
> Thanks in advance
> H

Does SET DATEFORMAT only set the format for the session?

Hello
Can anyone confirm that if I use SET DATEFORMAT that it
will only change the date format for the SQL session and
not permanently for the machine?
Thanks in advance
HHi -
The Transact-SQL programming language provides several SET statements that
alter the current session handling of specific information.
SET DATEFORMAT is one such SET Function which will chage the date format for
the SQL Session.
Thanks
-Surajit
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:19fbb01c44da7$b42e8000$a101280a@.phx
.gbl...
> Hello
> Can anyone confirm that if I use SET DATEFORMAT that it
> will only change the date format for the SQL session and
> not permanently for the machine?
> Thanks in advance
> H|||Yes, Only for that specific session( not for everyone). And it applies ONLY
until that session ends.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:19fbb01c44da7$b42e8000$a101280a@.phx
.gbl...
> Hello
> Can anyone confirm that if I use SET DATEFORMAT that it
> will only change the date format for the SQL session and
> not permanently for the machine?
> Thanks in advance
> H

Does SET DATEFORMAT only set the format for the session?

Hello
Can anyone confirm that if I use SET DATEFORMAT that it
will only change the date format for the SQL session and
not permanently for the machine?
Thanks in advance
HHi -
The Transact-SQL programming language provides several SET statements that
alter the current session handling of specific information.
SET DATEFORMAT is one such SET Function which will chage the date format for
the SQL Session.
Thanks
-Surajit
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:19fbb01c44da7$b42e8000$a101280a@.phx.gbl...
> Hello
> Can anyone confirm that if I use SET DATEFORMAT that it
> will only change the date format for the SQL session and
> not permanently for the machine?
> Thanks in advance
> H|||Yes, Only for that specific session( not for everyone). And it applies ONLY
until that session ends.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:19fbb01c44da7$b42e8000$a101280a@.phx.gbl...
> Hello
> Can anyone confirm that if I use SET DATEFORMAT that it
> will only change the date format for the SQL session and
> not permanently for the machine?
> Thanks in advance
> H

Does anyone know where I can connect to a test SQL box?

I recently lost my job and wanted to do some test development to keep my skills up to date. The problem is I don't have access to any data sources. Is there such a thing around? I tried installing microsoft's trial of SQL 2005 but can't get it to run on my laptop and SQL 2000 trial no longer exists. I simply need to create a SQL db/tables etc. Thanks in advance.

You probably want SQL 2005 Express.

Jeff

|||

jeff@.zina.com:

You probably want SQL 2005 Express.

Jeff

Thanks. I'll take a look at it.|||

wescosoft:

I recently lost my job and wanted to do some test development to keep my skills up to date. The problem is I don't have access to any data sources. Is there such a thing around? I tried installing microsoft's trial of SQL 2005 but can't get it to run on my laptop and SQL 2000 trial no longer exists. I simply need to create a SQL db/tables etc. Thanks in advance.

An employer will not give you Express for development so it is better to download the eval version which is good for 180 days to use for your development. The product almost self install after you choose components Hope this helps.

http://www.microsoft.com/sql/downloads/trial-software.mspx

|||Odd, my employer gave me Express for development. But we also have production, test, and staging sql servers as well.|||

Motley:

Odd, my employer gave me Express for development. But we also have production, test, and staging sql servers as well.

Most employers give personnal edition which is complete SQL Server for development. Express installs with VS, that does not mean employers give Express for development.

|||

SQL Personal Edition, SQL Desktop, and MSDE are what SQL Express is in 2005. And I've found that employers don't normally give anything, development teams and shops have different methods of development. Most that I deal with use development servers, which are full SQL Server editions that match the deployment servers.

There are limitations to SQL Express, but most shouldn't impede using it for web development.

Jeff

|||

jeff@.zina.com:

SQL Personal Edition, SQL Desktop, and MSDE are what SQL Express is in 2005. And I've found that employers don't normally give anything, development teams and shops have different methods of development. Most that I deal with use development servers, which are full SQL Server editions that match the deployment servers.

There are limitations to SQL Express, but most shouldn't impede using it for web development.

Jeff

Personnal is not MSDE so it is not Express, MSDE is Express and it is not a way to learn data driven web application development because a lot of what you need to run your application are SQL Server Agent dependent and there is no Agent in Express.

|||There is no 2005 Personal Edition, atleast not that I am aware of. Enterprise, Standard, Workgroup, Express, Developer, and I believe there is a Mobile Edition as well. You may be thinking of the Developer Edition which is similiar in fuctionality to Enterprise with some licensing restrictions.|||I did not say there is personnal in SQL Server 2005 but Express is not a replacement for personnal because it was a complete SQL Server. That is the reason I gave the user the eval version.

does anyone know how to open access by todays date using sql

Hi
Can anyone point me in the right direction here, I would like to open
a table in access 2003 by date. I have an asp web page which needs to
read data from a table with each days today's date (which ever day
that is) then a new table is created with today's date.
Example:
I have a table called 17-may-2007 my ASP page reads this table for
24hours then tomorrow (12:00 midnight 18th) I will have a new table
called 18-may-2007 and the old table is left behind (As 17-may-2007)
so I need my sql statement to automatically open table by today's
date.
This is what I have so far but it does not work. Any ideas
'--Start of code--
Call BarGraphSQLData( _
"SELECT id, DataReading FROM DAY(CURRENT_TIMESTAMP) ORDER BY id" ,
_
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Server.MapPath("db/data.mdb") & ";" , _
'--End of code--
Thanks
Trevor
You might get better responses if you posted this in an Access group.
Andrew J. Kelly SQL MVP
"Trev" <trevor-dustan@.lycos.co.uk> wrote in message
news:1179408317.732724.182460@.q75g2000hsh.googlegr oups.com...
> Hi
> Can anyone point me in the right direction here, I would like to open
> a table in access 2003 by date. I have an asp web page which needs to
> read data from a table with each days today's date (which ever day
> that is) then a new table is created with today's date.
> Example:
> I have a table called 17-may-2007 my ASP page reads this table for
> 24hours then tomorrow (12:00 midnight 18th) I will have a new table
> called 18-may-2007 and the old table is left behind (As 17-may-2007)
> so I need my sql statement to automatically open table by today's
> date.
>
> This is what I have so far but it does not work. Any ideas
> '--Start of code--
> Call BarGraphSQLData( _
> "SELECT id, DataReading FROM DAY(CURRENT_TIMESTAMP) ORDER BY id" ,
> _
> "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
> Server.MapPath("db/data.mdb") & ";" , _
> '--End of code--
>
> Thanks
>
> Trevor
>

Document Map format

Is is possible to format the text of a field used in Document Map. I'm using
a date field and can format the field on the report, but not in the Document
Map. I couldn't find any expression function that can format a data value.Try the Format function: =Format(Fields!MyDate.Value,"d")
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Elmer Miller" <millere@.empireco.nospam> wrote in message
news:ewP5tzcZEHA.3228@.TK2MSFTNGP12.phx.gbl...
> Is is possible to format the text of a field used in Document Map. I'm
using
> a date field and can format the field on the report, but not in the
Document
> Map. I couldn't find any expression function that can format a data value.
>

Do While Skip in Selecting...

Hello:

I have one table and it contains a column named ID Number, and a column named Date. I have a Do While statement that runs a SQL select statement a few times based on the number of records with the same ID Number. During the Do While statement the information is copied into another table and deleted from the old table. After I look at the results, I see that at the second Do While loop, the data was not selected and the Select statement did not run... so the old variable value from varValue is used again... Any reasons on why?

Here is a code snippet of what is going on:

 Do While varCount < varRecordCount
 conSqlConnect.Open()
 cmdSelect = New SqlCommand ("Select * From temp_records_1 where [id number]=@.idnumber and date<@.date", conSqlConnect)
 cmdSelect.Parameters.Add( "@.accountnumber", "10000" )
 cmdSelect.Parameters.Add( "@.date", dtnow )
 dtrdatareader = cmdSelect.ExecuteReader()
 While dtrdatareader.Read()
 If IsDbNull(dtrdatareader("value")) = false Then
 varValue = dtrdatareader("value")
 End If
 End While
 dtrdatareader.Close()
 conSqlConnect.Close()
'#####The information above is copied to another table here
'#####The record where the information was received is deleted.
 varCount = varCount + 1
 Loop

Any ideas?After playing around with this for a while, I found that the select statement is incorrect. The part where it says date<@.date... this selects more than one row, instead of just selecting one row.

I read articles that discuss using select MAX(column) but that will only return one column... how can I select a row based on a column with the maximum value?|||ok... great got it working now... just post the answer here for future reference... I went ahead and did Select Top 1 * instead of Select *... it solved everything...