DATE CONVERSION

[EPNICO]

Hello:

Here is my MS-SQL statements in dealing with the timestamp field:

select *
from wcps..wcps.policy_header_phrase
where ncci_transaction_code = "01"
AND updt_dt_tm = '20011011'

the field updt_dt_tm is a timestamp field. When I run the above query I received zero records. If I change it to >= I get all the records and I know records exist for 20011011.
What syntax do I use in order for the select to work correctly for a individual date.
Thanks for any ideas.

 

[puredesi5]

Use convert function to convert '20011011' into correct date time format.

 

[cmmrfrds]

Here is a definition of timestamp from SQL Server Help.

Special Data
Special data consists of data that does not fit any of the categories of data mentioned earlier such as binary data, character data, Unicode data, date and time data, numeric data and monetary data.

Microsoft® SQL Server™ includes four types of special data:

timestamp
Is used to indicate the sequence of SQL Server activity on a row, represented as an increasing number in a binary format. As a row is modified in a table, the timestamp is updated with the current database timestamp value obtained from the @@DBTS function. timestamp data is not related to the date and time of an insert or change to data. To automatically record times that data modifications take place in a table, use either a datetime or smalldatetime data type to record the events and triggers.

 

[tlbroadbent]

If the field is defined as timestamp you can't query it for dates for the reasons explained by cmmrfrds. If it is a datetime or smalldatetime column then you simply have to provide the date string in format recognized by SQL Server such as...

updt_dt_tm = '2001-10-11'
updt_dt_tm = '10/11/2001'
updt_dt_tm = 'Oct 11 2001'.

Look under "Cast and Convert" in SQL BOL for more info on date formats. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm