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
AP200310WHERE(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.
ly values for