May 1, 2008 at 9:10 am
I have two tables
Table1 Col1 datetime
Table2 COLA datetime
I have to select their dates in an equal where lclause. However for some reasons that are unknown to me, Col1 has this format 2008-05-01 11:03:35.203 and COLA has this format 5/1/2008 11:03:35 AM
How can I 'instruct' COLA to be in the same format as Col1?
Thanks
May 1, 2008 at 9:16 am
The format should not affect the determination of equality as SQL Server stores datatime as a numeric behind the scenes. As far as forcing the data to look the same you can use set dateformat m/d/y in your queries or you can do an explicit convert(varchar(15), data_col, 101) or whatever format you want.
BTW - I don't know why they would have different formats in display.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 1, 2008 at 9:41 am
Somehow when col1 has 2008-05-01 11:16:23.067 and COLA
has 5/1/2008 11:16:23 AM the equal condition fails ( I think the millisecs) got dropped.
When I did an insert into COLA I did have a convert
CONVERT(VARCHAR(30),GetDate(),21) to have COL1 format but it still ends up with just 5/1/2008 11:16:23 AM.
Thanks
May 1, 2008 at 9:48 am
If the milliseconds are being dropped the data is being stored as smalldatetime or prior to the insert it is smalldatetime. To get equality you need to convert the datatime value (with milliseconds) to smalldatetime. If you really need milliseconds then the process inserting into ColA needs to be sure to be using datetime all the way through the process. I really think the column is defined as smalldatetime.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 1, 2008 at 10:44 am
The format doesn't matter. Dates and times are stored in SQL as numbers. The missing milliseconds will cause it to not be equal.
In datetime, dates are stored as the number of days since 1 Jan 1900, and the number of 1/300th of a second since midnight. Smalldatetime uses the same start, but doesn't store are many bytes of data (only accurate to minutes). Per your original statement, both columns are datetime, not smalldatetime, so that's not the problem (unless you miswrote that).
To try this out for yourself, try:
select cast(0 as datetime)
select cast(1 as datetime)
select cast(.001 as datetime)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 1, 2008 at 10:54 am
yes both columns are of data type datetime.
Thanks, I will give it a try.
May 1, 2008 at 11:17 am
How are the column being populated? Somehow the data in ColA is being created without the milliseconds, which tells me at some level it is being used as a smalldatetime. If it is being passed using a smalldatetime variable and then inserted you will not have the milliseconds.
Try this:
Declare @smalldate smalldatetime, @date datetime, @datefromsmalldate datetime
Set @date = getdate()
Set @smalldate = @date
Set @datefromsmalldate = @smalldate
Select @date as full_date, @smalldate as smalldate, @datefromsmalldate as date_from_small_date
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 1, 2008 at 1:08 pm
declare @CreateDate varchar(50) ,@RowsFound int
set @CreateDate=CONVERT(VARCHAR(30),GetDate(),21)
set @RowsFound=0
select @RowsFound=count(*) from dbo.RECON_MASTER M where
M.INSTANCE=@INSTANCE
and M.ACCOUNT_NAME=@ACCOUNT_NAME
if @RowsFound > 0
Begin
-- last column is col1
insert into dbo.RECON_MASTER_HISTORY
select M.*,@CreateDaTe from dbo.RECON_MASTER M
where
M.INSTANCE=@INSTANCE
and M.ACCOUNT_NAME=@ACCOUNT_NAME
-- this is COLA that is updated with the exact same value
insert into dbo.RECON_MASTER_HISTORY_2EXCEL
(INSTANCE,ACCOUNT_NAME, COSTCENTER,HistCreateDate)
values (@INSTANCE,@ACCOUNT_NAME, @COSTCENTER,@CreateDate)
end
Thanks!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply