DATE - 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

Transact-SQL (2000)

DATE

Author

Topic

MarioK
Starting Member

20 Posts

Posted - 2005-01-31 : 14:49:34

Hi all
below is my SQL code that i set datelastpaid data types:datetime
is that right? is anyone have any better data type can i use?

USE CALLDATA
GO
SELECT datelastpaid, ssn
FROM debtors
WHERE ssn is not NULL AND datelastpaid BETWEEN '9/1/2003' AND '9/9/2003'
ORDER BY datelastpaid asc

result: how can i delete all the 00:00:00.0000?
datelastpaid ssn
2003-09-02 00:00:00.000 549-48-8559
2003-09-09 00:00:00.000 605-15-3721

(2 row(s) affected)

Thanks all

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-31 : 15:04:10

SELECT datelastpaid = convert(varchar(8),datelastpaid,112), ssn

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

MarioK
Starting Member

20 Posts

Posted - 2005-01-31 : 15:32:26

could you please explain how's this code work? i don't understand.
SELECT datelastpaid = convert(varchar(8),datelastpaid,112), ssn

another Question: original datelastpaid datatype is : char
i have changed to :datetime .....it works fine
but now i changed to :varchar
then noe i want to changed back to datetime to test the code you gave me
then i got the error

'Debtors' table
- Warning: Data may be lost converting column 'DateLastPaid' from 'varchar(50)'.
'Debtors' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

please show me how to changed the datatypes with out all error?

thanks Most Valuable Programmer MVP A+

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-01 : 10:09:30

Mario... BOL man, BOL.
That means "Books On Line" (from Query Analyzer: F1 or Shift+F1)
Start by looking at the "Cast and Convert" topic (lookup convert from the index tab)

If you read through BOL you too could someday be a SQL Server MVP

- Warning: Data may be lost converting column 'DateLastPaid' from 'varchar(50)'.
You'll get this anytime you change a column definition that COULD result in loss of data because of a loss of precision. TinyInt to Int will not give you that warning, Int to tinyInt will.
Look for values that won't convert to datetime by something like this:
Select * from Debtors where isDate(DateLastPaid) = 0

- The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
This means you have at least 1 value in your varchar(50) column that cannot be converted to datetime. Find the values that won't convert with:
Select * from Debtors where isDate(DateLastPaid) = 0

Be One with the Optimizer
TG
edited: You'd think I would know how to spell "Analyzer" by now

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-01 : 10:33:45

quote:
Originally posted by TG

Mario... BOL man, BOL.
That means "Books On Line" (from Query Analizer: F1 or Shift+F1)
Start by looking at the "Cast and Convert" topic (lookup convert from the index tab)

If you read through BOL you too could someday be a SQL Server MVP

- Warning: Data may be lost converting column 'DateLastPaid' from 'varchar(50)'.
You'll get this anytime you change a column definition that COULD result in loss of data because of a loss of precision. TinyInt to Int will not give you that warning, Int to tinyInt will.
Look for values that won't convert to datetime by something like this:
Select * from Debtors where isDate(DateLastPaid) = 0

- The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
This means you have at least 1 value in your varchar(50) column that cannot be converted to datetime. Find the values that won't convert with:
Select * from Debtors where isDate(DateLastPaid) = 0

Be One with the Optimizer
TG

Thanks for the eye-opener on TinyInt!

P.S. 2 more to YAK vet status!

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

MarioK
Starting Member

20 Posts

Posted - 2005-02-01 : 16:49:51

i read BOL before i asked you these Question, but im beginning with SQL.....

"If you read through BOL you too could someday be a SQL Server MVP"
thanks mucho TG

Thanks Xerses.

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-01 : 17:33:11

You're welcome!!

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources