Sql Server 2005 - FieldType for Date field 4

[BxWill]

Performing a query in Sql Server 2005 and receive some nonsensical values for the date field

For example, I receive dates formatted as
"2020-09-04 00:00:00.000"

Using "DATETIME" in my CREATE TABLE section (per the example below) does not appear to work properly.

EXAMPLE------------------------------------------

CREATE TABLE ##tmpTest
DATE_PERIOD DATETIME
.
.
.
-------------------------------------------------

Upon opening the table in sql server, the "DATE_PERIOD" field is formatted like "201001'

What is the correct field type for a date column that has the format YYYYMM?

 

[r937]

first of all, 2020-09-04 00:00:00.000 is ~not~ a nonsensical value

if that is the format of the date values you are receiving, you don't have a problem

DATETIME is the proper datatype for those dates

now, if you want to store them as "date periods" with YYYYMM format, you would need to do a conversion, and then you can store the result in an INTEGER or VARCHAR(6) column



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

 

[bborissov]

Datetime is the field type you need.
You easy can get rid of time portion in your Frontend application.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Olaf Doschke]

There are several datetime data types, but none is just the month of a year (interpretable as a date period). SQL2005 offers datetime and smalldatetime, but both data types actually do have a full date with time portion. Smalldatetime only has an accuracy down to minutes, while datetime goes down to 3.33 milliseconds.

Im not sure what you're doing, when you say "Upon opening the table in sql server, the "DATE_PERIOD" field is formatted like 201001". SQL Server itself does not offer 'opening a table', SQL Server Management Studio does, maybe also components in .NET studio let you 'open' the table, showing the table contents. Such tools may offer formatting, so you only see the YYYYMM portion, but what is stored and what you get when querying is the full datetime anyway.

Since SQL2008 you can also have the Date field type, which hos no time portion, but there is no data type for date period.

Querying data of a month:

Anyway, Store the first of a month and interpret it as a date period. And if you want to query data for a month, you rather query datetimefield between 'YYYYMM01000000' and 'YYYYNN01000000' with NN being one month later than MM, eg '20101201000000' and '20110101000000' would be the borders of the date period December 2010.

If you want to very precise and fear records stored exactly at midnight:

Select ... from .... where '20101201000000'<=datetimefield AND datetimefield<'20110101000000'

Bye, Olaf.

 

[markros]

Olaf,

Drop extra 0 in the datetime, e.g.
'20100101' is a valid datetime, but '20110101000000' is not and can not be converted to datetime.

PluralSight Learning Library

 

[BxWill]

I am using sql server management studio 2005

Receiving date in the format such as "2020-09-04 00:00:00.000"
is nonsensical if I have the following sql script where I am restricting the date by using

"AND DATE_PERIOD_NM BETWEEN 200904 AND 200909"

--BedStay per 1000
--Cost/Day
--Paid/Day

DROP TABLE ##tmpTest

CREATE TABLE ##tmpTest
(PRODUCT VARCHAR (10)
,DATE_PERIOD DATETIME 
,PROVIDER VARCHAR (70)
,STAT VARCHAR (20)
,STAT_TYPE VARCHAR (20))

INSERT INTO ##tmpTest
SELECT                                                                  
    
CASE WHEN PA.TYPE IN ('SDA', 'PNK') THEN 'LLTC' ELSE PA.TYPE END AS PRODUCT,
DATE_PERIOD_NM AS DATE_PERIOD, 
DP.PROV_FULL_NM AS PROVIDER,
SUM(PD.UTIL_PFR_CNT) AS BEDSTAY,

'BEDSTAYS' AS STAT_TYPE

FROM DCA.dbo.PAY_DETAIL PD (NOLOCK)

INNER JOIN DCA.dbo.CATEG CA (NOLOCK)
 ON PD.CATEG_CD = CA.CATEG_CD
 
INNER JOIN DCA.dbo.PROD_PLAN PA (NOLOCK)
 ON PD.PROD_ID = PA.PROD_ID

INNER JOIN DIM_DATE DD 
 ON PD.DATE_ID = DD.DATE_ID
 AND DATE_PERIOD_NM BETWEEN 200904 AND 200909

INNER JOIN DIM_PROVIDER DP 
 ON PD.SVC_PROV_ID = DP.PROVIDER_ID


WHERE 


CA.FINANCE_SVC_TYPE_CD IN ('IPGB','DPHT')

AND DATE_PERIOD_NM BETWEEN 200904 AND 200909	

GROUP BY

CASE WHEN PA.TYPE IN ('SDA', 'PNK') THEN 'STO' ELSE PA.TYPE END,
DATE_PERIOD_NM,
DP.PROV_NM
ORDER BY
CASE WHEN PA.TYPE IN ('SDA', 'PNK', 'STO') THEN 'STO' ELSE PA.TYPE END,
DP.PROV_NM

 

[r937]

Receiving date in the format such as "2020-09-04 00:00:00.000" is nonsensical if I have the following sql script

if you don't mind me saying this, it's not the date that's nonsensical, it's the way you're handling it


if you want only dates from april through to and including september, do it like this --

AND DATE_PERIOD_NM >= '2009-04-01'
AND DATE_PERIOD_NM  < '2009-10-01'

notice it's less than the first of the following month

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

 

[BxWill]

So, I can specify that the DATE_PERIOD_NM >= '2009-04-01'...even though it is displayed in the table as '200904'?

Will try r937's suggestion.