DateTime format

Hello all,

I'm trying to write a query against an exisiting table that i can't modify and i'm running into a bit of a problem. The table stores timestamps as a char field instead of a datetime.

So, i've had to use the CONVERT function to change it to a datetime during my query. A sample is below:

SELECT convert(datetime, logged, 120) FROM AP200310

This works, except i want to include the option of querying a single day. Since the data that is returned is in this format:

12/12/2006 6:54:15 PM

The following sql statement doesn't work:
SELECT convert(datetime, logged, 120) FROM AP200310 WHERE logged = '12/12/2006'

Thanks in advance for any help.

Have you tried using cast instead of convert?|||

You need to handle the logged field because it is a datetime field.

You can try this to get your query to work:

SELECT

CONVERT(NVARCHAR(10),logged,120)

FROM

AP200310

WHERE(CONVERT(NVARCHAR(10),logged,101)='12/12/2006')

|||

Thanks for the quick response.

I tried your code and it didn't seem to work.

Help!!

|||

Can you post the results of the query limno posted? and tell us why it does not work. It seems to work for me.

declare @.ttable (col1int identity, col2char(22))insert into @.tvalues ('12/12/2006 6:54:15 PM')insert into @.tvalues ('12/12/2006 6:55:15 PM')insert into @.tvalues ('12/20/2006 6:54:15 PM')insert into @.tvalues ('10/12/2006 6:54:15 PM')selectconvert(nvarchar(10),col2,120), *from @.twhere(CONVERT(NVARCHAR(10),col2,120) ='12/12/2006')

|||

Here is my query.

SELECT CONVERT(NVARCHAR(10), LOGGED, 120) AS LOGGED
FROM AP200612
WHERE (CONVERT(NVARCHAR(10), LOGGED, 101) = '12/12/2006')

My results are nothing is returned.

To help matters, i've included a copy of the schema of the table in question:

CREATETABLE [dbo].[AP200612](

[ACCOUNT] [char]

(9)COLLATE SQL_Latin1_General_CP1_CI_ASNULLCONSTRAINT [gmc_AP200612ACCOUNT]DEFAULT(''),

[LOGGED] [char]

(19)COLLATE SQL_Latin1_General_CP1_CI_ASNULLCONSTRAINT [gmc_AP200612LOGGED]DEFAULT(''),

[ORIGIN] [decimal]

(3, 0)NULLCONSTRAINT [gmc_AP200612ORIGIN]DEFAULT((0)),

[STAT_NUM] [char]

(5)COLLATE SQL_Latin1_General_CP1_CI_ASNULLCONSTRAINT [gmc_AP200612STAT_NUM]DEFAULT(''),

[SUBLOC] [char]

(5)COLLATE SQL_Latin1_General_CP1_CI_ASNULLCONSTRAINT [gmc_AP200612SUBLOC]DEFAULT(''),

[SUFFIX] [char]

(2)COLLATE SQL_Latin1_General_CP1_CI_ASNULLCONSTRAINT [gmc_AP200612SUFFIX]DEFAULT(''),

[TERM] [char]

(5)COLLATE SQL_Latin1_General_CP1_CI_ASNULLCONSTRAINT [gmc_AP200612TERM]DEFAULT(''),

[TIME] [char]

(19)COLLATE SQL_Latin1_General_CP1_CI_ASNULLCONSTRAINT [gmc_AP200612TIME]DEFAULT(''),

[TYPE] [char]

(3)COLLATE SQL_Latin1_General_CP1_CI_ASNULLCONSTRAINT [gmc_AP200612TYPE]DEFAULT('')

)

ON [PRIMARY]

Thanks for your responses and any future responses.

Richard M.

|||

I just replaced your column name and table name and it works for me:

You have different format numbers - 120 and 101, both are same though. Can you also post some sample rows?

declare @.ttable (col1int identity, col2char(19))insert into @.tvalues ('12/12/2006 6:54:15')insert into @.tvalues ('12/12/2006 6:55:15')insert into @.tvalues ('12/20/2006 6:54:15')insert into @.tvalues ('10/12/2006 6:54:15')SELECTCONVERT(NVARCHAR(10), col2, 120)AS col2FROM @.tWHERE (CONVERT(NVARCHAR(10), col2, 101) ='12/12/2006')

|||Hi rmethod, ndinakar's solution should work, I'd like know how you insert rows into the AP200612 table. BTW, if the LOGGED column is used to store some date, why not use DATETIME/SMALLDATETIME data type? Then you can use rich?T-SQLDate and Time functions to filter rows based on the LOGGED column.