Got a little problem... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Got a little problem...

Author

Topic

jbryandavis
Starting Member

12 Posts

Posted - 2008-09-22 : 12:35:23

Any help would be appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 12:43:30

may be this

DELETE t
FROM
(SELECT CONVERT(datetime,'01/'+CardExp) AS CardExp,CradNum,BillingAddress
FROM Table)t
WHERE t.CardExp<DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

jbryandavis
Starting Member

12 Posts

Posted - 2008-09-22 : 12:54:06

What does "t" stand for? The field?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 12:58:19

quote:
Originally posted by jbryandavis

What does "t" stand for? The field?

nope. its an alias for derived table.

jbryandavis
Starting Member

12 Posts

Posted - 2008-09-22 : 13:03:12

What is the problem with just storing the month and year?

Like this code shows:
SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 13:04:57

quote:
Originally posted by jbryandavis

What is the problem with just storing the month and year?

Like this code shows:
SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY]

Then date manipulations will be really difficult. comparison,sorting wont work as you expect as it treats dates as character data.

jbryandavis
Starting Member

12 Posts

Posted - 2008-09-22 : 13:12:19

Here is the error message I get:

Server: Msg 4421, Level 16, State 1, Line 1
Derived table 't' is not updatable because a column of the derived table is derived or constant.

jbryandavis
Starting Member

12 Posts

Posted - 2008-09-22 : 13:15:59

quote:
Originally posted by visakh16

quote:
Originally posted by jbryandavis

What is the problem with just storing the month and year?

Like this code shows:
SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY]

Then date manipulations will be really difficult. comparison,sorting wont work as you expect as it treats dates as character data.

I just want to make sure I can easily export out the MM/YY so when we create our .csv file it won't cause problems because the third-party software is expecting MM/YY and NOT DD/MM/YY

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 13:20:23

quote:
Originally posted by jbryandavis

Here is the error message I get:

Server: Msg 4421, Level 16, State 1, Line 1
Derived table 't' is not updatable because a column of the derived table is derived or constant.

what about this

DELETE FROM Table
WHERE CONVERT(datetime,'01/'+CardExp) < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

jbryandavis
Starting Member

12 Posts

Posted - 2008-09-22 : 13:27:07

quote:
Originally posted by visakh16

quote:
Originally posted by jbryandavis

Here is the error message I get:

Server: Msg 4421, Level 16, State 1, Line 1
Derived table 't' is not updatable because a column of the derived table is derived or constant.

what about this
DELETE FROM Table
WHERE CONVERT(datetime,'01/'+CardExp) < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

What about the issue of when we need to export from this table into a csv file and the expiration date needs to be MMYY?

jbryandavis
Starting Member

12 Posts

Posted - 2008-09-22 : 13:31:03

New Error:

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 13:32:10

quote:
Originally posted by jbryandavis

quote:
Originally posted by visakh16

quote:
Originally posted by jbryandavis

Here is the error message I get:

Server: Msg 4421, Level 16, State 1, Line 1
Derived table 't' is not updatable because a column of the derived table is derived or constant.

what about this
DELETE FROM Table
WHERE CONVERT(datetime,'01/'+CardExp) < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

What about the issue of when we need to export from this table into a csv file and the expiration date needs to be MMYY?

so you want only month and year? Then send the column value as its is to csv. do this convertion only for checking expired cards.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 13:33:52

quote:
Originally posted by jbryandavis

New Error:

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

try putting SET DATEFORMAT dmy on top of query.

SET DATEFORMAT dmy

DELETE FROM Table
WHERE CONVERT(datetime,'01/'+CardExp) < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

jbryandavis
Starting Member

12 Posts

Posted - 2008-09-22 : 13:35:12

quote:
Originally posted by visakh16

quote:
Originally posted by jbryandavis

quote:
Originally posted by visakh16

quote:
Originally posted by jbryandavis

Here is the error message I get:

Server: Msg 4421, Level 16, State 1, Line 1
Derived table 't' is not updatable because a column of the derived table is derived or constant.

what about this
DELETE FROM Table
WHERE CONVERT(datetime,'01/'+CardExp) < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

What about the issue of when we need to export from this table into a csv file and the expiration date needs to be MMYY?

so you want only month and year? Then send the column value as its is to csv. do this convertion only for checking expired cards.

Yes. I only want month and year, that's all credit card processing requires.

So, I guess basically all I need is a script that can convert the info to a date long enough to delete expired cards.

jbryandavis
Starting Member

12 Posts

Posted - 2008-09-22 : 13:38:08

New Error:

Server: Msg 241, Level 16, State 1, Line 3
Syntax error converting datetime from character string.

jbryandavis
Starting Member

12 Posts

Posted - 2008-09-22 : 13:40:15

Some of those errors could be caused by some dates.

For example:
09/10 is in the varchar field as 910

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 13:42:57