Help! SQLExpress2005 date formats

I installed SQLExpress2005 and restored some old MSSQL7 databases. This
seemed to work at first glance, but there is at least one problem. These
databases contained datetime values.
And we are in Germany where dates are written like this: 25.12.2005
(christmas, for example).
My ColdFusion application contains queries like this:
SELECT Datum, Kurztext, Feiertag, Brauchtumstag, Tagestyp, Freianteil,
Notiz
FROM tblBetriebskalender
WHEREdatum between '01.12.2005' and '31.12.2005'
ORDER BYDatum
this used to work without problems.
Now the same queries don't work because for a date such as '31.12.2005',
SQLServer thinks 12 is the day and 31 is the month.
What can I do? Is there a central switch somewhere that changes the data
formats? Unfortunately I don't know what it was set to in the old system.
(The database properties option tab shows that collation is now set to
SQL_Latin1_General_CP1_CI_AS, and the compatibility level is SQLServer 7.0.)
-Michael
hi Michael,
Michael Peters wrote:
> I installed SQLExpress2005 and restored some old MSSQL7 databases.
> This seemed to work at first glance, but there is at least one
> problem. These databases contained datetime values.
> And we are in Germany where dates are written like this: 25.12.2005
> (christmas, for example).
> My ColdFusion application contains queries like this:
> SELECT Datum, Kurztext, Feiertag, Brauchtumstag, Tagestyp, Freianteil,
> Notiz
> FROM tblBetriebskalender
> WHERE datum between '01.12.2005' and '31.12.2005'
> ORDER BY Datum
> this used to work without problems.
> Now the same queries don't work because for a date such as
> '31.12.2005', SQLServer thinks 12 is the day and 31 is the month.
> What can I do? Is there a central switch somewhere that changes the
> data formats? Unfortunately I don't know what it was set to in the
> old system.
> (The database properties option tab shows that collation is now set to
> SQL_Latin1_General_CP1_CI_AS, and the compatibility level is
> SQLServer 7.0.)
> -Michael
Dates should be handled in ISO format YYYY-MM-DD or YYYYMMDD in order to
avoid this kind of problem as the dateformat is language specific and each
login has na associated language default.. probably you had german setting
for your login on the old server and englis setting on the new one...
you can change the login's language
ALTER LOGIN login_name
DEFAULT_LANGUAGE = new_language
http://msdn2.microsoft.com/en-us/library/ms189828.aspx
or, at connection level, using the SET DATEFORMAT
http://msdn.microsoft.com/library/de...-set_052s.asp,
but I strongly urge you to modify your language management to standard
ISO/ODBC format..
an unvaluable article by Notre Dame SQL Server Kalen Delaney about "dates"
is available at
http://www.windowsitpro.com/SQLServe...147/9147.html, but
only for subscriber...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||thanks very much Andrea.
-Michael

Help! Parameter Conversion Unix Time

My dates are in unix time and the paramaters are entered as 7/6/2004. How can I convert the date in the parameter field back into unix time so it will retireve the records.
Unix time is the number of seconds from 1/1/1970 at 12:00am
ThanksFor a variable dateTime of type DateTime you can use the follwing
expression:
dateTime.Subtract(new DateTime(1970, 1, 1)).TotalSeconds
This returns floating point value and may contain fractions of a second.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:52981B6F-5FB5-4638-8959-8CFE298D5503@.microsoft.com...
> My dates are in unix time and the paramaters are entered as 7/6/2004. How
can I convert the date in the parameter field back into unix time so it will
retireve the records.
> Unix time is the number of seconds from 1/1/1970 at 12:00am
> Thanks

Help! Need to extract date/time from SQL database table

Please help! How do I extract mm/dd/yyyy hh:nn from a timestamp field located in an MS SQL 2000 database table?

I can do this in MS Access where , for example only, I can convert 56323938 to "07/24/2006 11:47"

But when i attemp to extract the same info from the same field in SQL Query Analyzer, I only get "07/24/2006 00:00".

Can someone help me ? Is there a stored procedure or sql string that I can use that will in fact return the "mm/dd/yyyy hh:nn" from a timetamp field (where the hh:nn is actual and not fillers like "00:00"?

:)Dear,
The ODBC and OLE DB timestamp data type records dates and times. The Transact-SQL timestamp data type is a binary data type with no time-related values.
May You get some idea from this.otherwise there should be no problem in retrieving time through (select <date_column> from <table>),i suppose.
Regards,
Lipsa|||Try

select convert( datetime, <timestamp_column> ) from <table>

Rgds

mjg|||I now recall how i got the mm/dd/yyyy hh:mm am/pm from a timestamp

I used the following string:

select convert(datetime,cast( [timestamp] as decimal)/1440 + 1,101) from table

(timestamp is the sql encoded stamp integer field stored on a table. By converting the [timestamp] field to decimal instead of "datetime" allows the hh:mm:ss to be extracted where the "/ 1440 + 1" extracts the date from the timestamp.

:)

Quote:

Originally Posted by MikeG

Try

select convert( datetime, <timestamp_column> ) from <table>

Rgds

mjg

Help! Got an error while do the replication update

Dear all,
I got the following error while doing the replication in updating the date,
I found that a column's datatype is NTEXT, but i have other table also are
have columns set to NTEXT datatype and it works.
Does anyone have any idea on it?
"Only text pointers are allowed in work tables, never text, ntext, or image
columns. The query processor produced a query plan that required a text,
ntext, or image column in a work table."
can you post your schema here for the problem table? Also do you recall what
update/insert/delete caused this problem?
Perhaps try to restart your agent and log according to:
http://support.microsoft.com/default...b;en-us;312292
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Madstan" <stanley.chong@.hk.mrspedag.com> wrote in message
news:uQdRmGKvEHA.1404@.TK2MSFTNGP11.phx.gbl...
> Dear all,
> I got the following error while doing the replication in updating the
date,
> I found that a column's datatype is NTEXT, but i have other table also are
> have columns set to NTEXT datatype and it works.
> Does anyone have any idea on it?
> "Only text pointers are allowed in work tables, never text, ntext, or
image
> columns. The query processor produced a query plan that required a text,
> ntext, or image column in a work table."
>

HELP! EM wont connect after apply sp3a,reporting services and VS.net 2003

Install SQL 2000 on a completelyu up to date windows 2003 advanced server,
then applied sp3a, then a installed reporting service developer addition,
then installed VS .net 2003 the reinstalled reporting services to get the
Report Developer.
Now I can't connect to the Server using EM.
Get SQL_alloc_handle on SQL_handle_env failed.
Tried all types of registrations.
Any suggestions short of starting from scratch?Make an ODBC trace of the connection attempt & open a case with the Webdata
team. They handle the MDAC problems with SQL.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Will do that if re-install of tools SQL fails. How do I open a case with the
WEBdata Team.
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:XWmAGmZ9DHA.704@.cpmsftngxa07.phx.gbl...
> Make an ODBC trace of the connection attempt & open a case with the
Webdata
> team. They handle the MDAC problems with SQL.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>

HELP! Default Date Parameters Expression in Reporting Services

How do I add a default date parameter to get this:

@.StartDate: Last Sunday

@.EndDate : Yesterday

So if I was to run this today I want the reports StartDate As Sunday, November 11, 2007

and

EndDate >>> Yesterday Thursday, November 15, 2007

Hi,

From your description, it seems that you want to add some parameters to filter the report, right?

If so, I suggest that you should associate a Query Parameter with a Report Parameter. Assume that you handle your querying works in a stored procedure, you can use getdate() method to get the current date, datediff() and dateadd() method to calculate the date. See the following code snippet (Just the idea, not the executable code.):

declare @.InputDay,@.StartDay,@.EndDay

-- @.EndDay=getdate()-1
case @.InputDay='Sun'
-- @.StartDay=getdate()-7
case @.InputDay='Mon'
-- @.StartDay=getdate()-1
case @.InputDay='Tue'
-- @.StartDay=getdate()-2
case @.InputDay='Wed'
-- @.StartDay=getdate()-3
case @.InputDay='Thu'
-- @.StartDay=getdate()-4
case @.InputDay='Fri'
-- @.StartDay=getdate()-5
case @.InputDay='Sat'
-- @.StartDay=getdate()-6

Thanks.

Help! Date formatting nightmare!

I converted an Access Database to SQL Express. The dates were converted to datetime

I'm using VWD 2005

Here is the source of my date and the query.

sqlDate = (DateTime.Now.AddDays(-7))

sqlTxt ="SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID WHERE (Service_Orders.SDate_Entered >= CONVERT(DATETIME, '" + sqlDate +"', 101)) ORDER BY Service_Orders.SDate_Entered DESC"

This retrurns 0 records.

sqlDate =11/28/2005 12:23:27 AM from the function above.

The querywill return records with :

sqlDate ="2005-11-01 21:56:20"

I tried changing the CONVERT(DATETIME, '" + sqlDate + "',1XX from 100 to 120 with no luck

I know this must be an easy fix, but it is beyond me.

I need to know how to

1. convert my date to the dateformat from "11/28/2005 12:23:27 AM" to "2005-11-01 21:56:20"

or

2. find out how to use the CONVERT(DATETIME, '" + sqlDate + "', 1XX properly

Thanks for any help in advance!

Bill

I always convert them after I recieved the data from the database so the way how you do it depends on where you handle them afterwards. Datagrid has it's own dataformatstring property and incode you can do it like this for an example:
lblFormat.Text = String.Format("{0:dd.MM.yyyy}", myData("myField"))
Find outmore
|||

OK here is the problem:

When I run my query, the query return 0 rows.

I narrowed the problem down to the fact that it doesn't like my date.

I construct the date via the function sqlDate = (DateTime.Now.AddDays(-7))

When I pass that date into my query 0 rows are returned. If I hard code the date with

sqlDate ="2005-11-01 21:56:20" I get all the rows for that date.

Here is my query

sqlTxt ="SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID WHERE (Service_Orders.SDate_Entered >= CONVERT(DATETIME, '" + sqlDate +"', 102)) ORDER BY Service_Orders.SDate_Entered DESC"

The field SDate_Entered is a datetime field.

Who would make a product where a simple query is such a problem??

Help! CUBE? ROLLUP? or COMPUTE BY?

Help! CUBE? ROLLUP? or COMPUTE BY?
Giving a table's content as below...
Date Area Product Amount
----
--
2005/07/23 CA Book 150
2005/07/29 NY Pen 70
2005/08/03 CA Pen 500
2005/08/04 CA Book 200
2005/08/05 NY Book 270
May i ask how to get the result as below?
Area Book Book Pen Pen
----
--
CA SUM(Book_CA_thisMonth) SUM(Book_CA_th
isyear) SUM(Pen_CA_thisMonth) SUM(Pe
n_CA_thisyear)
NY SUM(Book_NY_thisMonth) SUM(Book_NY_th
isyear) SUM(Pen_NY_thisMonth) SUM(Pe
n_NY_thisyear)
Should i use CUBE? ROLLUP? or COMPUTE BY? What's the correct SELECT syntax?Hi
COMPUTE/COMPUTE BY are available for backward compatibility and there should
not be used in new code. The information obtained by using ROLLUP would be:
CREATE TABLE myData([Date] datetime,Area char(2),Product char(4),Amount int)
INSERT INTO myData([Date],Area,Product,Amount)
SELECT '20050723','CA','Book', 150
UNION ALL SELECT '20050729','NY','Pen', 70
UNION ALL SELECT '20050803','CA','Pen', 500
UNION ALL SELECT '20050804','CA','Book', 200
UNION ALL SELECT '20050805','NY','Book', 270
CREATE VIEW MyMonthlyValues AS
SELECT YEAR([Date]) AS Year, MONTH([Date]) AS [Month], Area, Product, Amount
FROM MyData
SELECT CASE WHEN (GROUPING([Year]) = 1) THEN 'All' ELSE CAST([Year] AS
CHAR(5)) END AS [Year],
CASE WHEN (GROUPING([Month]) = 1) THEN 'All' ELSE CAST([Month] AS CHAR(4))
END AS [Month],
CASE WHEN (GROUPING([Area]) = 1) THEN 'All' ELSE [Area] END AS [Area],
CASE WHEN (GROUPING([Product]) = 1) THEN 'All' ELSE [Product] END AS
[Product],
SUM(Amount) AS AmountTotal
FROM MyMonthlyValues
GROUP BY [Year], [Month], Area, Product WITH ROLLUP
To restrict to the current month you could do
SELECT CASE WHEN (GROUPING([Year]) = 1) THEN 'All' ELSE CAST([Year] AS
CHAR(5)) END AS [Year],
CASE WHEN (GROUPING([Month]) = 1) THEN 'All' ELSE CAST([Month] AS CHAR(4))
END AS [Month],
CASE WHEN (GROUPING([Area]) = 1) THEN 'All' ELSE [Area] END AS [Area],
CASE WHEN (GROUPING([Product]) = 1) THEN 'All' ELSE [Product] END AS
[Product],
SUM(Amount) AS AmountTotal
FROM MyMonthlyValues
WHERE [Month] = MONTH(GETDATE()) AND [Year] = YEAR(GetDate())
GROUP BY [Year], [Month], Area, Product WITH ROLLUP
You can process the rows on the client if you don't the format you
specified. Alternatively it is possible to do:
CREATE VIEW MyMonthlyTotals AS
SELECT [Year], [Month], [Area], [Product], SUM(Amount) As MonthTotal
FROM MyMonthlyValues
GROUP BY [Year], [Month], [Area], [Product]
SELECT A.[Year], A.[Month], A.[Area], A.[Product], A.[MonthTotal], ( SELECT
SUM(M.[MonthTotal]) FROM MyMonthlyTotals M WHERE A.[Year] = M.[Year] AND
A.[Month] >= M.[Month] AND A.[Area] = M.[Area] AND A.[Product] = M.[Product]
) AS AnnualTotal
FROM MyMonthlyTotals A
WHERE A.[Month] = MONTH(GETDATE())
AND A.[Year] = YEAR(GetDate())
ORDER BY A.[Year], A.[Month], A.[Area], A.[Product]
You may need a Products, Areas and possibly a Calendar Table (see
http://www.aspfaq.com/show.asp?id=2519) to OUTER JOIN to, so that all
products, areas, months are displayed in case data is missing for certain
values.
e.g.
CREATE TABLE Products ( ProductId int NOT NULL IDENTITY(1,1), Product
char(4) )
INSERT INTO Products ( Product )
SELECT 'Book'
UNION ALL SELECT 'Pen'
CREATE TABLE Areas( AreaId int NOT NULL IDENTITY(1,1), Area char(2) )
INSERT INTO Areas ( Area )
SELECT 'CA'
UNION ALL SELECT 'NY'
John
"OKLover" wrote:
> Help! CUBE? ROLLUP? or COMPUTE BY?
>
> Giving a table's content as below...
> Date Area Product Amount
> ----
--
> 2005/07/23 CA Book 150
> 2005/07/29 NY Pen 70
> 2005/08/03 CA Pen 500
> 2005/08/04 CA Book 200
> 2005/08/05 NY Book 270
>
> May i ask how to get the result as below?
> Area Book Book Pen Pen
> ----
--
> CA SUM(Book_CA_thisMonth) SUM(Book_CA_th
isyear) SUM(Pen_CA_thisMonth) SUM(
Pen_CA_thisyear)
> NY SUM(Book_NY_thisMonth) SUM(Book_NY_th
isyear) SUM(Pen_NY_thisMonth) SUM(
Pen_NY_thisyear)
> Should i use CUBE? ROLLUP? or COMPUTE BY? What's the correct SELECT syntax?[/color
]|||What a MVP likes John Bell should to be respected. you do so much. :)
As your suggestion, i got 3 SELECT results as below:
Year Month Area Product AmountTotal
---
2005 7 CA Book 150
2005 7 CA All 150
2005 7 NY Pen 70
2005 7 NY All 70
2005 7 All All 220
2005 8 CA Book 200
2005 8 CA Pen 500
2005 8 CA All 700
2005 8 NY Book 270
2005 8 NY All 270
2005 8 All All 970
2005 All All All 1190
All All All All 1190
Year Month Area Product AmountTotal
---
2005 8 CA Book 200
2005 8 CA Pen 500
2005 8 CA All 700
2005 8 NY Book 270
2005 8 NY All 270
2005 8 All All 970
2005 All All All 970
All All All All 970
Year Month Area Product MonthT AnnualT
----
2005 8 CA Book 200 350
2005 8 CA Pen 500 500
2005 8 NY Book 270 270
Is it possible to get the results like this:
Book Pen
---
CA | 200 350 500 500
NY | 270 270 Null Null
Total with 4 columns and 2 rows excluding the Product and Area label.|||Hi
I would have hoped that you would have taken the suggestions further,
progressing further with the scripts and taken on some of the suggestions,
such as using a product, area and calendar table you can "fill in the gaps"
such as:
CREATE TABLE Calendar ( Month int, Year int, [Date] datetime )
DECLARE @.basedate datetime
SET @.basedate = '20050101'
INSERT Calendar ( Month, Year, [Date] )
SELECT
MONTH(DATEADD(m,i,@.basedate)),Year(DATEA
DD(m,i,@.basedate)),DATEADD(m,i,@.base
date)
FROM ( SELECT 1 AS i
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
UNION ALL SELECT 11
UNION ALL SELECT 12 ) A
SELECT R.[Area], P.[Product], A.[MonthTotal], ( SELECT
SUM(M.[MonthTotal]) FROM MyMonthlyTotals M WHERE C.[Year] = M.[Year] AND
C.[Month] >= M.[Month] AND R.[Area] = M.[Area] AND P.[Product] = M.[Product]
) AS AnnualTotal
FROM Calendar C
CROSS JOIN [Products] P
CROSS JOIN [Areas] R
LEFT JOIN MyMonthlyTotals A ON A.Product = P.Product AND C.[Month] =
A.[Month] AND C.[Year] = A.[Year] AND R.[Area] = A.[Area]
WHERE C.[Month] = 8
AND C.[Year] = 2005
ORDER BY R.[Area], P.[Product]
This will give
Area Product MonthTotal AnnualTotal
-- -- -- --
CA Book 200 350
CA Pen 500 500
NY Book 270 270
NY Pen NULL 70
In the previous post I said to get into your exact format it is best to do
it on the client, but it is possible using SQL, and there are many posts on
how to CROSSTAB or PIVOT your results such as http://tinyurl.com/7hfet where
if you had read the links such as
http://www.windowsitpro.com/SQLServ...5608/15608.html you
should have come up with:
SELECT [Area], SUM(CASE WHEN [Product] = 'Book' THEN MonthTotal ELSE 0 END)
AS [Book Month],
SUM(CASE WHEN [Product] = 'Book' THEN AnnualTotal ELSE 0 END) AS [Book Year],
SUM(CASE WHEN [Product] = 'Pen' THEN MonthTotal ELSE 0 END) AS [Pen Month],
SUM(CASE WHEN [Product] = 'Pen' THEN AnnualTotal ELSE 0 END) AS [Pen Year]
FROM ( SELECT R.[Area], P.[Product], A.[MonthTotal], ( SELECT
SUM(M.[MonthTotal]) FROM MyMonthlyTotals M WHERE C.[Year] = M.[Year] AND
C.[Month] >= M.[Month] AND R.[Area] = M.[Area] AND P.[Product] = M.[Product]
) AS AnnualTotal
FROM Calendar C
CROSS JOIN [Products] P
CROSS JOIN [Areas] R
LEFT JOIN MyMonthlyTotals A ON A.Product = P.Product AND C.[Month] =
A.[Month] AND C.[Year] = A.[Year] AND R.[Area] = A.[Area]
WHERE C.[Month] = 8
AND C.[Year] = 2005 ) D
GROUP BY [Area]
ORDER BY [Area]
John
"OKLover" wrote:

> What a MVP likes John Bell should to be respected. you do so much. :)
> As your suggestion, i got 3 SELECT results as below:
> Year Month Area Product AmountTotal
> ---
> 2005 7 CA Book 150
> 2005 7 CA All 150
> 2005 7 NY Pen 70
> 2005 7 NY All 70
> 2005 7 All All 220
> 2005 8 CA Book 200
> 2005 8 CA Pen 500
> 2005 8 CA All 700
> 2005 8 NY Book 270
> 2005 8 NY All 270
> 2005 8 All All 970
> 2005 All All All 1190
> All All All All 1190
>
>
> Year Month Area Product AmountTotal
> ---
> 2005 8 CA Book 200
> 2005 8 CA Pen 500
> 2005 8 CA All 700
> 2005 8 NY Book 270
> 2005 8 NY All 270
> 2005 8 All All 970
> 2005 All All All 970
> All All All All 970
>
> Year Month Area Product MonthT AnnualT
> ----
--
> 2005 8 CA Book 200 350
> 2005 8 CA Pen 500 500
> 2005 8 NY Book 270 270
>
> Is it possible to get the results like this:
> Book Pen
> ---
> CA | 200 350 500 500
> NY | 270 270 Null Null
>
> Total with 4 columns and 2 rows excluding the Product and Area label.|||THANK YOU VERY MUCH !!!

help!

date company amount
01/01 ABS 2000
10/01 KB 1500
02/03 ABS 4000
02/04 LB 3000
04/05 KB 3000
05/06 LB 7000

i need help to write a SQL statement which can output companies with a total amount over 5000 for the period 01/01 to 05/06.try this

select company, sum(amount)
from table
where (the conditions you like)
group by company
having sum(amount) > 5000|||Select
Company,
Sum(Amount) as CompanyTotal

From
Table

Where
(CompanyTotal > 5000)
AND
( Date > 01/01
and
Date < 05/06)

Group by
Company|||Please note that 'Having sum(amount) > x' is not equal to 'where amount > x'. Having is a post group function while where is a pre function.

Help woth stored procedure!

Hi,
Below is my stored procedure, for some reason it is not bringing back the
max contract start date although I have sopecified this. Any ideas would be
greatly appreciated. Will also include some data to show what I am trying
to do.
SELECT TOP 100 PERCENT dbo.tbl_referral_name.rn_id,
dbo.tbl_referral_name.rn_forename, dbo.tbl_referral_name.rn_surname,
dbo.tbl_referral_add.ra_add1,
dbo.tbl_referral_add.ra_add2,
dbo.tbl_referral_info.ri_closed, dbo.tbl_support.s_options,
dbo.tbl_officer.off_full_name,
MAX(dbo.tbl_support.s_contract_startdate) AS
Start_date, dbo.tbl_support_provider.sp_company
FROM dbo.tbl_referral_name INNER JOIN
dbo.tbl_referral_add ON dbo.tbl_referral_name.rn_id =
dbo.tbl_referral_add.ra_rn_id INNER JOIN
dbo.tbl_referral_info ON dbo.tbl_referral_add.ra_id =
dbo.tbl_referral_info.ri_ra_id INNER JOIN
dbo.tbl_support ON dbo.tbl_referral_info.ri_id =
dbo.tbl_support.s_ri_id INNER JOIN
dbo.tbl_officer ON dbo.tbl_referral_info.ri_off_id =
dbo.tbl_officer.off_id INNER JOIN
dbo.tbl_support_provider ON dbo.tbl_support.s_sp_id =
dbo.tbl_support_provider.sp_id
GROUP BY dbo.tbl_referral_info.ri_closed, dbo.tbl_support.s_options,
dbo.tbl_officer.off_full_name, dbo.tbl_support_provider.sp_company,
dbo.tbl_referral_add.ra_add2,
dbo.tbl_referral_add.ra_add1, dbo.tbl_referral_name.rn_surname,
dbo.tbl_referral_name.rn_forename,
dbo.tbl_referral_name.rn_id
ORDER BY dbo.tbl_referral_name.rn_surname
Damon Smith, 41 Seven Oaks, Caerau, 0, , Alan Jones, 10/03/2003,
Homeless Team
Damon Smith, 41 Seven Oaks, Caerau, 0, CA, Alan Jones, 06/10/2003, Tai
Troth
There are also other records included in the list but I am trying to get the
maximum contract start date for any where there are more than one instance
like the example above.
Appreciate the help
Thanks
DamonWhat does "not bringing back the max contract start date" mean? The
wrong date? No date? An error message? It's hard to help you unless you
post some code that will actually reproduce the problem and explain
what result you expect. See the following article which explains the
best way to post your problem for the group:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Hi,
Sorry about that. Basically when I include MAX on the contract_startdate
field it is having no effect on my results. What it should be doing is
bringing back the most recent contract_startdates if there is more than once
instance of a person and address but it is bringing back everything. i.e.
Damon Smith, 41 Seven Oaks, Caerau, 0, , Alan Jones, 10/03/2003,
Homeless Team
Damon Smith, 41 Seven Oaks, Caerau, 0, CA, Alan Jones, 06/10/2003, Tai
Troth - I want it to just bring this one back (most recent date) in all
cases where there is more than one instance of a person and address like
above.
Any ideas why this may be?
Damon
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108634350.485310.14970@.l41g2000cwc.googlegroups.com...
> What does "not bringing back the max contract start date" mean? The
> wrong date? No date? An error message? It's hard to help you unless you
> post some code that will actually reproduce the problem and explain
> what result you expect. See the following article which explains the
> best way to post your problem for the group:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>|||You need something like the following. PLease bear in mind I typed this
and have not tested it. You need to do a subselect in the where clause
of the main query to determine the ID and maximum start date then limit
the records in the main criteria based on the subselect.
You do not need a Group By clause in the main query. I have also
forgotten whether MS SQL Server allows multiple fields for subselects
as I have shown. If it does not then concatenate the two fields into
one. eg dbo.tbl_referral_name.rn_id +
dbo.tbl_support.s_contract_startdate
SELECT dbo.tbl_referral_name.rn_id,
dbo.tbl_referral_name.rn_forename,
dbo.tbl_referral_name.rn_surname,
dbo.tbl_referral_add.ra_add1,
dbo.tbl_referral_add.ra_add2,
dbo.tbl_referral_info.ri_closed,
dbo.tbl_support.s_options,
dbo.tbl_officer.off_full_name,
dbo.tbl_support.s_contract_startdate AS Start_date,
dbo.tbl_support_provider.sp_company
FROM dbo.tbl_referral_name INNER JOIN
dbo.tbl_referral_add ON dbo.tbl_referral_name.rn_id =
dbo.tbl_referral_add.ra_rn_id INNER JOIN
dbo.tbl_referral_info ON dbo.tbl_referral_add.ra_id =
dbo.tbl_referral_info.ri_ra_id INNER JOIN
dbo.tbl_support ON dbo.tbl_referral_info.ri_id =
dbo.tbl_support.s_ri_id INNER JOIN
dbo.tbl_officer ON dbo.tbl_referral_info.ri_off_id =
dbo.tbl_officer.off_id INNER JOIN
dbo.tbl_support_provider ON dbo.tbl_support.s_sp_id =
dbo.tbl_support_provider.sp_id
WHERE dbo.tbl_referral_name.rn_id, dbo.tbl_support.s_contract_startdate
in ( select dbo.tbl_referral_name.rn_id,
max(dbo.tbl_support.s_contract_startdate)
from dbo.tbl_referral_name INNER JOIN
dbo.tbl_referral_add ON dbo.tbl_referral_name.rn_id =
dbo.tbl_referral_add.ra_rn_id INNER JOIN
dbo.tbl_referral_info ON dbo.tbl_referral_add.ra_id =
dbo.tbl_referral_info.ri_ra_id INNER JOIN
dbo.tbl_support ON dbo.tbl_referral_info.ri_id =
dbo.tbl_support.s_ri_id
group by dbo.tbl_referral_name.rn_id)
ORDER BY dbo.tbl_referral_name.rn_surname
Celtic Kiwi

Help With Variable Containing Datetime

HI,

I HAVE A PROBLEM WITH A VARIABLE THAT I AM NOT BEEN ABLE TO SORT OUT.

DECLARE @.DATE NVARCHAR(100)
SET @.DATE = MONTH(GETDATE())
EXEC ('SELECT ' + @.DATE)

WHEN I RUN THIS, I HAVE NO PROBLEM AS IT GIVES ME THE ANSWER SAY 5 AS IT IS MAY.

BUT,

WHEN I RUN A VARIABLE CONTAINING DATETIME,

DECLARE @.DATE DATETIME
SET @.DATE = GETDATE()
EXEC ('SELECT ' + @.DATE)

IT GIVES ME AN ERROR :-

"Line 1: Incorrect syntax near '12'. "

IS THERE A WAY THAT I CAN USE DATETIME AS VARIABLE IN THIS CASE.Try this:
DECLARE @.DATE DATETIME

SET @.DATE = GETDATE()
EXEC ('SELECT ' + ''''+@.DATE+'''')

Harshal.|||Hi,

Thanks For Your Timely Help,the Problem Got Sorted Out In A Jiffy.|||And what about this approach:

declare @.date datetime
set @.date = getdate()
select @.date

Greetz,
DePrins
;)|||Hi,

Yes, I Know That Method ,but It Can't Be Used Many Times :- For E.g:- If I Want To Create Table_names Containing Month & Year Name Like Customer_data_for_20july2004

Then I Have To Use The Exec Command.|||Hi,

Yes, I Know That Method ,but It Can't Be Used Many Times :- For E.g:- If I Want To Create Table_names Containing Month & Year Name Like Customer_data_for_20july2004

Then I Have To Use The Exec Command.

Try this:
DECLARE @.DATE DATETIME,
@.Dt_Dsc Varchar(50),
@.SQL varchar(200)

SET @.DATE = GETDATE()
Set @.Dt_Ddc = Replace(Cast(Left(@.Date , 11) AS varchar(50)),' ','_')
Set @.SQL = 'Select ' + @.Dt_Dsc

Exec (@.SQL)

Gil|||In order to prevent you from tearing out your hair later, I'd like to strongly suggest that you format your dates differently and use them as a prefix rather than a suffix on your table names. If you format the prefix as E20040720 instead of 20_july2004, you won't have problems with 12_dec2000 sorting between 04_jul2004 and 20_may2010! If you use a prefix instead of a suffix, all of your extract tables will sort together by date of extract when you display table names sorted alphabetically. You can use:DECLARE @.prefix VARCHAR(10)
SET @.prefix = 'E' + Replace(Convert(VARCHAR(10), GetDate(), 120), '-', '')Note that I added a letter before the digits, just to make it easier to work with the tables going forward. Sometimes it gets messy trying to cope with table names that start with a digit.

-PatP|||Hi,

Thanks Pat And Glubstein That Was Wonderful Solved Many Of My Problems And Saved Many Headaches.

Thanks Once Again

Help with varchar to date

I want to convert a varchar(5) field to smalldatetime. I have a column that
hold credit card expiry dates in mm/yy format e.g 10/06
I want to update a new column with a smalldatetime value derived form the
column above. So I tried the following
CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
RETURNS smalldatetime
AS
BEGIN
--want to be sure it is interpreted as dd-mm-yy format
RETURN CONVERT(smalldatetime,
'01-' +
CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
WHEN 5 THEN 'May'
WHEN 6 THEN 'Jun'
WHEN 7 THEN 'Jul'
WHEN 8 THEN 'Aug'
WHEN 9 THEN 'Sep'
WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov'
WHEN 12 THEN 'Dec'
END
+ '-' + Right(@.DATETEXT, 2))
END
I then try to execute the following:
UPDATE Credit_Card
SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
WHERE LEN(Expiry_Date) = 5 --jic a bad field
And get the following error:
Syntax error converting character string to smalldatetime data type
The function does return correctly. Can anyone give me some help in getting
this working. ThanksCan you show a simplified example, e.g. your table structure, and 3 or 4
rows of sample data that cause the failure.
This is just one of the dozens of problems with choosing the wrong data
type. Another big one with your function specifically:
You're checking for left(@.datetext,2) but then saying WHEN 1 -- two problems
here, one is that 1 is not a string ('1' would be) and unless it is november
or december, I am sure that Left(@.DateText, 2) yields two characters (only
one of which is the month). How on earth do you distinguish between 11206
(jan 12 06) and 11206 (nov 2 06)? How about 1006 (nov 06) vs. 106 (jan 06)?
Again, some sample data that causes the problem would be useful. But more
importantly, before trying to debug a function, get a query running that
does what you want (but without the convert to smalldatetime). That makes
it much easier to debug and figure out which rows are not producing valid
dates.
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:qComg.3523$b6.86616@.nasal.pacific.net.au...
>I want to convert a varchar(5) field to smalldatetime. I have a column that
>hold credit card expiry dates in mm/yy format e.g 10/06
> I want to update a new column with a smalldatetime value derived form the
> column above. So I tried the following
> CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
> RETURNS smalldatetime
> AS
> BEGIN
> --want to be sure it is interpreted as dd-mm-yy format
> RETURN CONVERT(smalldatetime,
> '01-' +
> CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
> WHEN 1 THEN 'Jan'
> WHEN 2 THEN 'Feb'
> WHEN 3 THEN 'Mar'
> WHEN 4 THEN 'Apr'
> WHEN 5 THEN 'May'
> WHEN 6 THEN 'Jun'
> WHEN 7 THEN 'Jul'
> WHEN 8 THEN 'Aug'
> WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov'
> WHEN 12 THEN 'Dec'
> END
> + '-' + Right(@.DATETEXT, 2))
> END
> I then try to execute the following:
> UPDATE Credit_Card
> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
> WHERE LEN(Expiry_Date) = 5 --jic a bad field
> And get the following error:
> Syntax error converting character string to smalldatetime data type
> The function does return correctly. Can anyone give me some help in
> getting this working. Thanks
>|||Harry
> --want to be sure it is interpreted as dd-mm-yy format
Make sure that the format you are converting to is YYYYMMDD
CREATE TABLE Credit_Card (dt VARCHAR(5))
INSERT INTO Credit_Card SELECT '10/06'
SELECT * FROM Credit_Card
UPDATE Credit_Card SET dt= CAST(CONVERT(CHAR(6),GETDATE(),112)+'01'
AS
DATETIME)
It retruns just 'June'
How about to alter the table and change the datatype's column or expand it
to varchar(50) for instance
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:qComg.3523$b6.86616@.nasal.pacific.net.au...
>I want to convert a varchar(5) field to smalldatetime. I have a column that
>hold credit card expiry dates in mm/yy format e.g 10/06
> I want to update a new column with a smalldatetime value derived form the
> column above. So I tried the following
> CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
> RETURNS smalldatetime
> AS
> BEGIN
> --want to be sure it is interpreted as dd-mm-yy format
> RETURN CONVERT(smalldatetime,
> '01-' +
> CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
> WHEN 1 THEN 'Jan'
> WHEN 2 THEN 'Feb'
> WHEN 3 THEN 'Mar'
> WHEN 4 THEN 'Apr'
> WHEN 5 THEN 'May'
> WHEN 6 THEN 'Jun'
> WHEN 7 THEN 'Jul'
> WHEN 8 THEN 'Aug'
> WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov'
> WHEN 12 THEN 'Dec'
> END
> + '-' + Right(@.DATETEXT, 2))
> END
> I then try to execute the following:
> UPDATE Credit_Card
> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
> WHERE LEN(Expiry_Date) = 5 --jic a bad field
> And get the following error:
> Syntax error converting character string to smalldatetime data type
> The function does return correctly. Can anyone give me some help in
> getting this working. Thanks
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O9Um9QblGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Can you show a simplified example, e.g. your table structure, and 3 or 4
> rows of sample data that cause the failure.
> This is just one of the dozens of problems with choosing the wrong data
> type. Another big one with your function specifically:
> You're checking for left(@.datetext,2) but then saying WHEN 1 -- two
> problems
Have a look again--CONVERT(tinyint, Left(@.DATETEXT, 2)) returns 1 from
'01'
The function DOES work correctly and returns from a parameter of '06/06'
the result '2006-06-01 00:00:00'
TRY the function!

> here, one is that 1 is not a string ('1' would be) and unless it is
> november or december, I am sure that Left(@.DateText, 2) yields two
> characters (only one of which is the month). How on earth do you
> distinguish between 11206 (jan 12 06) and 11206 (nov 2 06)? How about
> 1006 (nov 06) vs. 106 (jan 06)?
READ again -- I am passing a string value with a mm/yy format e.g. '10/06'
I am prepending '01-' for the day in the function.
The reason I am converting the LEFT 2 characters to a tinyint for use in the
CASE statement
e.g. '01' becomes 1, '02' becomes 2 etc the reason being I want the string
in dd-MMM-yy format
so the convert function will not be between an Australian date
format and a US format.

> Again, some sample data that causes the problem would be useful. But more
> importantly, before trying to debug a function, get a query running that
> does what you want (but without the convert to smalldatetime). That makes
> it much easier to debug and figure out which rows are not producing valid
> dates.
AGAIN, the function works - READ what I have written. Further to this I have
given you sample data!
"I want to convert a varchar(5) field to smalldatetime. I have a column that
hold credit card expiry dates in mm/yy format e.g 10/06
I want to update a new column with a smalldatetime value derived from the
column above. So I tried the following"
PLEASE read and understand the question.

>
> "Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
> news:qComg.3523$b6.86616@.nasal.pacific.net.au...
>|||"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:qComg.3523$b6.86616@.nasal.pacific.net.au...
>I want to convert a varchar(5) field to smalldatetime. I have a column that
>hold credit card expiry dates in mm/yy format e.g 10/06
> I want to update a new column with a smalldatetime value derived form the
> column above. So I tried the following
> CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
> RETURNS smalldatetime
> AS
> BEGIN
> --want to be sure it is interpreted as dd-mm-yy format
> RETURN CONVERT(smalldatetime,
> '01-' +
> CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
> WHEN 1 THEN 'Jan'
> WHEN 2 THEN 'Feb'
> WHEN 3 THEN 'Mar'
> WHEN 4 THEN 'Apr'
> WHEN 5 THEN 'May'
> WHEN 6 THEN 'Jun'
> WHEN 7 THEN 'Jul'
> WHEN 8 THEN 'Aug'
> WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov'
> WHEN 12 THEN 'Dec'
> END
> + '-' + Right(@.DATETEXT, 2))
> END
> I then try to execute the following:
> UPDATE Credit_Card
> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
> WHERE LEN(Expiry_Date) = 5 --jic a bad field
> And get the following error:
> Syntax error converting character string to smalldatetime data type
> The function does return correctly. Can anyone give me some help in
> getting this working. Thanks
Perhaps I had better explain further:
I have a table with a number of columns, one of which is called
"Expiry_Date" - varchar(5) which stores string values in the format mm/yy
e.g '06/06' or '12/06' as we all see as the expiry date on a credit card. I
now need to know in advance if a crediy card is going to expire. The current
format makes that very difficult. So I am trying the following:
I have added another column called "CardExpiry" which is smalldatetime. I
want to update this column from values contained in the "Expiry_Date"
column. Obviously I have to convert the string value to a smalldatetime
value first. That is why I created the function above. Even though the
function returns a smalldatetime value, sql server (2000) still thinks the
output of the function is a character string.|||> I have added another column called "CardExpiry" which is smalldatetime. I
> want to update this column from values contained in the "Expiry_Date"
> column. Obviously I have to convert the string value to a smalldatetime
> value first. That is why I created the function above. Even though the
> function returns a smalldatetime value, sql server (2000) still thinks the
> output of the function is a character string.
CREATE TABLE Credit_Card (Expiry_Date VARCHAR(5),CardExpiry SMALLDATETIME)
INSERT INTO Credit_Card (Expiry_Date) SELECT '10/06'
INSERT INTO Credit_Card (Expiry_Date) SELECT '09/06'
INSERT INTO Credit_Card (Expiry_Date) SELECT '01/06'
INSERT INTO Credit_Card (Expiry_Date) SELECT '02/06'
SELECT * FROM Credit_Card
--Now we are going to update CardExpiry column
UPDATE Credit_Card SET CardExpiry= CAST('20'+RIGHT(Expiry_Date,2)+ LEFT
(Expiry_Date,2) +'01' AS SMALLDATETIME)
SELECT * FROM Credit_Card
DROP TABLE Credit_Card
"Still Love VB6" <harry@.nospam.com.au> wrote in message
news:1Jqmg.14185$ap3.3358@.news-server.bigpond.net.au...
> "Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
> news:qComg.3523$b6.86616@.nasal.pacific.net.au...
> Perhaps I had better explain further:
> I have a table with a number of columns, one of which is called
> "Expiry_Date" - varchar(5) which stores string values in the format mm/yy
> e.g '06/06' or '12/06' as we all see as the expiry date on a credit card.
> I now need to know in advance if a crediy card is going to expire. The
> current format makes that very difficult. So I am trying the following:
> I have added another column called "CardExpiry" which is smalldatetime. I
> want to update this column from values contained in the "Expiry_Date"
> column. Obviously I have to convert the string value to a smalldatetime
> value first. That is why I created the function above. Even though the
> function returns a smalldatetime value, sql server (2000) still thinks the
> output of the function is a character string.
>|||This alteration to your function shuold clear up the error you are receiving
.
It returns a alpha month, 4 digit year, and the first of the month. Bad and
non-confirming parameters will return NULL. You can then easily find the bad
data.
CREATE FUNCTION dbo.StringToDate
( @.DateText varchar(11) )
RETURNS datetime
AS
BEGIN
IF len( @.DateText ) < 5
RETURN NULL
SET @.DateText =
CASE left( @.DateText, 2 )
WHEN '01' THEN replace( @.DateText, '01/', '01/Jan/' )
WHEN '02' THEN replace( @.DateText, '02/', '01/Feb/' )
WHEN '03' THEN replace( @.DateText, '03/', '01/Mar/' )
WHEN '04' THEN replace( @.DateText, '04/', '01/Apr/' )
WHEN '05' THEN replace( @.DateText, '05/', '01/May/' )
WHEN '06' THEN replace( @.DateText, '06/', '01/Jun/' )
WHEN '07' THEN replace( @.DateText, '07/', '01/Jul/' )
WHEN '08' THEN replace( @.DateText, '08/', '01/Aug/' )
WHEN '09' THEN replace( @.DateText, '09/', '01/Sep/' )
WHEN '10' THEN replace( @.DateText, '10/', '01/Oct/' )
WHEN '11' THEN replace( @.DateText, '11/', '01/Nov/' )
WHEN '12' THEN replace( @.DateText, '12/', '01/Dec/' )
END
IF @.DateText LIKE '%/0%'
RETURN ( replace( @.DateText, '/0', '/200' ))
IF @.DateText LIKE '%/1%'
RETURN ( replace( @.DateText, '/1', '/201' ))
RETURN NULL
END
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message news:qComg.3523$b6.86616
@.nasal.pacific.net.au...
>I want to convert a varchar(5) field to smalldatetime. I have a column that
> hold credit card expiry dates in mm/yy format e.g 10/06
>
> I want to update a new column with a smalldatetime value derived form the
> column above. So I tried the following
>
> CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
> RETURNS smalldatetime
> AS
> BEGIN
> --want to be sure it is interpreted as dd-mm-yy format
> RETURN CONVERT(smalldatetime,
> '01-' +
> CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
> WHEN 1 THEN 'Jan'
> WHEN 2 THEN 'Feb'
> WHEN 3 THEN 'Mar'
> WHEN 4 THEN 'Apr'
> WHEN 5 THEN 'May'
> WHEN 6 THEN 'Jun'
> WHEN 7 THEN 'Jul'
> WHEN 8 THEN 'Aug'
> WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov'
> WHEN 12 THEN 'Dec'
> END
> + '-' + Right(@.DATETEXT, 2))
>
> END
>
> I then try to execute the following:
>
> UPDATE Credit_Card
> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
> WHERE LEN(Expiry_Date) = 5 --jic a bad field
>
> And get the following error:
>
> Syntax error converting character string to smalldatetime data type
>
> The function does return correctly. Can anyone give me some help in gettin
g
> this working. Thanks
>
>|||Apparently the function does NOT work -you are getting an error!
I'm now.
Does the function NOT work properly and you are asking for our help,
OR
Does the function work properly and you are posting here for -what was that
reason again?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Still Love VB6" <harry@.nospam.com.au> wrote in message
news:Svqmg.14176$ap3.2372@.news-server.bigpond.net.au...
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:O9Um9QblGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Have a look again--CONVERT(tinyint, Left(@.DATETEXT, 2)) returns 1 from
> '01'
> The function DOES work correctly and returns from a parameter of '06/06'
> the result '2006-06-01 00:00:00'
> TRY the function!
>
> READ again -- I am passing a string value with a mm/yy format e.g. '10/06'
> I am prepending '01-' for the day in the function.
> The reason I am converting the LEFT 2 characters to a tinyint for use in
> the CASE statement
> e.g. '01' becomes 1, '02' becomes 2 etc the reason being I want the string
> in dd-MMM-yy format
> so the convert function will not be between an Australian date
> format and a US format.
>
> AGAIN, the function works - READ what I have written. Further to this I
> have given you sample data!
> "I want to convert a varchar(5) field to smalldatetime. I have a column
> that
> hold credit card expiry dates in mm/yy format e.g 10/06
> I want to update a new column with a smalldatetime value derived from the
> column above. So I tried the following"
> PLEASE read and understand the question.
>
>|||Could it be that you have bad data in your table, for example an expiry date
of 13/06?
Chris
"Still Love VB6" wrote:

> "Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
> news:qComg.3523$b6.86616@.nasal.pacific.net.au...
> Perhaps I had better explain further:
> I have a table with a number of columns, one of which is called
> "Expiry_Date" - varchar(5) which stores string values in the format mm/yy
> e.g '06/06' or '12/06' as we all see as the expiry date on a credit card.
I
> now need to know in advance if a crediy card is going to expire. The curre
nt
> format makes that very difficult. So I am trying the following:
> I have added another column called "CardExpiry" which is smalldatetime. I
> want to update this column from values contained in the "Expiry_Date"
> column. Obviously I have to convert the string value to a smalldatetime
> value first. That is why I created the function above. Even though the
> function returns a smalldatetime value, sql server (2000) still thinks the
> output of the function is a character string.
>
>|||> Perhaps I had better explain further:
Yes, that would be a good start!

> Even though the function returns a smalldatetime value, sql server (2000)
> still thinks the output of the function is a character string.
No, that is not what is happening at all.
You have some "dates" in your table where they aren't really dates. I can
think of hundreds of examples, since you allow varchar(5) in there, there is
no easy way to make them conform to any date format, so your table is
probably full of crap. It may be one row that is causing your function to
fail; it may be all rows! Who knows?
Do you see, now, the importance of sample data!?

Help with user defined function

I have a UDF that takes my input and returns the next valid business day date. My valid date excludes weekends and holidays.

It works perfect except for one issue. It doesn't check to see if today's date is a holiday.

I pass a query to sql server like so " select dbo.getstartdate('01/ 10/2007',2)"

It then moves ahead two business days and returns that date.

Here is the current code. Hopefully someone can tell me how to do the holiday check on the current date.

I really don't want to rewrite the whole script .

Code------------------

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

--DROP FUNCTION GetStartDate

--declare function receiving two parameters -
--the date we start counting and the number of business days

CREATE FUNCTION GetStartDate (@.startdate datetime, @.days int)
RETURNS datetime
AS
BEGIN

--declare a counter to keep track of how many days are passing
declare @.counter int

/*
Check your business rules. If 4 business days means you
count starting tomorrow, set counter to 0. If you start
counting today, set counter to 1
*/
set @.counter = 1

--declare a variable to hold the ending date
declare @.enddate datetime

--set the end date to the start date. we'll be
-- incrementing it for each passing business day
set @.enddate = @.startdate

/*
Start your loop.
While your counter (which was set to 1), is less than
or equal to the number of business days increment your
end date
*/
WHILE @.counter <= @.days

BEGIN

--for each day, we'll add one to the end date
set @.enddate = DATEADD(dd, 1, @.enddate)

--If the day is between 2 and 6 (meaning it's a week
--day and the day is not in the holiday table, we'll
--increment the counter
IF (DATEPART(dw, @.enddate) between 2 and 6) AND
(@.enddate not in
(
select HolidayDate
from tFederalHoliday
where [HolidayYear] = datepart(yyyy,@.enddate)
)
)
BEGIN
set @.counter = @.counter + 1
END

--end the while loop
END

--return the end date
RETURN @.enddate

--end the function
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

----------------------------

You can have a table with a list of holidays (both your company declared holidays and public holidays) and have the query check against the table in your UDF. This gives you the flexibility of adding/removing new holidays each year. You can either put the holidays in a table or even hardcode them in the UDF, whichever works best for you.