DATETIME Question

[Ringers]

1. I am trying to get rows where the time is equal 08:30 AM
2. Need to get all rows where the date is equal 30/12/1899

The field is called PA_TIME and table is FPATS, the field looks like this
28/10/2004 1:30:00 PM

I have tried using DATEPART but can't get it to work, so if anyone has more of an idea than me that would be great;

SELECT PA_TIME
FROM FPATS
WHERE DATEPART(TIME,PA_TIME )='08:30:00 AM

 

[r937]

1. WHERE CONVERT(VARCHAR,pa_time,108) = '08:30:00'

2. WHERE pa_time >= '1899-12-30' AND pa_time < '1899-12-31'

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Ringers]

Sorry neither of those statements work. Can you please explain the '108', thanks.

 

[r937]

what is the datatype of the column, please?

108 is the convert style for hh:mm:ss

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[fhlee]

ASSUMING THAT PA_TIME is a DATETIME field:

1. I am trying to get rows where the time is equal 08:30 AM.
SELECT PA_TIME
FROM FPATS
WHERE ( DATEPART( hour, PA_TIME) = 8 ) )
AND ( DATEPART( minute, PA_TIME) = 30 ) )
/* I assume that you don't care what seconds within 8:30am. for example, you want 8:30:01am, 8:30:25am, etc, etc */


2. Need to get all rows where the date is equal 30/12/1899
SELECT PA_TIME
FROM FPATS
WHERE CONVERT(DATETIME, CONVERT(VARCHAR, PA_TIME, 101), 101) = '12/30/1899'
/*Note: 101 means to use the mm/dd/yyyy format*/


~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

 

[fhlee]

Hi R937.com,

1. WHERE CONVERT(VARCHAR,pa_time,108) = '08:30:00'
**This is SPECIFIC to 8:30:00 am. Means accurate to the second and possibly microsecond. May not be what Ringers is looking for. I'm just guessing Maybe he just want accuracy to the minute.

2. WHERE pa_time >= '1899-12-30' AND pa_time < '1899-12-31'
**This is highly dependent on the locale setting of the SQL server. Not all SQL server is setup to have datetime to be in the format of yyyy-mm-dd, as such, this code may not work across all servers.

Hope you don't mind me giving my 2 cents. cheers.


~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

 

[r937]

Not all SQL server is setup to have datetime to be in the format of yyyy-mm-dd

actually, yes they are


internally, datetimes are stored as two separate integers (one for the number of days beyond the "zero" or base date, and one for the number of clock ticks past midnight)

so there has to be conversion on the way in and also conversion on the way out

if you see 03/19/2009 when you display a date, then all this means is that this is your default dispaly format

perhaps this is what you meant by the server being "set up"

but yyyy-mm-dd is the most unambiguous format for specifying a date, and all sql servers understand it

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Ringers]

The Datatype is DATE

 

[Ringers]

Got it working;

SELECT PA_TIME FROM FPATS WHERE TO_CHAR(PA_TIME,'HH24:MI:SS') = '08:30:00'

SELECT PA_TIME FROM FPATS WHERE TO_CHAR(PA_TIME,'YYYY-MM-DD') = '1899-12-31'

Thanks all for replys.

 

[r937]

ringers, you aren't by any chance using oracle, are you?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon